If you are new to this concept in database world than you might think what is Auditing Trail? The answer is pretty much easy and simple, if you want to increase the data-integrity and wants to have full details about insertion, edition and deletion of your data; Audit Trail is the concept you have to adopt.
Since this is a very huge topic and not possible to cover complete topic in one or may be in few articles, I will give some basic details here and will write some more article with live situation as and when time permits.
You would like to keep track of total update and delete in records after its insert, you may want which front-end accessed your row for modification, which user has changed which records.There are many methods popular to keep track of your records change like you can have duplicate table for each table along with some comment field sand keep the track and old value. You may wish to create one table which will have details off all tables and about every single transaction but In that case, maintenance of that table is very crucial.
Let us create one table and try to keep trail of database.--demo table for AuditTrail
--Note: this is just for demo purpose, in live situation we may have very big table than-- on explained here. everybody has their own need and situation.
CREATE TABLE AuditTrail(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT def_AuditTrail_Id DEFAULT(NEWID()),
AuditDate DATETIME,
TableName VARCHAR(50) NOT NULL,
ColumnName VARCHAR(50) NOT NULL,
Description VARCHAR(50) NOT NULL,
UserName VARCHAR(50) NOT NULL,
OldValue VARCHAR(50) NOT NULL,
NewValue VARCHAR(50) NOT NULL
)
--Now we will create one more table for which we will keep the trail in above table
--You can keep Audit of Insert, Delete and Update everything in above table
--and can customize it as per your need
CREATE TABLE AuditDemo
(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(10),
Company VARCHAR(10)
-- have few records in above table
INSERT INTO AuditDemo
SELECT 'Ritesh','eChem' UNION ALL
--LET us create fixed audit trail trigger
create TRIGGER AUDITonAuditDemo
ON AuditDemo
AFTER UPDATE
AS
IF UPDATE(Name)
BEGIN
INSERT AuditTrail
(AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)
SELECT
GETDATE(),'AuditDemo','Name','Update',suser_sname(),DELETED.Name,INSERTED.Name
FROM INSERTED
JOIN DELETED
ON INSERTED.ID=DELETED.ID
END
IF UPDATE(company)
BEGIN
INSERT AuditTrail
(AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)
SELECT
GETDATE(),'AuditDemo','Company','Update',suser_sname(),Deleted.Company,Inserted.Company
FROM INSERTED
JOIN DELETED
ON INSERTED.ID=DELETED.ID
END
--let us update record in AuditDemo tabel
UPDATE AuditDemo SET Name='R.Shah' WHERE ID=2
--LET US NOW CHECK WHETHER WE HAVE TRAILED THE RECORDS OR NOT
SELECT * FROM AuditTrail
--let use update and check once agian
UPDATE AuditDemo SET Company='Testing'
SELECT * FROM AuditTrailHope you have enjoyed!!!!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
3 comments:
Thanks for the post. But if you want to do this for a table of 20 fields? do u have to do all 20 fields separately?
Yes, you have to, unfortunately there is no short way, may be you can do it by some dynamic SQL
related post http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html
Post a Comment