April 13, 2006 at 9:34 am
I am working with a trigger that fires on an update. It's a single row update and I want to grab the ID of that row. How can this be done? I've seen some posts with code like this within the trigger:
select @ID = (select [ID] from updated)
But that doesn't work. Any help would be appreciated. TIA!
April 13, 2006 at 10:52 am
It depends on your version of SQL.
For instance in SQL 2000 if it is only one row always one answer is SET @ID = SCOPE_IDENTITY()
April 13, 2006 at 11:31 am
Antarres686,
Yes I am using 2000. I'll tried that and I didn't get it to work. I'm trying to get the Identity (from the row that is being updated) within the trigger. So this code will reside in the Trigger syntax. From what I've briefly read scope_identity and @@Identity seem to only work with INSERT statments not UPDATE.
From BOL:
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
April 13, 2006 at 12:03 pm
I found it. I read a little about Using the Inserted and Deleted Tables in the BOL.
select @ID = ID from inserted
This works within the trigger.
April 14, 2006 at 2:29 am
Do not forget that the inserted table can have multiple rows.
You should test this !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply