General discussion

Locked

SQL Srver Trigger Logical table Inserted

By steves815 ·
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?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by farzanfar In reply to SQL Srver Trigger Logical ...

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

Collapse -

Field Name

by ghozan In reply to

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?

Collapse -

by amoljpatil In reply to SQL Srver Trigger Logical ...

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

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums