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
|
