SQL BLOG: Using Triggers for creating history tables in SQL Server

Case Study:

1)       We  need to track the employee table in terms of new records inserted along with the datetime stamp

2)      In case any updation, we need to track the same with only the column name that is updated. Rest all columns should be null. All tracking will be stored in a separate table called employee_audit.

Solution:

i)                    Creation of Employee Table:

create table employee

(

empid integer,

ename varchar(20),

location varchar(20),

company varchar(20),

doj date

)

insert into employee values (1,'aa','hyd','zzzz','2012-01-01')

insert into employee values (2,'bb','blore','xxxx','2013-01-01')

insert into employee values (3,'cc','delhi','yyyy','2013-02-28')

ii)                   Creation of Employee_Audit table that will contain the audit value:

create table employee_audit

(

empid integer,

ename varchar(20),

location varchar(20),

company varchar(20),

doj date,

audit_action varchar(20),

modified_date datetime

)

iii)                 Creation of Insert after trigger that will take care of newly inserted records:

/* create insert after  trigger and capture information on employee audit table */

create trigger tr_insert_after_employee_audit on employee

for insert

as

declare @empid integer

declare @ename varchar(20)

declare @location varchar(20)

declare @company varchar(20)

declare @doj date

declare @audit_action varchar(20)

select @empid = i.empid from inserted i;

select @ename = i.ename from inserted i;

select @location = i.location from inserted i;

select @company = i.company from inserted i;

select @doj = i.doj from inserted i;

set @audit_action = 'Insert Record'

insert into employee_audit values (@empid,@ename,@location,@company,@doj,@audit_action,getdate())

iv)                 Update trigger for keeping record of updated columns

/* trigger for updation of record in employee table */

create trigger tr_update_after_employee_audit on employee

for update

as

declare @empid integer

declare @ename varchar(20)

declare @location varchar(20)

declare @company varchar(20)

declare @doj date

declare @audit_action varchar(20)

/* variables declaration for deleted records */

declare @d_empid integer

declare @d_ename varchar(20)

declare @d_location varchar(20)

declare @d_company varchar(20)

declare @d_doj date

declare @d_audit_action varchar(20)

select @empid = case when (d.empid = i.empid) then  i.empid end from inserted i inner join deleted d on i.empid = d.empid;

select @ename = case when (d.ename = i.ename) then  i.ename end from inserted i inner join deleted d on i.empid = d.empid;

select @location = case when (d.location = i.location) then null else i.location end from inserted i left outer join deleted d on i.empid = d.empid;

select @company = case when (d.company = i.company) then null else i.company end from inserted i left outer join deleted d on i.empid = d.empid;

select @doj = case when (d.doj = i.doj) then null else i.doj end from inserted i left outer join deleted d on i.empid = d.empid;

set @audit_action = 'Updated Record'

insert into employee_audit values (@empid,@ename,@location,@company,@doj,@audit_action,getdate())

Sample Output:

Empid	Ename	Location	Company	DOJ	Action Status	Modified_Date
1	aa	hyd	zzzz	01-01-2012	Insert Record	11:03.9
2	bb	blore	xxxx	01-01-2013	Insert Record	11:03.9
3	cc	delhi	yyyy	28-02-2013	Insert Record	11:03.9
1	aa	hyd-1	NULL	NULL	Updated Record	11:34.7