Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/articles/sql-server/)
-   -   Audit Trail in SQL Server 2000 or SQL Server 7 (http://www.go4expert.com/articles/audit-trail-sql-server-2000-sql-server-7-t301/)

shabbir 16May2005 06:31

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


clocking 29Sep2007 13:41

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
oh, you're sql expert, too. How many capacity A forum like yours often needs?
And to work, what do it need more?

imrantechi 15Feb2008 19:13

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
it really helps a new sql developer to learn a lot

lead.smart34 26Feb2008 17:44

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
sql tough,very tough to understand

subashini 8Apr2008 19:37

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
Very good article. Thanks for this nice info

dotnetexpert 17May2008 12:53

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
It's a nice article.

dealwi8me 12Feb2009 14:30

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
Nice article thank you for posting it.

shabbir 12Feb2009 14:50

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
Quote:

Originally Posted by dealwi8me (Post 42817)
Nice article thank you for posting it.

My Pleasure.

virender.ets 22Jan2011 16:48

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
It's really very helpful for SQL development.
Thanks

Tobiasgar 6Sep2011 19:20

Re: Audit Trail in SQL Server 2000 or SQL Server 7
 
Thanx for sharing information and helping people)) Highly appreciate your work:D


All times are GMT +5.5. The time now is 12:21.