Menu
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?
630
To create a trigger, we need to change the delimiter. Inserting the row into Table1 activates the trigger and inserts the records into Table2. To insert record in Table1. To check if the records are inserted in both tables or not.
First, specify the name of the trigger that you want to create after the CREATE TRIGGER keywords. Second, use AFTER INSERT clause to specify the time to invoke the trigger. Third, specify the name of the table on which you want to create the trigger after the ON keyword.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
