Connection information in a table trigger?

Is it possible to log or insert into a table the connection information (Application & Login) from an table trigger?

We have tough problem where data in a particular table is getting 'wiped-out' (rows are getting set to all NULLs) and we are unable to correlate this with any particular piece of software. My hope is that we can write an table trigger that can log or create an row in a temp table or the like to allow us to track this down to the offending application so that we can finallly get rid of the problem entirely.

Thanks,

You really should be using Profiler for this kind of activity. It will be able to give you more information than a TRIGGER. Profiler will allow you to capture the entire query (or queries) that are being sent to the server.

A TRIGGER would only allow you to log parameter and system values -NOT the actual query.

|||

Agree with Arnie for the most part, you can use profiler for this most likely. You would want to get really granular and track statements inside procedures too. You might also filter on the name of the table. Profiler can be kind of noisy/picky, so it might take a few tries, but it is the greatest thing to have in a crisis

As far as a trigger, you can get some of this information from sys.sysprocesses (or master.dbo.sysprocesses for 2000 and earlier.) You could join some of the values with the values in the inserted and deleted tables to see what is happening at a granular way. What I might do is to add a trigger that does:

if exists (select * from inserted where columnIDon'tWantSetToNull is null)

begin

raiserror ('DON''T DO THIS!',16,1)

rollback transaction

end

insert into log

select inserted.key, sysprocesses.columns
from inserted
join sysprocesses

on sysprocesses.spid = @.@.spid

If the operation was not in a transaction, you will get a log row, but your data will certainly not be hosed. If your application/process doesn't just ignore errors, you can track it down that way.

|||

Thank you both for your help. I'm not very good/handy with profiler, but I'll give it a try if I don't get anywhere with it, I'll try with an trigger on the table and the sysprocess table information.

George

|||

You definitely need to get good with profiler. In my opinion, it is the greatest thing about SQL Server, and for a person who has worked only with SQL Server for 15 years, that is saying something. Diagnosing problems with SQL Server is so much easier than pretty much any other programming tool, simply because I can see, immediately what the "heathen" user is trying to do to it and stop them.

Of course it has made it easy to simply force the DBA to prove that the database is not the culprit first since it is so easy, but that is another story

|||

How do I see the parameter values sent in a parameterized query in Profiler?

George

|||Profiler will show you the entire query, with parameters placed in the correct locations.