TSql – Auditing Row Deletions with Triggers

June 13, 2012 nateb Sql, Technology, 0

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

 

Simple Tool to Run Visual Studio Unit Tests without MsTest The Courious Case of the Bungie Interview