用于更新 INSERTED 表值的 SQL 插入触发器 [英] SQL Insert trigger to update INSERTED table values

问题描述

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

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

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