sql-server-2005 - 用于更新 INSERTED 表值的 SQL 插入触发器

Question

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

我试过:

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.

我该怎么做？

Answer 1

您需要更新目标表，而不是逻辑表。但是，您可以加入逻辑表以找出要更新的行:

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