Get inserted and updated record ID in Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get inserted and updated record ID in Trigger

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2003-10-10 : 09:33:02

Hi,

I would like to have a trigger to find out the Rec ID (identity) of an inserted and updated record to the table, is it possible ?

* I think I can use this "SELECT IDENT_CURRENT('tablename')" to find out the inserted Rec ID.

* But don't know how to find out the updated Record ID.

Please Help,
Thanks in advance
Matthew

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-10-10 : 10:29:51

matthew

try this

CREATE TRIGGER trecID on tablename
for insert

declare @recID numeric(18,0)

select @recid = i.recID from
inserted i

print @recid
OMB

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-10 : 11:03:16

People have a hard time wrapping their minds around triggers that fire as a result of multi-row DML. OMB, what would you trigger do if I ..


inesrt tablename (recID)
select 1 as recID
union alll
select 2 as recid

... your trigger will only print 1 or 2, but not both.

matkwan, you need to clarify your business rule. Do you want the greatest identity value, or all of the identities from the DML? If you just want the greatest, have a look at "@@identity" in Books Online.

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-10 : 11:07:29

This is "set based processing world"

A trigger can fire for 1 to n records....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources