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: msdn.microsoft.com/en-us/library/aa175912%28SQL.80%29.aspx — OMG Ponies
– OMG Ponies, Commented Dec 3, 2009 at 3:44
@OMG Ponies: Shimmy can't use a default as per the question: stackoverflow.com/questions/1744455 — gbn
– gbn, Commented Dec 3, 2009 at 8:18

Michael Haren · Accepted Answer · 2009-12-03 03:40:02Z

52

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 3, 2009 at 3:40

Michael Haren

109k41 gold badges172 silver badges208 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy King Over a year ago

(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".

phuzi Over a year ago

@AndyKing JOIN Inserted I ON Y.Key = I.Key will restrict the update to just the inserted row/rows

Jeff French · Accepted Answer · 2009-12-03 03:51:26Z

23

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.

answered Dec 3, 2009 at 3:51

Jeff French

1,0298 silver badges22 bronze badges

Comments

kleopatra · Accepted Answer · 2013-01-25 10:37:17Z

0

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),

edited Jan 25, 2013 at 10:37

kleopatra

51.6k28 gold badges103 silver badges220 bronze badges

answered Jan 25, 2013 at 10:20

Rohan

1

Collectives™ on Stack Overflow

SQL Insert trigger to update INSERTED table values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related