Value in Trigger

Question

Value in Trigger

Rohit @ SQL

SSC Eights!

Points: 938
More actions
January 11, 2004 at 3:38 am

#108645

Hi to All,I have on table Events
With Structure
EventID Bigint Identity(1,1)
EventNo Bigint
EventDate DateTime
Every Time any events occurs in applicationnew row is inserted
I have to identify in the trigger that which event happenedin the application
That is i need to identify the row which was inserted in thetrigger itself
i am not sure that
Set @LastEvent=Select Max(EventId) From EventsWill work for this
Or Set @LastEvent=@@Identity Will work
Any body could help
me for this i am stuck Thanks RohitkGupta

Rohit

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 1

There is one point that i forgot to tell that the rows insertions are done by more that one appliication running simultaneously

Rohit

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

First of all if you want to know which was the even then you have to create 3 triggers. one for INSERT, one for UPDATE and one for DELETE.

I do not understand exactly your problem but you can identify the application and the host by queriing the sysprocess table out of the trigger

select hostname, program_name from master..sysprocesses where spid = @@spid

Bye
Gabor

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 3

I need to know the identity column (EventId) When the Trigger Got Fired i know tha can be done by Select EventId From inserted But what i am not sure is that if multiple insertions are done simultaneously then the value i will get will be correct that is it will not be the last inserted row's EventId but for which row is inserted

Rohit

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 4

I suspect you are needing something like this

create trigger foo on tablename

for update, insert

as

update tablename set modifiedDate = getdate()

where EventID in (select eventid from inserted)

This will accomodate batch inserts or updates.

HTH

------------

Ray Higdon MCSE, MCDBA, CCNA

justevolved SSC Enthusiast Points: 188 More actions · Answer 5

You can use scope_identity for finding the last Id that was inserted.Also look in BOL for difference between @@identity and @@scope_identity

You have to dig for tons of dirt to get an ounce of Gold