DELETE Trigger

Question

Post reply

DELETE Trigger

imgflow

Valued Member

Points: 57
More actions
May 27, 2008 at 5:26 am

#182779

Hi
How do i get the event details in a "FOR AFTER DELETE" trigger - the user who executed the query, the command / sp the user passed, the ip / network address of the user?
Txs
Alwyn

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This should work.

SELECT original_login(), local_net_address, text

FROM sys.dm_exec_requests er

INNER JOIN sys.dm_exec_connections ec on er.session_id = ec.session_id

CROSS APPLY sys.dm_exec_sql_text (er.sql_handle)

where er.session_id = @@SPID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

imgflow Valued Member Points: 57 More actions · Answer 2

HI

Txs but this gives me the info and text of the create trigger command - i need the info on the delete command that started the trigger

Alwyn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Try DBCC INPUTBUFFER(@@SPID).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

imgflow Valued Member Points: 57 More actions · Answer 4

Hi

Txs

The combination of the two gives me all the info i need!!!

Alwyn