{[['
']]}
']]}
Track DML Changes
We can track data changes to a SQL server table like update, delete and insert operations individually by creating AFTER Triggers for update , Trigger for Insert and Trigger for Delete. In our scenario I’m going to explain creating a trigger to track the data changes and save into audit table for each individual operation happens on rows in a table.By implementing this trigger we can have the historical changes to the rows in a table and also we can query the table by using data column to find out the productivity of the table and how much operational is the table?
Note: This trigger captures only the operation occurred and on which row is effected.
Implementation
1-Creating a test database/*scriptCreating a Test Database*/2-Creating test table to implement trigger on
Create database Test_DB
Go
/* Script Test Table*/3-Insert some data into the table for testing
Create table Contact_Info( id int identity ,Name varchar(50) ,phonenumber varchar(10) )
/*Insert data into Test Table for which we track DML changes*/
Insert into Contact_Info(Name,phonenumber) values('Lucky',123465789)
Insert into Contact_Info(Name,phonenumber) values('Sam',0110101101)
Insert into Contact_Info(Name,phonenumber) values('Tom',0120120120)
Insert into Contact_Info(Name,phonenumber) values('Peter',013013013)
Insert into Contact_Info(Name,phonenumber) values('Nancy',055505550)
/*select the rows inserted in the above script*/4-Creating an audit table in the TestDB to save the tracked data from inserted, updated and deleted as below.
select * from Contact_Info
/*Create Table to save Audit Data changes */
Create table Test_DB.dbo.Audit_Data
(
Effective_date datetime, -- To get the date and time of the changed row
Operation char(10) -- To get the operation occured like Insert or Update or Delete
,Id INT
,Name Varchar(50)
,phonenumber varchar(10)
)
5-Create the After Trigger for all DML changes and save into the audit table using the below script which is well explained with comment lines.
Create TRIGGER [dbo].[DML_Changes]
ON [dbo].[Contact_Info] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
-- SET NOCOUNT ON added to prevent extra result sets from query execution
SET NOCOUNT ON;
-- Determine if this is an INSERT,UPDATE, or DELETE Action
DECLARE @operation as Varchar(10)
DECLARE @Count as int
SET @operation = 'Inserted' -- Setting operation to 'Inserted'
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
BEGIN
SET @operation = 'Deleted' -- Set Operation to 'Deleted'
SELECT @Count = COUNT(*) FROM INSERTED
IF @Count > 0
SET @operation = 'Updated' -- Set Operation to 'Updated'
END
-- Capturing Delete Operation
if @operation = 'Deleted'
BEGIN
Insert into Audit_Data(Effective_date,Operation,Id,Name,phonenumber)
SELECT GETDATE(),'Deleted',ID,Name,phonenumber from deleted
END
ELSE
BEGIN
-- trigger treats insert and update as same, so we can make it clear here
SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
--Capturing Insert Operation
if @operation = 'Inserted'
BEGIN
Insert into Audit_Data(Effective_date,Operation,Id,Name,phonenumber)
SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
END
-- Capture Update Operation
ELSE
BEGIN
INSERT INTO Audit_Data(Effective_date,Operation,Id,Name,phonenumber)
SELECT GETDATE(),'Updated',ID,Name,phonenumber from inserted
END
END
Testing
Test Insert Operation
/*Testing Insert Operation*/
insert into Contact_Info(Name,phonenumber) values('John Do',123456789)
Then verify that the row inserted above is tracked and saved in the audit table.
select * from dbo.Audit_Data where Name like 'John Do'
Test Update Operation
/*Testing Update Operation*/
Update Contact_Info Set Name= 'Iron Man' Where Name like 'John Do'
Then verify that the row we updated is tracked and inserted into audit table
select * from dbo.Audit_Data where Name like 'Iron Man'
Test Delete operation
/*Delete a row from the table test_table*/Then verify that the row we deleted is tracked and inserted into audit table
DELETE FROM Contact_Info WHERE Name like 'Iron Man'
/*Verify the deleted row*/
Select * from dbo.Audit_Data where Operation like 'Deleted'
Post a Comment