Web Development

Ask something
Discussion Locked

SQL Srver Trigger Logical table Inserted

0 helpful
3 replies

by steves815

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by farzanfar

Check This :

CREATE TRIGGER TRIG_NAME ON TABLE2
AFTER INSERT
AS
UPDATE TABLE1
SET TABLE1.FIELD2 = 'newvalue'
WHERE TABLE1.FIELD3 = (SELECT FIELD3 FROM INSERTED)

GoodLuck

by ghozan

What if the field name is not known? Or maybe you have to query first the name of the field and then try to select it from the table inserted. Something like this:

EXEC('INSERT INTO Temp_Value SELECT '+ @P_Key + ' FROM inserted')

The exec statement will give error, it said:
"Invalid Object name inserted".

Have any solution for this?

by amoljpatil

CREATE TRIGGER TRIG_NAME ON TABLE2
AFTER INSERT
AS
UPDATE TABLE1
SET TABLE1.FIELD2 = 'newvalue'
WHERE TABLE1.FIELD3 IN (SELECT TABLE2.FIELD3 FROM TABLE2)

This discussion is closed to new replies.