I have created a trigger in SQL server to update a field in table1 when a record with a matching field is inserted in table2. E.g:
CREATE TRIGGER TRIG_NAME ON TABLE2
AFTER INSERT
AS
UPDATE TABLE1
SET TABLE1.FIELD2 = ‘newvalue’
FROM TABLE1 Y, TABLE2 X
WHERE Y.FIELD3 = X.FIELD3
This serves to update a status field in the table1 record whenever a copy of this record is inserted into table2. However, I think it will update every row in table1 and I only need to update the one that matches the record that just been inserted. I wonder if I can use the logical table INSERTED in my FROM clause. I.e FROM TABLE1 Y, INSERTED X WHERE… This would update only the current record., but it would mean that the logical table INSERTED goes away every time the trigger fires, and is always empty except for current inserts. Am I correct in my assumption?
