Who changed the data?

Frankly I was stumped! I had been through all the procedures that mentioned the table, I had scrolled through jobs, and audit logs. I had even looked through code embedded in reports. But I just could not see how a Customers title was being changed from ‘Mrs’ to ‘Ms’ in a report.

My last resort, and one I wish I had thought of sooner, was to put a trigger on the table to stop the specific text appearing. Here is the code:-

CREATE TRIGGER [dbo].[SALES_BY_SITE_stop] 
ON [dbo].[SALES_BY_SITE] 
AFTER INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT 1 FROM INSERTED 
   WHERE Customer = 'Ms SomeName')
   BEGIN
      ROLLBACK TRANSACTION
      RAISERROR ('"Ms SomeName" was blocked',16,1)
   END
END

Now it has to be said – it is generally bad practice to break something to fix something.

Luckily, it turned out that within a few minutes an email popped-up to say a job had failed. So I immediately removed the trigger and re-ran the job.

I then spent the next few hours trawling through some procedures and views to a base table that contained … the incorrect data. Cake time! 🙂