Creating audit history for your table(s) in SQL Server

We’re wanting to capture audit history on our table(s) in our database. A good way to do this is via triggers.

Let’s create ourselves an Audit table first, something like this

CREATE TABLE [dbo].[Audit](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Operation] [nvarchar](3) NOT NULL,
  [DateTime] [datetime] NOT NULL,
  [Before] [nvarchar](256) NULL,
  [After] [nvarchar](256) NULL,
) ON [PRIMARY]

The Operation will be used to store a value from “UPD”, “DEL” and “INS”. In other words the audit record is an Update, Delete or Insert. The DateTime is the date and time the operation took place, Before is the data before it’s changed, After the data it changed to and a Description field for a bit more information.

I’ve not included this field, but most likely we’ll also want to have a Who field for who made the change.

In my little example I have a table named Scale which is used to store musical scales. Just for completeness let’s take a look at the Scale table

CREATE TABLE [dbo].[Scale](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](256) NOT NULL,
  [Intervals] [nvarchar](256) NOT NULL,
  [Description] [nvarchar](256) NULL
) ON [PRIMARY]

Now I’m just going to add triggers to this one table, however you might have such triggers on multiple tables. We will want to have a trigger for deletion, insertion and updates.

For the delete trigger we might use something like this

ALTER TRIGGER [dbo].[ScaleDelete]
ON [dbo].[Scale]
FOR DELETE
AS
  INSERT INTO dbo.Audit(Operation, DateTime, Before)
  SELECT 'DEL', GETDATE(), (select d.Name, d.Description, d.Intervals from deleted d FOR JSON PATH)
  FROM deleted

For a deletion we insert the operation, date/time and a description into the audit table using the deleted data.

For insertions we might have something like this

ALTER TRIGGER [dbo].[ScaleInsert] 
ON [dbo].[Scale]
FOR INSERT
AS
  INSERT INTO dbo.Audit(Operation, DateTime, After)
  SELECT 'INS', GETDATE(), (select i.Name, i.Description, i.Intervals from inserted i FOR JSON PATH)
  FROM inserted;

In this case we take the data from the insert data and passing parts of this data to the audit table (similar to the deletion).

For the update trigger we might write something similar to the above, but any time the UPDATE SQL command is called the audit log will be added to, even if there were not changes. It would, therefore, be nice if we only updated the audit table when real changes are detected.

In the case of an update we get both inserted and deleted data. So we can check whether the inserted differs from deleted using the EXCEPT SQL command. Then, only if a difference has been found, will we update the audit table. For fun, we’re also going to turn the deleted and inserted data into JSON and store the whole row to in the Before and After fields of the audit table.

CREATE TRIGGER [dbo].[ScaleUpdate]
ON [dbo].[Scale]
AFTER UPDATE
AS
IF EXISTS (
  SELECT Name, Description, Intervals FROM inserted
  EXCEPT
  SELECT Name, Description, Intervals FROM deleted
)
BEGIN
  INSERT INTO dbo.Audit(Operation, DateTime, Before, After)
  SELECT 'UPD', GETDATE(), 
  (select d.Name, d.Description, d.Intervals from deleted d FOR JSON PATH), 
  (select i.Name, i.Description, i.Intervals from inserted i FOR JSON PATH)
  FROM inserted i
  join deleted d on (i.Id = d.Id)
END

If you’d prefer to create XML output you can replace JSON PATH to XML RAW.

Now to test these we can use

DELETE from Scale where Name = 'Chromatic'

INSERT INTO Scale (Name, Intervals)
VALUES ('Chromatic', '1,1,1,1,1,1,1,1,1,1,1,1')

UPDATE Scale
SET Intervals = '1,1,1,1,1,1,1,1,1,1,1,2'
WHERE Name = 'Chromatic'