Audit Trail with OUTPUT clause in SQL Server 2005/2008/Denali

Monday, July 4, 2011 |


Audit Trail is one of the task client asked for in most of the project so that they can keep watch on crucial fields regarding its change.

I have seen many small & few medium sized companies is still not hiring SQL Server professional and used to go ahead with .NET developer. .NET developer generally keep their knowledge updated with .NET technology but they lack of knowledge in SQL Server domain so for Audit Trail, they still rely on old pseudo table (INSERTED, DELETED) accessible in Trigger. But in SQL Server 2005 and later, you don’t even need to rely on Triggers for Audit Trail but you can use new “OUTPUT” clause.

“OUTPUT” clause is still much underutilized feature even after 7 years. That is the reason I am writing on Audit trail and OUTPUT clause once again.

Within “OUTPUT” clause, you can access pseudo table and generate your “Audit Trail” log. I will show you with an example.

We will have one table of Test comes to an environmental company to analyze, if client change the Test s/he wants to perform, we have to keep trail on that, if he cancel any test, we have to keep trail on that too.

--we will keep "Audit Trail" for below given table
create table ClientOrder
(
      OrderID varchar(5)
      ,ClientID varchar(5)
      ,Test varchar(20)
      ,OrderDate datetime default getdate()
)
GO

--following is the table, we will keep "Audit Trail" in.
--this will keep track of all data changed
CREATE TABLE AuditOfOrder
(
      Id INT Identity(1,1)
      ,OrderID varchar(50)
      ,OldTest varchar(20)
      ,NewTest varchar(20)
      ,DMLPerformed varchar(15)
      ,ChangeDate datetime default getdate()
)
GO


--inserting data in "ClientOrder" table
--all insert will be stored in audit trail table too via "OUTPUT" clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES('A1001','CHEM1','VOCMS Group1')
GO

--inserting data in "ClientOrder" table
--all insert will be stored in audit trail table too via "OUTPUT" clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES('A1001','CHEM1','Pesticide Group1')
GO

--let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go

--we will perform UPDATE on "ClientOrder" table
--which will be recorded in "AuditOfOrder" table too
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
UPDATE ClientOrder
      SET Test ='SVOC Stars'
OUTPUT
      inserted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      inserted.Test AS NewTest,
      'UPDATE' as DMLPerformed
WHERE ClientOrder.Test='VOCMS Group1'
) t
GO

--let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go

--Finally the log of Delete will be stored in the same way.
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
DELETE FROM ClientOrder
OUTPUT
      deleted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      deleted.Test AS NewTest,
      'DELETE' as DMLPerformed
WHERE ClientOrder.Test='SVOC Stars'
) t
GO

--let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go

Isn’t this interesting? Start using it.

BTW, I have previously written some articles regarding Audit Trail techniques (old & new both), if you want to refer it, have a look at below links:


Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

0 comments: