RSS
Feed
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:
Post a Comment