Go4Expert - Audit Trail in SQL Server 2000 or SQL Server 7

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