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.

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?

回答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

回答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.

回答3:

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