Audit Trail in SQL Server 2000 or SQL Server 7

shabbir's Avatar author of Audit Trail in SQL Server 2000 or SQL Server 7
This is an article on Audit Trail in SQL Server 2000 or SQL Server 7 in SQL Server.
Rated 5.00 By 1 users
There are many occasions where if someone updates a records or even deletes a record we need to keep the Audit trails of the record updated or deleted or even keep a history of records as what are the updates that happened on the database as well as what are the records deleted from the table. In Any database system there are triggers to achieve the same but remember that if triggers if not used efficiently can degrade the performance of the Database system

If we have a table

Code to create table
Code:
CREATE TABLE [dbo].[Details] (
    	[RegNo] [varchar] (50) NOT NULL ,
    	[RegYear] [varchar] (4) NOT NULL ,
    	[FullName] [varchar] (50) NOT NULL ,
    	[FathersName] [varchar] (50) NOT NULL ,
    	[Location] [varchar] (250) NOT NULL ,
    	[Village] [varchar] (250) NOT NULL ,
    	[PresentAdd] [varchar] (500) NOT NULL ,
    	[CreatedBy] [varchar] (50) NOT NULL ,
    	[DateCreated] [datetime] NOT NULL ,
    	[UpdatedBy] [varchar] (50) NULL ,
    	[DateUpdated] [datetime] NULL
    )
    GO
    
    ALTER TABLE [dbo].[Details] WITH NOCHECK ADD
    	CONSTRAINT [PK_Details] PRIMARY KEY  CLUSTERED
    	(
    		[RegNo],
    		[RegYear]
    	)  ON [PRIMARY]
    GO
Code to create the log table
Code:
CREATE TABLE [dbo].[logDetails] (
     	[LogType] [varchar] (50) NULL ,
     	[RegNo] [varchar] (50) NOT NULL ,
     	[RegYear] [varchar] (4) NOT NULL ,
     	[FullName] [varchar] (50) NOT NULL ,
     	[FathersName] [varchar] (50) NOT NULL ,
     	[Location] [varchar] (250) NOT NULL ,
     	[Village] [varchar] (250) NOT NULL ,
     	[PresentAdd] [varchar] (500) NOT NULL ,
     	[CreatedBy] [varchar] (50) NOT NULL ,
     	[DateCreated] [datetime] NOT NULL ,
     	[UpdatedBy] [varchar] (50) NULL ,
     	[DateUpdated] [datetime] NULL
     )
     GO
Now We need to write the trigger to create the Audit Trail / log of updation/deletion of record on Details into the logDetails table.
Code:
CREATE TRIGGER AuditDetails
     ON dbo.Details
     FOR Update,Delete AS
     
     BEGIN
     	DECLARE @auditType varchar(10)
     	-- Audit OLD record.
     	IF EXISTS (Select i.RegNo,i.RegYear from Inserted i)
     	-- Record is updated
     	BEGIN
     		SET @auditType = 'Updated'
     	END
     	ELSE
     	-- Record is deleted
     	BEGIN
     		SET @auditType = 'Deleted'
     	END
     
     	INSERT INTO logDetails
     	(logtype,
     	RegNo,
     	RegYear,
     	FullName,
     	FathersName,
     	Location,
     	Village,
     	PresentAdd,
     	CreatedBy,
     	DateCreated,
     	UpdatedBy,
     	DateUpdated
     	)
 	SELECT @auditType,
     	d.RegNo,
     	d.RegYear,
     	d.FullName,
     	d.FathersName,
     	d.Location,
     	d.Village,
     	d.PresentAdd,
     	d.CreatedBy,
     	d.DateCreated,
     	d.UpdatedBy,
     	d.DateUpdated
     	FROM deleted d
     END
     
     GO
0
clocking's Avatar, Join Date: Jun 2007
Ambitious contributor
oh, you're sql expert, too. How many capacity A forum like yours often needs?
And to work, what do it need more?
0
imrantechi's Avatar, Join Date: Feb 2008
Ambitious contributor
it really helps a new sql developer to learn a lot
0
lead.smart34's Avatar, Join Date: Feb 2008
Contributor
sql tough,very tough to understand
0
subashini's Avatar, Join Date: Apr 2008
Newbie Member
Very good article. Thanks for this nice info
0
dotnetexpert's Avatar
Light Poster
It's a nice article.
0
dealwi8me's Avatar, Join Date: Feb 2009
Newbie Member
Nice article thank you for posting it.
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Quote:
Originally Posted by dealwi8me View Post
Nice article thank you for posting it.
My Pleasure.
0
virender.ets's Avatar
Banned
It's really very helpful for SQL development.
Thanks
0
Tobiasgar's Avatar
Go4Expert Member
Thanx for sharing information and helping people)) Highly appreciate your work