Track DML Changes Using after Trigger for Update, Delete and Insert rows- Capture Changed data rows using T-sql

Track DML Changes Using after Trigger for Update, Delete and Insert rows

There is out lot many options to track DML changes to data in SQL server like Change data Capture(CDC), Change tracking which are very easy to implement and make use of them but there are scenarios where we cannot use these CDC and change tracking as they were introduced in SQL server 2008. If you want to implement this in before 2008 versions the best way can be using of triggers.

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.

What is the purpose of this trigger?

Track the newly inserted or updated row and save into the audit table
Track the deleted rows and save into the audit table by using date stamp column

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, See below for the snapshot of the result.

Keep in mind that I’m using SQL Server Denali (CTP) Version for creating this trigger but also tested in 2005 and 2008 versions too.

In order to implement this Trigger, I’m creating test database by using the below script

/*Creating a Test Database*/
 Create database TestDB
 Go

Next, Creating test table to implement trigger on

/*Create Test Table to make use for Implementing trigger for DML Changes */

 Create table Test_table(
 id int identity
,Name varchar(50)
,phonenumber varchar(10) )

Next, insert some data into the table to perform testing the trigger using the below script

/*Insert data into Test Table for which we track DML changes*/

 Insert into Test_table(Name,phonenumber) values('Lucky',9191919191)
 Insert into Test_table(Name,phonenumber) values('Priya',0110101101)
 Insert into Test_table(Name,phonenumber) values('Meha',9987979237)
 Insert into Test_table(Name,phonenumber) values('stacy',9178697239)
 Insert into Test_table(Name,phonenumber) values('Nancy',9126827982)

/*select the rows inserted in the above script*/

select * from TestDB.dbo.Test_table

Next, creating an audit table in the TestDB to save the trigger tracked data from inserted, updated and deleted as below make sure that we are creating timestamp column to query later for historical changes to the table as below

/*Create Table to save Audit Data changes */

Create table TestDB.dbo.Test_table_Audit(
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)
 )

In the next final step we will see how to 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].[Tr_Test_Table_Audit]
ON [dbo].[Test_table] 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 Test_table_Audit(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 Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	         SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
        END

-- Capture Update Operation
ELSE

        BEGIN
              INSERT INTO Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	          SELECT GETDATE(),'Updated',ID,Name,phonenumber from inserted

        END
END

Testing

Test Insert Operation – Insert a row into Test_table

/*Testing Insert Operation*/
insert into Test_table(Name,phonenumber) values('Microsoft',9190879979)

Then verify that the row we inserted above is tracked and saved in the audit table.

select * from dbo.Test_table_Audit where Name like ‘Microsoft’

Test Update Operation – Update a row just inserted above using below script

/*Testing Update Operation*/
Update Test_table
Set Name= 'SQLFRNDZ'
Where Name like 'Microsoft'

Then verify that the row we updated is tracked and inserted into audit table

select * from dbo.Test_table_Audit where Name like ‘SQLFRNDZ’

Test Delete operation- Delete a row we just updated above using below script

/*Delete a row from the table test_table*/
DELETE FROM Test_table
WHERE Name like 'SQLFRNDZ'

Then verify that the row we deleted is tracked and inserted into audit table

/*Verify the deleted row*/
Select * from dbo.Test_table_Audit where Operation like 'Deleted'

I would to see any other better type of trigger if you have to track the changes in a similar way please reply at the bottom of this page or send it to

admin@sqlfrndz.com