RSS

#29 Dynamic Audit Trail on any MSSQL Table

10 May

Recently one of my client asked me to have audit trail on their ERP data on creditor and debtor master. BY Default in the system there was no such Audit trail available. So I end up creating a trigger that will capture the detail of any column changes in debtor and creditor table and store in a separate table called “AUDIT_TRAIL”.

I made this trigger as dynamic as possible so that this can be used on any table that we want to Audit. I am sharing following script that will create a trigger on “DR_ACCS” table.

 

‘*********************************************************************

CREATE TRIGGER [dbo].[Audit_DRACCS]

ON [dbo].[DR_ACCS]

FOR UPDATE

AS

BEGIN

SET NOCOUNT ON

/* */

— Declare Variables

Declare @LoopCounter INT, @MaxCOL INT, @COLNAME varchar(50)

Declare @OLDVALUE varchar(100), @NEWVALUE varchar(100)

Declare @SQLSTR as varchar(500)

 

if not exists (select * from sysobjects where name=’temp_GlobalVariables’ and xtype=’U’)

CREATE TABLE temp_GlobalVariables( Oldvalue VARCHAR(100),Newvalue Varchar(100))

 

if not exists (select * from sysobjects where name=’AUDIT_TRAIL’ and xtype=’U’)

CREATE TABLE [AUDIT_TRAIL]([SEQNO] [int] IDENTITY(1,1) NOT NULL,[TABLENAME] [varchar](60) NULL,[LINE_SEQNO] [int] NULL,[FIELDNAME] [varchar](60) NULL,[OLD_VALUE] [varchar](100) NULL,[NEW_VALUE] [varchar](100) NULL,[MODIFIEDBY] [varchar](100) NULL,[MODIFIEDDATE] [datetime] NULL,PRIMARY KEY CLUSTERED ([SEQNO] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

 

SELECT @LoopCounter = Min(ORDINAL_POSITION), @MaxCOL = max(ORDINAL_POSITION) FROM

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’DR_ACCS’

 

WHILE(@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxCOL)

BEGIN

SELECT @COLNAME = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION = @LoopCounter and TABLE_NAME = N’DR_ACCS’

Set @SQLSTR = ‘Delete from temp_GlobalVariables’

EXEC(@SQLSTR)

if exists (select * from sysobjects where name=’TEMPACC’ and xtype=’U’)   DROP table TEMPACC

 

Select * into TEMPACC from Deleted

 

Set @SQLSTR =’Insert into temp_GlobalVariables (Oldvalue,Newvalue) (select D.’ + @COLNAME + ‘,I.’ + @COLNAME + ‘ from TEMPACC D inner join DR_ACCS I on D.ACCNO = I.ACCNO)’

EXEC(@SQLSTR)

 

if (Select top 1 Oldvalue from temp_GlobalVariables)<> (Select top 1 Newvalue from temp_GlobalVariables) and @COLNAME not in (‘LAST_UPDATED’,’AGEDBAL0′,’AGEDBAL1′,’AGEDBAL2′,’BALANCE’,’LASTMONTH’,’MONTHVAL’,’PRIOR_AGEDBAL0′,’PRIOR_BALANCE’,’YEARVAL’)

Begin

INSERT INTO [dbo].[AUDIT_TRAIL] ([TABLENAME],[LINE_SEQNO],[FIELDNAME],[OLD_VALUE],[NEW_VALUE],[MODIFIEDBY],[MODIFIEDDATE])

(Select ‘DR_ACCS’,i.ACCNO, @COLNAME , (Select top 1 Oldvalue from temp_GlobalVariables), (Select top 1 Newvalue from temp_GlobalVariables),HOST_NAME(),GETDATE() from TEMPACC I inner join DR_ACCS D on D.ACCNO = I.ACCNO )

End

SET @LoopCounter = @LoopCounter + 1

END

END

 

 

‘****************************************************************************

 

This trigger automatically create table called “Audit_Trail” if not there in the database. You can also avoid any column that you do not want to track by adding a column name in the “if condition”.

Note: Yes this process is resource incentive and will affect the performance of updating the data in respective table. So it is advised to not apply this on the transaction table but if required you can.

I have written this in short time so there may be lots of improvement options that will make this trigger efficient.

Aggyey

Advertisements
 
Leave a comment

Posted by on May 10, 2016 in ERP

 

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

 
%d bloggers like this: