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 |
