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

AuditTrail is one of the task client asked for in most of the project so thatthey can keep watch on crucial fields regarding its change.
I have seen many small & few medium sized companies isstill not hiring SQL Server professional and used to go ahead with .NETdeveloper. .NET developer generally keep their knowledge updated with .NETtechnology but they lack of knowledge in SQL Server domain so for AuditTrail, they still rely on old pseudo table (INSERTED, DELETED) accessiblein Trigger. But in SQL Server 2005 and later, you don’t even need to rely onTriggers for AuditTrail but you can use new “OUTPUT” clause.
“OUTPUT” clause is still much underutilized feature even after7 years. That is the reason I am writing on Audit trail and OUTPUT clause onceagain.
Within “OUTPUT” clause, you can access pseudo table andgenerate your “AuditTrail” log. I will show you with an example.
We will have one table of Test comes to an environmentalcompany to analyze, if client change the Test s/he wants to perform, we have tokeep 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 isthe table, we will keep “Audit Trail” in.
–this will keeptrack 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 datain “ClientOrder” table
–all insertwill be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘VOCMSGroup1’)
GO
–inserting datain “ClientOrder” table
–all insertwill be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘PesticideGroup1’)
GO
–let us seewhat we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–we willperform UPDATE on “ClientOrder” table
–which will berecorded in “AuditOfOrder” table too
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
UPDATEClientOrder
      SET Test =‘SVOC Stars’
OUTPUT
      inserted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      inserted.Test AS NewTest,
      ‘UPDATE’ as DMLPerformed
WHEREClientOrder.Test=‘VOCMS Group1’
) t
GO
–let us seewhat we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–Finally thelog 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
WHEREClientOrder.Test=‘SVOC Stars’
) t
GO
–let us seewhat 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 AuditTrail techniques (old & new both), if you want to refer it, have a look atbelow links:
Reference: Ritesh Shah

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

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

AuditTrail is one of the task client asked for in most of the project so thatthey can keep watch on crucial fields regarding its change.
I have seen many small & few medium sized companies isstill not hiring SQL Server professional and used to go ahead with .NETdeveloper. .NET developer generally keep their knowledge updated with .NETtechnology but they lack of knowledge in SQL Server domain so for AuditTrail, they still rely on old pseudo table (INSERTED, DELETED) accessiblein Trigger. But in SQL Server 2005 and later, you don’t even need to rely onTriggers for AuditTrail but you can use new “OUTPUT” clause.
“OUTPUT” clause is still much underutilized feature even after7 years. That is the reason I am writing on Audit trail and OUTPUT clause onceagain.
Within “OUTPUT” clause, you can access pseudo table andgenerate your “AuditTrail” log. I will show you with an example.
We will have one table of Test comes to an environmentalcompany to analyze, if client change the Test s/he wants to perform, we have tokeep 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 isthe table, we will keep “Audit Trail” in.
–this will keeptrack 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 datain “ClientOrder” table
–all insertwill be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘VOCMSGroup1’)
GO
–inserting datain “ClientOrder” table
–all insertwill be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘PesticideGroup1’)
GO
–let us seewhat we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–we willperform UPDATE on “ClientOrder” table
–which will berecorded in “AuditOfOrder” table too
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
UPDATEClientOrder
      SET Test =‘SVOC Stars’
OUTPUT
      inserted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      inserted.Test AS NewTest,
      ‘UPDATE’ as DMLPerformed
WHEREClientOrder.Test=‘VOCMS Group1’
) t
GO
–let us seewhat we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–Finally thelog 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
WHEREClientOrder.Test=‘SVOC Stars’
) t
GO
–let us seewhat 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 AuditTrail techniques (old & new both), if you want to refer it, have a look atbelow links:
Reference: Ritesh Shah

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: