SQL Insert trigger to update INSERTED table values

Question

Hello.

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?

Answer 1

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

Answer 2

You could change the trigger to an INSTEAD OF INSERT. This will let you check the incoming values and, if needed replace them with the values from your other table.

CREATE TRIGGER CoolTrigger 
ON MyAwesomeTable 
INSTEAD OF INSERT
AS 
BEGIN  

INSERT MyAwesomeTable (TheValueCol)
SELECT ISNULL(INSERTED.TheValueCol, AnotherTable.TheValueCol) AS TheValueCol
FROM INSERTED
JOIN AnotherTable ON INSERTED.ValueCol1 = AnotherTable.ValueCol1

END

NOTE: INSTEAD OF triggers do NOT cause recursion.