1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Audit Trail in SQL Server 2000 or SQL Server 7

Discussion in 'SQL Server' started by shabbir, May 16, 2005.

  1. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    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
         
     
    Last edited: May 19, 2005
  2. clocking

    clocking New Member

    Joined:
    Jun 12, 2007
    Messages:
    122
    Likes Received:
    0
    Trophy Points:
    0
    oh, you're sql expert, too. How many capacity A forum like yours often needs?
    And to work, what do it need more?
     
  3. imrantechi

    imrantechi New Member

    Joined:
    Feb 12, 2008
    Messages:
    116
    Likes Received:
    4
    Trophy Points:
    0
    it really helps a new sql developer to learn a lot
     
    shabbir likes this.
  4. lead.smart34

    lead.smart34 New Member

    Joined:
    Feb 14, 2008
    Messages:
    77
    Likes Received:
    0
    Trophy Points:
    0
    sql tough,very tough to understand
     
  5. subashini

    subashini New Member

    Joined:
    Apr 6, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Very good article. Thanks for this nice info
     
  6. dotnetexpert

    dotnetexpert New Member

    Joined:
    May 16, 2008
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    It's a nice article.
     
  7. dealwi8me

    dealwi8me New Member

    Joined:
    Feb 12, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Nice article thank you for posting it.
     
  8. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    My Pleasure.
     
  9. virender.ets

    virender.ets Banned

    Joined:
    Jan 22, 2011
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    It's really very helpful for SQL development.
    Thanks
     
  10. Tobiasgar

    Tobiasgar New Member

    Joined:
    Aug 29, 2011
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    Thanx for sharing information and helping people)) Highly appreciate your work:D
     
  11. deonrock

    deonrock New Member

    Joined:
    Jan 10, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    work at it sector!!
    Location:
    mumbai
    Home Page:
    i was looking for this coding.....
     

Share This Page