TSql – Auditing Row Deletions with Triggers
Every run into the fun problem of trying to find out which of your service runners is buggy and accidently deleting records it shouldn’t ?
You could dig through 1000s of line of code or do it the easy way with a simple trigger.
Create the Audit Table
First we need to create a simple audit table that contains the the search key we are interested in along with columns for “DeleteUser”, “HostMachine”, and DeleteTime.
CREATE TABLE [dbo].[MachineDeletionAudit]( [MachineDeletionAuditId] [int] IDENTITY(1,1) NOT NULL, [MachineId] [nvarchar](50) NOT NULL, [DeleteUser] [nvarchar](50) NOT NULL, [HostMachine] [nvarchar](50) NOT NULL, [DeleteTime] [datetime] NOT NULL, CONSTRAINT [PK_MachineDeletionAudit] PRIMARY KEY CLUSTERED ( [MachineDeletionAuditId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON[PRIMARY]
Create a Trigger
Creating a trigger is fairly straight forward. The important thing to remember is the ‘magic’ tables available during the trigger’s stored procedure: updated, inserted and deleted will contain the changes.
Now for the fun part; go grab some ice cream maybe watch a movie. As soon as the repro has occurs again you’ll have the answer
CREATE TRIGGER [dbo].[MachinesVirtualDeleted] ON dbo.MachinesVirtual AFTER DELETE AS BEGIN SET NOCOUNT ON; insert into MachineDeletionAudit (MachineId,HostMachine,DeleteUser,DeleteTime) select d.MachineID,HOST_NAME(),SYSTEM_USER,GETDATE() from deleted d END GO