October 13, 2005 at 8:26 am
I need some suggestions for using or more identifying what record in a table was inserted, updated, or deleted so that I can use the identity info from that record later on in the same trigger.
Specifically, I have a trigger that watches a column. I want that trigger to fire on update, insert, or delete and be able to grab the identity of the row that fired the trigger, again so I can use it later within the same trigger.
Thanks in advance, this forum always points me in the right direction!
October 13, 2005 at 8:30 am
Look into BOL on Triggers and use of the "inserted" and "deleted" tables...
I wasn't born stupid - I had to study.
October 13, 2005 at 8:36 am
I always look to BOL first but can't find what I need. I guess what I really want is to know where I can find a list of system cursors that are created by default whenever there is an event that modifies a table.
Jeff
October 13, 2005 at 8:44 am
do you have an id column ?! what is the ddl of the table on which you're setting the trigger ?!
are you not able to grab the modified row using the id from the "Inserted" or "Deleted" tables ?!
**ASCII stupid question, get a stupid ANSI !!!**
October 13, 2005 at 8:44 am
Those two tables "inserted" and "deleted" contain the information about the records that have been changed.
Try making another Trigger that takes all records from "inserted" and dumps them into a table of the same structure as the original. Make some type of change to the table and see what you get...
I wasn't born stupid - I had to study.
October 14, 2005 at 2:43 pm
Here's an example of the use of the inserted table in a trigger:
Create Trigger CustomerTruckInsert on CustomerTruck FOR Insert, Update
not for replication
as
Update CustomerTruck set DateLastChanged=getutcdate()
From [Inserted] , CustomerTruck
where Inserted.CustomerTruckID=CustomerTruck.CustomerTruckID
hth
jg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply