Trace all delete operation on a MS SQL table
-
Hello everybody
I really appreciate if you can help me with the following problem:
I want to trace all delete operation that takes place on a MS SQL database table – let’s call it Employees table. Up until now I have written a simple trigger that inserts in another table details about deleted records from Employees table (I select a few columns from “deleted” table inside trigger and insert them in my log table). Thing is that I also want to find out who deleted the records (client computer name and application name would be enough), and I don’t know exactly how.
Since the records can be deleted from SQL Query Analyzer, or Enterprise Manager or my app (that uses Employees table) I want to do it somehow in TSQL with a trigger, or in any other mode that can be done on the SQL Server.
Sysprocesses table has a lot of information but I can’t like it with my Employees table or with the operations running on it and besides sysprocesses is update after the trigger fires…
Thank you!
-
Sysprocesses has the information you need in it when the trigger fires. You can get the information you need with a cross join since there is only 1 row in sysprocesses for each spid.
insert into log_table (
somecolumn,
someothercolumn,
login_column,
program_name_column,
machine_name_column)
select d.somecolumn,
d.someothercolumn,
s.loginame,
s.program_name,
s.hostname
from deleted d
cross join master.dbo.sysprocesses s
where s.spid = @@SPID
-
tried it, works fine, thanks a lot!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply