SQL插入触发器以更新INSERTED表值

本文介绍了SQL插入触发器以更新INSERTED表值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想创建一个插入触发器，如果所有插入的行为空，则更新所有插入行的值，根据插入表中的另一列，新值应从另一个表中获取。

I want to create an Insert trigger that updates values on all the inserted rows if they're null, the new values should be taken from a different table, according to another column in the inserted table.

我尝试过：

UPDATE INSERTED
SET TheColumnToBeUpdated =
    (
    SELECT TheValueCol FROM AnotherTable.ValueCol
    WHERE AnotherTable.ValudCol1 = INSERTED.ValueCol1
    )
WHERE ValueCol IS NULL

但我收到此错误：

Msg 286, Level 16, State 1, Procedure ThisTable_INSERT, Line 15
The logical tables INSERTED and DELETED cannot be updated.

我应该怎么做？

推荐答案

您需要更新目标表，而不是逻辑表。不过，您可以与逻辑表连接，以找出要更新的行：

You need to update the destination table, not the logical table. You join with the logical table, though, to figure out which rows to update:

UPDATE YourTable
SET TheColumnToBeUpdated =
    (
    SELECT TheValueCol FROM AnotherTable.ValueCol
    WHERE AnotherTable.ValudCol1 = INSERTED.ValueCol1
    )
FROM YourTable Y
JOIN Inserted I ON Y.Key = I.Key
WHERE I.ValueCol IS NULL

这篇关于SQL插入触发器以更新INSERTED表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！