Audit SQL Tables with Trigger

Say , you want to audit every record changes in your database tables and also, you need an easier way to query these data whenever needed, I present you, a sample SQL trigger for inserting a new audit record to audit table when an insert, update or delete operation is done on a table row.

https://www.simple-talk.com/sql/database-administration/auditing-ddl-changes-in-sql-server-databases/


First, the audit table: 

CREATE TABLE [dbo].[AuditRecord](
[ID] [int] IDENTITY(1,1) NOT NULL,
   [AuditType] [char](1) NOT NULL,
   [TableName] [nvarchar](50) NOT NULL,
   [OperationTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_AuditRecord_OperationTimeStamp]  DEFAULT (getdate()),
   [UserName] [nvarchar](50) NOT NULL,
   [Data] [xml] NOT NULL,
CONSTRAINT [PK_AuditRecord] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


[AuditType] is either I (Insert), U (Update), or D (Delete)
[Data] is an XML field, which will contain all the IUD operations nicely formatted into XML. 

Following is the sample XML



<dbo.TestGroup>
   <row user="me" type="U" timestamp="2015-11-23T10:06:30.913">
      <TestGroupID>1</TestGroupID>
      <Name old="Performance" new="System Performance" /> <!-- notice the update -->
   </row> 
   <row user="me" type="U" timestamp="2015-11-23T10:06:30.913">
      <TestGroupID>6</TestGroupID>  <!-- user tried to update without any changes! -->
   </row> 
   <row user="me" type="U" timestamp="2015-11-23T10:06:30.913">
      <TestGroupID>1</TestGroupID>
      <Active old="0" new="1" />  <!-- notice the update -->
   </row>
   <row user="me" type="D" timestamp="2015-11-23T11:24:29.963">
      <TestGroupID>4</TestGroupID>
      <Name old="Test" /> <!-- notice the delete, there is no new value -->
      <Active old="1" />
   </row>
   <row user="me" type="I" timestamp="2015-11-23T11:26:18.180">
      <TestGroupID>7</TestGroupID>
      <Name new="Latency" /> <!-- notice the insert, there is no old value -->
      <Active new="1" />
   </row>
</dbo.TestGroup>


This is the trigger. (Note that, this trigger was generated using a T4 template. Its easier than hand-coding all the columns)


IF EXISTS (SELECT *
           FROM   dbo.sysobjects
           WHERE  id = object_id(N'[dbo].[TestGroup_audit]')
                  AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
    DROP TRIGGER [dbo].[TestGroup_audit];


GO
ALTER TRIGGER TestGroup_audit
    ON [dbo].[TestGroup]
    AFTER INSERT, UPDATE, DELETE
    AS DECLARE @type AS VARCHAR (1), 
@UpdateDate AS DATETIME, 
@UserName AS VARCHAR (128), 
@audit_data AS XML;
       IF EXISTS (SELECT *
                  FROM   inserted)
          AND EXISTS (SELECT *
                      FROM   deleted)
           SELECT @type = 'U';
       ELSE
           IF EXISTS (SELECT *
                      FROM   inserted)
               SELECT @type = 'I';
           ELSE
               SELECT @type = 'D';
       SELECT @UpdateDate = getdate(),
              @UserName = SYSTEM_USER;
       SELECT @audit_data = (SELECT @UserName AS '@user', -- select as row attribute
                                    @type AS '@type', -- select as row attribute
                                    @UpdateDate AS '@timestamp', -- select as row attribute
                                    COALESCE (d.[TestGroupID], i.[TestGroupID]) AS 'TestGroupID', -- select as element
                                    (CASE 
WHEN d.[Name] = i.[Name] THEN NULL ELSE d.[Name] 
END) AS 'Name/@old', -- add old value as attribute if the old value has changed
                                    (CASE 
WHEN d.[Name] = i.[Name] THEN NULL ELSE i.[Name] 
END) AS 'Name/@new', -- add new value as attribute if the new value has changed
                                    (CASE 
WHEN d.[Active] = i.[Active] THEN NULL ELSE d.[Active] 
END) AS 'Active/@old',
                                    (CASE 
WHEN d.[Active] = i.[Active] THEN NULL ELSE i.[Active] 
END) AS 'Active/@new'
                             FROM   inserted AS i
                                    FULL OUTER JOIN
                                    deleted AS d
                                    ON i.TestGroupID = d.TestGroupID
                             FOR    XML PATH ('row'), ROOT ('dbo.TestGroup')); -- root is table name, row is each modified/created row
       INSERT  INTO [dbo].[AuditRecord] ([AuditType], [TableName], [OperationTimeStamp], [UserName], [Data])
       VALUES                          (@type, 'TestGroup', @UpdateDate, @UserName, @audit_data);

I think the trigger is simple enough, but, if you need an explanation, do post in comments.

Gist: Audit SQL Tables with Trigger

Popular posts from this blog

Print a receipt using a Thermal Printer with C#.NET

Automatic redirect upon session timeout using ASP.NET MVC and Javascript

Complex Master-Detail Form using Knockout.js and ASP.NET MVC