Creating audit triggers in SQL Server

I just want to call out couple of points:

Use code generators You can't have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation to generate the code from XML, and the XML can be generated automatically from metadata. This allows you to easily maintain the triggers by regenerating them each time you make a change to the audit logic/structure or a target table is added/altered.

Consider capacity planning for the audit. An audit table that tracks all value changes will be, by far, the biggest table in the database: it will contain all the current data and all the history of the current data. Such a table will increase the database size by 2-3 orders of magnitude (x10, x100). And the audit table will quickly become the bottleneck of everything:

• every DML operation will require locks in the audit table
• all administrative and maintenance operations will have to accommodate the size of the database due to audit

Take into account the schema changes. A table named 'Foo' may be dropped and later a different table named 'Foo' may be created. The audit trail has to be able to distinguish the two different objects. Better use a slow changing dimension approach.

Consider the need to efficiently delete audit records. When the retention period dictated by your application subject policies is due, you need to be able to delete the due audit records. It may not seem such a big deal now, but 5 years later when the first records are due the audit table has grown to 9.5TB it may be a problem.

Consider the need to query the audit. The audit table structure has to be prepared to respond efficiently to the queries on audit. If your audit cannot be queried then it has no value. The queries will be entirely driven by your requirements and only you know those, but most audit records are queried for time intervals ('what changes occurred between 7pm and 8pm yesterday?'), by object ('what changes occurred to this record in this table?') or by author ('what changes did Bob in the database?').

There is no generic way to do it the way you want. Ultimately you end up writing reams of code for each table. Not to mention it can be fairy slow if you need to compare each column for change.

Also the fact that you might be updating multiple rows at the same time implies you need to open a cursor to loop through all the records.

The way I'd do it will be using table with structure identical to the tables you are tracking and unpivot it later to show which columns have actually changed. I'd also keep track of the session that actually did the change. This assumes that you have primary key in the table being tracked.

So given a table like this

CREATE TABLE TestTable  
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,  
Name2 NVARCHAR(40))

I'd create an audit table like this in the audit schmea.

CREATE TABLE Audit.TestTable  
(SessionID UNIQUEIDENTIFER NOT NULL,  
ID INT NOT NULL,
Name1  NVARCHAR(40) NOT NULL,  
Name2  NVARCHAR(40),  
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),  
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),  
ChangedDate DATETIME NOT NULL Default GETDATE(),  
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())

And a trigger for Update like this

CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS  
BEGIN  
    SET NOCOUNT ON
    DECLARE @SessionID UNIQUEIDENTIFER
    SET @SessionID = NEWID()
    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted

    INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
    SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted

END

This runs quite fast. During reporting , you simply join the rows based on sessionID, and Primary key and produce a report. Alternatively you can have a batch job that periodically goes through all the tables in the audit table and prepare a name-value pair showing the changes.

HTH

It looks simple and should work well until you have image/varbinary etc elements in your tables You have whole old record and whole new record as xml. Should also work properly for inserting multiple columns at 1 batch.

CREATE TABLE _AuditTable
(Aud_Id int identity(1,1) primary key,
Aud_TableName varchar(100), 
Aud_ActionType char(1),
Aud_Username varchar(100),
Aud_OLDValues xml, 
Aud_NEWValues xml,
Aud_OperationDate datetime DEFAULT GETDATE()
)

And trigger code

CREATE TRIGGER _test2_InsertUpdate on _test2
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
 IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) 
    RETURN;

declare @tablename varchar(100)
SELECT @tablename = OBJECT_NAME(parent_object_id) 
             FROM sys.objects 
             WHERE sys.objects.name = OBJECT_NAME(@@PROCID)

/*Action*/
DECLARE @ActionType char(1)
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @ActionType = 'U'
       ELSE
               SELECT @ActionType = 'I'
ELSE
       SELECT @ActionType = 'D'

declare @inserted xml, @deleted xml 
SET @inserted = (SELECT * FROM inserted FOR XML PATH)
SET @deleted = (SELECT * FROM deleted FOR XML PATH)

             INSERT INTO _AuditTable(Aud_TableName, Aud_ActionType, Aud_Username, Aud_OLDValues, Aud_NEWValues)
             SELECT @tablename, @ActionType, SUSER_SNAME(), @deleted, @inserted
END

OUTPUT

Aud_Id | Aud_TableName  | Aud_ActionType | Aud_Username | Aud_OLDValues | Aud_NEWValues |   Aud_OperationDate
1      |_test2          |   I            |abc\mR        |   NULL        |<row><name>abc</name></row> |  2018-11-07 12:38:34.937