Audit the data changes using triggers


Hi Every one

Although SQL Server have introduced the functionality of audit on data itself, it is not available in all the versions except for the enterprise endition. As a alternative we can keep a trigger on each table for writing the before and after data. The below code consists of a table audit_change_log which will hold the data and the SP uspAudit will create the triggers on the tables.

if exists ( select 1 from sys.tables where name = 'Audit_Change_log')
    drop table Audit_Change_log
GO

create table Audit_Change_log
(
     ID             BIGINT          IDENTITY(1,1)
    ,tableName      Varchar(100)    NOT NULL
    ,previousData   VARCHAR(MAX)    NULL
    ,CurrentData    VARCHAR(MAX)    NULL
    ,UpdtTime       DATETIME        DEFAULT GETDATE()
    ,operation      VARCHAR(20)     NULL
)

GO

IF EXISTS ( SELECT 1 FROM sys.procedures where name = 'uspAudit')
    DROP procedure uspAudit
GO

CREATE procedure uspAudit
(
     @action         CHAR(1) = 'C'
)
AS
BEGIN
    DECLARE @SQL        NVARCHAR(4000)
    DECLARE @SQ         NVARCHAR(4)
    DECLARE @tblName    NVARCHAR(500)
    BEGIN TRY
        SET @SQ = ''''
        DECLARE abcd CURSOR
        FOR SELECT name FROM sys.tables where name <> 'Audit_Change_log'
        OPEN abcd
        FETCH abcd into @tblName
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF NOT EXISTS ( SELECT 1 FROM information_schema.columns where table_name = @tblName AND Data_type in ('text','ntext','image'))
            BEGIN
                SELECT @SQL = 'IF OBJECT_ID (' + @SQ + 'trg_' + @tblName + @SQ + ',' + @SQ + 'TR' + @SQ +') IS NOT NULL DROP TRIGGER ' + 'trg_'+@tblName + ';'
                --select @SQL
                EXEC(@SQL)
                SELECT @SQL = 'CREATE TRIGGER trg_'+@tblName+' ON ' + @tblName + ' AFTER INSERT, UPDATE, DELETE AS ' +
                                + ' INSERT INTO  Audit_Change_log (tableName,previousData,CurrentData) SELECT ' + @SQ +  @tblName + @SQ + 'AS tableName '
                                +', (select * from deleted for XML AUTO) AS previousData, (select * from inserted for XML AUTO) as CurrentData; '
                                + ' UPDATE  Audit_Change_log SET  operation = CASE  WHEN previousData IS NULL THEN ' + @SQ +  'Insert'  + @SQ
                                + ' WHEN CurrentData IS NULL THEN ' + @SQ + 'Delete' + @SQ
                                + ' ELSE ' + @SQ + 'Update' + @SQ + ' END WHERE ID = @@IDENTITY ;'
                --select @SQL
                EXEC (@SQL)
            END
            ELSE
            BEGIN
                SELECT @SQL = 'IF OBJECT_ID (' + @SQ + 'trg_' + @tblName + @SQ + ',' + @SQ + 'TR' + @SQ +') IS NOT NULL DROP TRIGGER ' + 'trg_'+@tblName + ';'
                EXEC(@SQL)
                SELECT @SQL = 'CREATE TRIGGER trg_'+@tblName+' ON ' + @tblName + ' AFTER INSERT, UPDATE, DELETE AS ' +
                                + ' INSERT INTO  Audit_Change_log (tableName,previousData,CurrentData) SELECT ' + @SQ +  @tblName + @SQ + 'AS tableName '
                                +', (select '  + (select stuff((select ','+column_name from information_schema.columns where data_type not in ('text','ntext','image') and table_name = @tblName for xml path('')  ),1,1,''))
                                + ' from deleted for XML AUTO) AS previousData'
                                + ', (select '+  (select stuff((select ','+column_name from information_schema.columns where data_type not in ('text','ntext','image') and table_name = @tblName for xml path('')  ),1,1,''))
                                + ' from inserted for XML AUTO) as CurrentData;'
                                + ' UPDATE  Audit_Change_log SET  operation = CASE  WHEN previousData IS NULL THEN ' + @SQ +  'Insert'  + @SQ
                                + ' WHEN CurrentData IS NULL THEN ' + @SQ + 'Delete' + @SQ
                                + ' ELSE ' + @SQ + 'Update' + @SQ + ' END WHERE ID = @@IDENTITY ;'
                EXEC (@SQL)

            END
            FETCH abcd into @tblName
        END
        CLOSE abcd
        DEALLOCATE abcd
    END TRY
    BEGIN CATCH
        CLOSE abcd
        DEALLOCATE abcd
        SELECT CAST(ERROR_LINE() AS VARCHAR) +':' +      ERROR_MESSAGE()
    END CATCH
END
GO

EXEC uspAudit
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s