Sunday, January 12, 2020

How to use the Trigger for Insert, Update, Delete to Audit the Changes on a Table in SQL Server

How to use a Trigger on a Table to Audit the Insert, Update, and Delete operations in SQL Server
Scenario :
Suppose we have a Sample table, on which we wants to track the changes like the Insert, Update, and Delete operations.
The Audit details of the changes should be updated to a Audit table.
Select * From [dbo].[trgr_Sample]


Next we will create a Audit table, which we will used to stored the Audit details.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE
[dbo].[AuditDataChanges](
[RecordId] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NOT NULL,
[ColumnPK] [varchar](50) NOT NULL,
[ColumnName] [varchar](50) NOT NULL,
[OldValue] [varchar](50) NULL,
[NewValue] [varchar](50) NULL,
[ChangeDate] [datetime2](7) NOT NULL,
[ChangeType] [varchar](50) NOT NULL,
[UpdatedBy] [varchar](50) NOT NULL,
CONSTRAINT [PK_AuditDataChanges] PRIMARY KEY CLUSTERED
([RecordId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AuditDataChanges] ADD DEFAULT (getdate()) FOR [ChangeDate]
GO

Next we will create a AFTER INSERT, UPDATE, DELETE Trigger on the above Sample table where we want to fire the Trigger to track the changes.
----------------------------------------------------------
CREATE TRIGGER [dbo].[trg_AfterInsertUpdateDelete] ON [dbo].[trgr_Sample]
FOR INSERT,UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @@vTable AS TABLE(Id Int)
DECLARE @sql VARCHAR(5000)
,@sqlInserted NVARCHAR(500)
,@sqlDeleted NVARCHAR(500)
,@NewValue NVARCHAR(100)
,@OldValue NVARCHAR(100)
,@UpdatedBy VARCHAR(50)
,@ParamDelete NVARCHAR(500)
,@ParamInsert NVARCHAR(500)
,@TABLE_Name VARCHAR(100)
,@Column_Name VARCHAR(100)
,@modifiedColumnsList NVARCHAR(4000)
,@ColumnListItem NVARCHAR(500)
,@Pos INT
,@RecordPk VARCHAR(50)
,@RecordPkName VARCHAR(50)
,@EventType VARCHAR(50);

SELECT * INTO [dbo].[#Deleted] FROM Deleted;
SELECT * INTO [dbo].[#Inserted] FROM Inserted;

--Select * From Inserted
--Select * From Deleted

IF EXISTS(SELECT * FROM Inserted) 
BEGIN
INSERT INTO @@vTable ([Id]) SELECT [Order_Id] FROM Inserted
SET @EventType = 'Update'
END
ELSE IF EXISTS(SELECT * FROM Deleted)
BEGIN
INSERT INTO @@vTable ([Id]) SELECT [Order_Id] FROM Deleted;
SET @EventType = 'Delete';
END
ELSE 
BEGIN
SET @EventType = 'UnKnown';
--Print(@EventType)
END
---Change the base table '[dbo].[trgr_Sample]' to your table Name
SET @TABLE_Name = '[dbo].[trgr_Sample]'
----Cursor for Looping through each record by PrimaryKey of the base Table
DECLARE t_Cursor CURSOR
FOR
Select [Id] From @@vTable
OPEN t_Cursor
FETCH NEXT
FROM t_Cursor INTO @RecordPk 
--Print @RecordPk 
----First WHILE Loop begins here
WHILE @@FETCH_STATUS = 0
BEGIN
    --Select @RecordPk=[Id] From @@vTable
SET @UpdatedBy = SYSTEM_USER
SET @RecordPkName = 'Order_Id'

IF @EventType = 'Insert'
BEGIN
SET @modifiedColumnsList = STUFF((
           SELECT   ',' + Name
          FROM Sys.Columns
          WHERE Object_Id = OBJECT_ID(@TABLE_Name)
           AND SUBSTRING(COLUMNS_UPDATED(), ((Column_Id-1) / 8 + 1), 1)&                                       (POWER(2, ((Column_Id-1) % 8 + 1) -1)) = POWER(2, (Column_Id-1) % 8) 
          FOR XML PATH('')
), 1, 1, ''); 
END
ELSE
BEGIN
SET @modifiedColumnsList = STUFF((
SELECT ',' + Name
FROM Sys.Columns
WHERE Object_Id = OBJECT_ID(@TABLE_Name)
FOR XML PATH('')
), 1, 1, '');
END
--Print (@modifiedColumnsList)

----Second WHILE Loop begins here
WHILE LEN(@modifiedColumnsList) > 0
BEGIN
SET @Pos = CHARINDEX(',', @modifiedColumnsList);
IF @Pos = 0
    BEGIN
         SET @ColumnListItem = @modifiedColumnsList;
    END;
ELSE
    BEGIN
        SET @ColumnListItem = SUBSTRING(@modifiedColumnsList, 1, @Pos-1);
    END;

SET @Column_Name = @ColumnListItem;
SET @ParamDelete = N'@OldValueOut NVARCHAR(100) OUTPUT';
SET @ParamInsert = N'@NewValueOut NVARCHAR(100) OUTPUT';
SET @sqlDeleted = N'SELECT @OldValueOut=' + @Column_Name + ' 
                                          FROM [dbo].[#Deleted] where ' + @RecordPkName + '=' +                                                                  CONVERT(VARCHAR(50), @RecordPk);
SET @sqlInserted = N'SELECT @NewValueOut=' + @Column_Name + ' 
                                         FROM [dbo].[#Inserted] where ' + @RecordPkName + '=' +                                                                 CONVERT(VARCHAR(50), @RecordPk);

EXECUTE sp_executesql @sqlDeleted ,@ParamDelete
,@OldValueOut = @OldValue OUTPUT; 

EXECUTE sp_executesql @sqlInserted,@ParamInsert
,@NewValueOut = @NewValue OUTPUT;

IF @OldValue='' 
Begin 
Set @EventType='Insert' 
    End

IF (LTRIM(RTRIM(@NewValue)) != LTRIM(RTRIM(@OldValue)))
BEGIN
SET @sql = 'INSERT INTO [dbo].[AuditDataChanges]
                                               ([TableName]
                                               ,[ColumnPK]
                                               ,[ColumnName]
                                               ,[OldValue]
                                               ,[NewValue]
                                       ,[ChangeType]
       ,[UpdatedBy] )
                                         VALUES
                                               (' + QUOTEName(@TABLE_Name, '''') + '
                                               ,' + QUOTEName(@RecordPk, '''') + '
                                               ,' + QUOTEName(@Column_Name, '''') + '
                                               ,' + QUOTEName(@OldValue, '''') + '
                                               ,' + QUOTEName(@NewValue, '''') + '
       ,' + QUOTEName(@EventType, '''') + '
       ,' + QUOTENAME(@UpdatedBy, '''') +')';

EXEC (@sql);
END;

SET @Column_Name = '';
SET @NewValue = '';
SET @OldValue = '';

IF @Pos = 0
            BEGIN
        SET @modifiedColumnsList = '';
    END;
ELSE
    BEGIN
         SET @modifiedColumnsList = SUBSTRING(@modifiedColumnsList, 
                         @Pos + 1, LEN(@modifiedColumnsList) - @Pos)
    END; 
END;
----Second WHILE Loop ends here
FETCH NEXT
FROM t_Cursor INTO @RecordPk 
END
----First WHILE Loop ends here
DROP TABLE [dbo].[#Inserted];
DROP TABLE [dbo].[#Deleted];
--Closing and DeAllocating the Cursor
CLOSE t_Cursor;
DEALLOCATE t_Cursor;

END;
GO


----------------------------------------------------------
Now we will test the Trigger for Insert, Delete and Update events on the Sample Table.
INSERT Event :
Insert Into [dbo].[trgr_Sample]
Values ( 127, 'Router', 'Japan','Q1-2017',8457),
( 128, 'Radio', 'Nepal','Q2-2017',7864)

Select * From [dbo].[trgr_Sample]

Once we insert few records to the Sample table, a INSERT trigger will be fired and the Audit log related that operation will be captured in AuditDataChanges tables as follows..
Select * From [dbo].[AuditDataChanges]

DELETE Event :
Delete From [dbo].[trgr_Sample] Where [Order_Id] in (127,128)

Now we will check the Audit details captured in the AuditDataChanges table

Notes:
In case of Insert, the OldValue will be balnk, and in case of Delete, the NewValue will be blank.

UPDATE Event :
Update [dbo].[trgr_Sample]
Set [ProdName]='Laptop' ,[FiscalQtr]='Q1-2016' Where [Order_Id] in (121,124)

Now we will check the Audit details captured in the AuditDataChanges table

Notes:
This is just a one of the way  to Track and Audit the data changes over a Table, using a Cursor and Trigger.
There could be better ways to achieve this Scenario. Please suggest if you have any ideas.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog