Creating SQL Server trigger to debug database issue-CodePudding

I have a database where one of the columns in one table keeps going blank. There's nothing in our software that can clear that column, so we are quite perplexed how it keeps happening.

Any suggestions on how I can figure this out? I'm thinking of creating a trigger that runs every time this table gets updated, and ideally when that field becomes empty.

But what kind of info can I actually track that will help me figure this out? Can I store the SQL statement that gets run when that update occurs? Can I store the Windows process that is connected to the database?

Any other suggestions? Thanks

CodePudding user response：

You could also throw an error from trigger and have your client fail. If your client code is written to handle errors and log them, you can find out what causes the issue that way.

CodePudding user response：

One thing you can try is using a trigger and testing for the specific column being updated with if update(column).

You can then capture some diagnositc data such as the following into a logging table:

select @@Spid, r.plan_handle, p.program_name, p.loginame, b.event_info
from sys.dm_exec_requests r 
join sys.sysprocesses p on p.spid=r.session_id
cross apply sys.dm_exec_input_buffer(r.session_id, r.request_id)b
where session_id=@@Spid

CodePudding user response：

Something like this may work, if you create the correct set of columns in some kind of logging table:

IF EXISTS (SELECT 1 FROM inserted WHERE LEN(ProblemColumn) = 0)
BEGIN
  INSERT INTO dbo.SomeLoggingTable(cols)
    SELECT getdate(), i.key, buf.* 
    FROM inserted AS i
    CROSS APPLY sys.dm_exec_input_buffer(@@SPID, NULL)
    WHERE LEN(ProblemColumn) = 0;
END