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]
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
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AuditDataChanges] ADD DEFAULT (getdate()) FOR [ChangeDate]
GO
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;
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]
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 :
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
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.
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.