2

Possible Duplicate:
Create Trigger to log SQL that affected table?

Is possible in SQL Server 2008 to write a trigger which will put into my log table the query which triggered it? I will explain it on the example:

I have a table, TAB1, and log table LOG. I execute query which deletes rows from TAB1 and I want to have that query (or something else what could help me to identify who executed delete query on TAB1) in my LOG table.

Any idea how this can be achieved?

Community
  • 1
  • 1
reizals
  • 1,245
  • 1
  • 14
  • 21

1 Answers1

0

the only way comes to my mind is to pass procedure name explicitly through CONTEXT_INFO this goes into procedure

declare @bin varbinary(128) = (cast(object_name(@@Procid) as varbinary(128))
set context_info @bin

and use it in trigger

declare @procName as nvarchar(max)
set @procName = cast(context_info() as nvarchar(max))

i dont know any other way to do that

edit: i think tham might be interesting to you clickie

WKordos
  • 2,167
  • 1
  • 16
  • 15