SQL Insert trigger to update INSERTED table values

insert, sql-server-2005, triggers

Solution

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

Problem

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. I tried: ``` UPDATE INSERTED SET TheColumnToBeUpdated = ( SELECT TheValueCol FROM AnotherTable.ValueCol WHERE AnotherTable.ValudCol1 = INSERTED.ValueCol1 ) WHERE ValueCol IS NULL ``` But I get this error: ``` Msg 286, Level 16, State 1, Procedure ThisTable_INSERT, Line 15 The logical tables INSERTED and DELETED cannot be updated. ``` How should I do that?

Original source