SQL Insert trigger to update INSERTED table values

Question

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?

If you aren't going to encapsulate the logic to handle this in a stored procedure, a better alternative than a trigger would be to use a default constraint: http://msdn.microsoft.com/en-us/library/aa175912%28SQL.80%29.aspx — OMG Ponies, Dec 03 '09 at 03:44
@OMG Ponies: Shimmy can't use a default as per the question: http://stackoverflow.com/questions/1744455 — gbn, Dec 03 '09 at 08:18

score 51 · Accepted Answer · answered Dec 03 '09 at 03:40

51

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

answered Dec 03 '09 at 03:40

Michael Haren

105,752
40
168
205

(I know that this is very old!) Won't this update all the rows in YourTable? Adding the following to the final "where" clause will update just the row that was inserted: "and Y.Key = I.Key". – Andy King Mar 13 '18 at 22:11
1

@AndyKing `JOIN Inserted I ON Y.Key = I.Key` will restrict the update to just the inserted row/rows – phuzi Jun 14 '18 at 13:46

score 23 · Answer 2 · answered Dec 03 '09 at 03:51

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.

score 0 · Answer 3 · edited Jan 25 '13 at 10:37

insert into output  
(SELECT t1.ts - INTERVAL (SECOND(t1.ts)%10) SECOND,  
t1.ts - INTERVAL (SECOND(t1.ts)%10) SECOND + INTERVAL 10 SECOND ,sum(t1.data),   
FROM (select * from input   
where unix_timestamp(ts) >= unix_timestamp('2000-01-01 00:00:10')  
and unix_timestamp(ts) < unix_timestamp('2000-01-01 00:01:20')  
)
  as t1   
GROUP BY UNIX_TIMESTAMP(t1.ts) DIV 10 );

This is where my output table is coming from. So the insertion is not by values.

Im so sorry but I can't access my account from here (office),

SQL Insert trigger to update INSERTED table values

3 Answers3

Linked