Capturing audit table values inline a.k.a Composable DML

Auditing is term we come across quite often in our projects where we need to track changes happening to data over a period of time. This article discusses how methodology of capturing audit information has evolved over various versions of SQL server.

SQL 2000 started with capturing the audit information by means of trigger code. We used to write trigger for capturing old as well as new values of the table for DML operations like INSERT,UPDATE & DELETE. This was done by means of two internal temporary tables INSERTED and DELETED which holds changed and old values respectively. Inside the trigger, we used to populate audit table from above two tables based on type of DML manipulation.

With the arrival of SQL 2005 the internal tables INSERTED and DELETED were exposed outside the trigger code by means of OUTPUT operator and we could capture the values to populate a temporary table and capture the audit information from it.So tracking audit changes became as simple as given below

DECLARE @Audit_Temp table

(

ID int,

NewColumn1 varchar(100),

OldColumn1 varchar(100),

NewColumn2 int,

OldColumn2 int,

NewColumn3 datetime,

OldColumn3 datetime,

...

)

UPDATE t

SET t.Column1=s.Column1,

t.Column2 = s.Column2,

t.Column3 = s.Column3,

..

OUTPUT INSERTED.Column1,

DELETED.Column1,

INSERTED.Column2,

DELETED.Column2,

INSERTED.Column3,

DELETED.Column3 ...

INTO @Audit_Temp

(

NewColumn1,

OldColumn1,

NewColumn2,

OldColumn2,

NewColumn3,

OldColumn3 ,

...

)

FROM Table1 t

INNER JOIN SourceTable s

ON s.PK=t.FK

then you can apply additional logic while inserting from this temporary audit table to your actual audit table

INSERT INTO Table_Audit ( Column1,Column2,Column3,..)

SELECT NewColumn1, NewColumn2, NewColumn3,..

FROM @Audit_Temp

WHERE OldColumn1 <> NewColumn1

OR OldColumn2 <> NewColumn2

...

SQL 2008 has gone one step forward wherein we're able to do this manipulation inline in main query by means of new feature called Composable DML. The main UPDATE will be nested inside a select which directly does the manipulation and populates the final audit table eliminating need for temporarily storing results of OUTPUT. So using composable DML feature the above batch reduces to single statement below

INSERT INTO Table_Audit ( Column1,Column2,Column3,..)

SELECT NewColumn1, NewColumn2, NewColumn3,..

FROM

(

UPDATE t

SET t.Column1=s.Column1,

t.Column2 = s.Column2,

t.Column3 = s.Column3,

..

OUTPUT INSERTED.Column1 AS NewColumn1,

DELETED.Column1 AS OldColumn1,

INSERTED.Column2 AS NewColumn2,

DELETED.Column2 AS OldColumn2,

INSERTED.Column3 AS NewColumn3,

DELETED.Column3 AS OldColumn3 ...

FROM Table1 t

INNER JOIN SourceTable s

ON s.PK=t.FK

)t

WHERE OldColumn1 <> NewColumn1

OR OldColumn2 <> NewColumn2

...