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
In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.
Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.
I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.
I wrote few articles on audit trails and would prefer to keep log in separate table even I see many times how to keep update track records in same table so I thought to create one example and share it with my readers.
CREATE TABLE STATSofTable
Status INT NOT NULL,
–insert into stats table
INSERT INTO STATSofTable (status,LOGS)
SELECT 1,‘TEST’ UNION ALL
SELECT 2,‘TEST’ UNION ALL
–CREATE TRIGGER TO UPDATE LOG AUTOMATICALLY
CREATE TRIGGER AuditTrailOnSTATSofTable ON STATSofTable
INSTEAD OF UPDATE
SET NOCOUNT ON
DECLARE @OldValue VARCHAR(1)
DECLARE @NewValue VARCHAR(1)
DECLARE @OldLog VARCHAR(max)
DECLARE @NewLog VARCHAR(max)
SELECT @OldValue = Status FROM Deleted
SELECT @NewValue = Status FROM INSERTED
SELECT @OldLog= LOGS from Deleted
SET @NewLog=@OldLog + ‘ STATUS CHANGE FROM ‘ + @OldValue + ‘ TO ‘+ @NewValue + ‘ ON ‘ + CONVERT(VARCHAR(50), getdate())
UPDATE STATS SET Status=@NewValue, LOGS=@NewLog WHERE Status=@OldValue
update STATSofTable set status=4 where status =1
update STATSofTable set status=1 where status =4
select * from STATSofTable
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()),
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,
— have few records in above table
INSERT INTO AuditDemo
SELECT ‘Ritesh’,‘eChem’ UNION ALL
–LET us create fixed audit trail trigger
create TRIGGER AUDITonAuditDemo
–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 AuditTrail
Hope you have enjoyed!!!!
Reference: Ritesh Shah