How to track data changes on a table or view when an insert happens or delete happens.

Problem: How to track data changes on a table or view when an insert happens or delete happens.

Solution:

CREATE TRIGGER

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected. For more information,

There are two types of Triggers

1.     After Triggers – Fires after the execution of constraints processing on a table.

2.     Instead of Triggers – Fires before the execution of constraints processing on a table.

Example:

In this article I am going to explain one example one for each type of trigger.

First Let’s create after trigger to track the insert’s update’s and deletes on a table.

To store all these insert, update and delete records we need a table, so I am creating a table and the type of action, date of operation and machine used to do the action.

CREATE TABLE [dbo].[Audit_Employees](

                [EmployeeID] [int]  NULL,

                [Name] [varchar](32) NULL,

                [PhoneNumber] [int] NULL,

                [EmployeeType] [varchar](32) NULL,

                [Company_Id] [int] NULL,

                [Action] varchar(100) NULL,

                [AuditDate] DateTime NULL,

                [MachineName] Varchar(100) NULL,

) ON [PRIMARY]

GO

1.       Insert Trigger to fire when a row is inserted into Employees table.

CREATE TRIGGER dbo.AfterInsertTrigger

   ON  dbo.Employees

   AFTER INSERT

AS

BEGIN

                SET NOCOUNT ON;

                declare @empid int,

                 @name varchar(100),

                 @phonenumber int,

                 @EmployeeType Varchar(32),

                 @companyid int

                declare @audit_action varchar(100);

                select @empid=i.[EmployeeID] from inserted i; 

                select @name=i.[Name] from inserted i;              

                select @phonenumber=i.[PhoneNumber] from inserted i;

                select @companyid=i.[Company_Id] from inserted i;

                select @EmployeeType=i.[EmployeeType] from inserted i;

               

                               

                set @audit_action='Inserted Record -- After Insert Trigger.';

                insert into dbo.[Audit_Employees]

           ([EmployeeID],[Name],[PhoneNumber],[EmployeeType],[Company_Id],[Action],[AuditDate],[MachineName])

values(@empid,@name,@phonenumber,@EmployeeType,@companyid,@audit_action,GETUTCDATE(),CAST(Serverproperty('MachineName') as varchar(100)));

END

GO

2.       Update Trigger to fire when a row is updated in Employees table.

CREATE TRIGGER [dbo].[AfterUpdateTrigger]

   ON  [dbo].[Employees]

   AFTER Update

AS

BEGIN

                SET NOCOUNT ON;

                declare @empid int,

                 @name varchar(100),

                 @phonenumber int,

                 @EmployeeType Varchar(32),

                 @companyid int

                declare @audit_action varchar(100);

                select @empid=i.[EmployeeID] from inserted i; 

                select @name=i.[Name] from inserted i;              

                select @phonenumber=i.[PhoneNumber] from inserted i;

                select @companyid=i.[Company_Id] from inserted i;

                select @EmployeeType=i.[EmployeeType] from inserted i;

                                               

                               

                set @audit_action='Updated Record -- After Update Trigger.';

                insert into dbo.[Audit_Employees]

           ([EmployeeID],[Name],[PhoneNumber],[EmployeeType],[Company_Id],[Action],[AuditDate],[MachineName])

values(@empid,@name,@phonenumber,@EmployeeType,@companyid,@audit_action,GETUTCDATE(),CAST(Serverproperty('MachineName') as varchar(100)));

END

3.       Delete Trigger to fire when a row is deleted from Employees table.

CREATE TRIGGER [dbo].[AfterDeleteTrigger]

   ON  [dbo].[Employees]

   AFTER Delete

AS

BEGIN

                SET NOCOUNT ON;

                declare @empid int,

                 @name varchar(100),

                 @phonenumber int,

                 @EmployeeType Varchar(32),

                 @companyid int

                declare @audit_action varchar(100);

                select @empid=d.[EmployeeID] from deleted d;

                select @name=d.[Name] from deleted d;             

                select @phonenumber=d.[PhoneNumber] from deleted d;

                select @companyid=d.[Company_Id] from deleted d;

                select @EmployeeType=d.[EmployeeType] from deleted d;

               

                               

                set @audit_action='Deleted Record -- After Delete Trigger.';

                insert into dbo.[Audit_Employees]

           ([EmployeeID],[Name],[PhoneNumber],[EmployeeType],[Company_Id],[Action],[AuditDate],[MachineName])

values(@empid,@name,@phonenumber,@EmployeeType,@companyid,@audit_action,GETUTCDATE(),CAST(Serverproperty('MachineName') as varchar(100)));

END

GO

Now it’s time to test all the work we have done on the triggers. Let’s insert few rows in employees table.

                INSERT INTO dbo.[Employees]

               ([Name],[PhoneNumber],[EmployeeType],[Company_Id])

                values('Debra',123456789,'tester',1);

                GO

              

Update dbo.Employees

                                set phonenumber=123123123

                                where name ='Debra'

                GO        

Delete From dbo.Employees

                                where name ='Debra'

GO