1

I'm working with a decently large software platform that utilizes Microsoft SQL Server 2008 R2. I'm investigating a very rare bug where something in the database is updating ContactInfo's primary key (CID) to 0. The table has two primary keys. CID is related to a primary key in another table to store contact information.

Is there a way to make an existing update trigger capture what stored procedure is making an update to the table? Or even better, is there a way to capture Profiler data in an audit table, such as stored procedure execution statement with input parameters? We could continuously run a Profiler trace to try to catch the update in real time but the the infrequency of the bug would result in at least a few hundred gigs of trace data to be stored, which is not an option.

Below is my code for the existing audit table. There are 28 columns, so I just replaced them with [columns] for simplicity and space.

ALTER TRIGGER [dbo].[wt_ContactInfo_U] 
ON [dbo].[ContactInfo]
FOR UPDATE AS 
    INSERT INTO [dbo].[ContactInfo_AUDIT] ( // columns )
        SELECT // columns
        FROM Deleted D
        WHERE
            CHECKSUM( // D.[columns]) NOT IN (SELECT CHECKSUM( // I.[columns])
                                              FROM INSERTED I
                                              WHERE I.[CID] = D.[CID])
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Arch
  • 31
  • 2
  • The [COLUMNS_UPDATED](https://learn.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-ver15) pattern might point you to a specific update statement? – Dale K Apr 21 '20 at 00:31
  • 2
    And [CONTEXT_INFO](https://learn.microsoft.com/en-us/sql/t-sql/functions/context-info-transact-sql?view=sql-server-ver15) can be used to determine who caused the `UPDATE` - the downside is you have to set the `CONTEXT_INFO` with some identifying information in *all* the calling procedures. – Dale K Apr 21 '20 at 00:33
  • 3
    OR (haha) you could just throw an error when an attempt is made to update to ID 0. Hopefully the calling App will log the error and notify someone? :) – Dale K Apr 21 '20 at 00:40
  • 1
    I assume you've tried [this](https://stackoverflow.com/questions/14467961/how-to-get-the-stored-procedure-name-from-a-trigger-when-an-update-operation-was)? – Dale K Apr 21 '20 at 01:16
  • I never came across the last link with all the searching I've done today. I'll definitely be trying that soon. As for the other two items, I'll check into those if that doesn't work. – Arch Apr 21 '20 at 04:14

0 Answers0