How to Create Log Table in Ms SQL Server with the Help of Triggers

Today I am going to show you how to create log for any important table with use of triggers.
 
In my live application of forex there is one master setting . All my application configuartion depends upon one master table.

There were too much of fluctuation in master table that some one changing it's master setting day by day , So I thought to create log for the particular master table.
 
Here we go .
 
EMP --- Original Live Table.
EMPLOG-- Log table of master with log date time .
 
If any modification happens in master table ,it's record saves in log table.

1 .Create emp Table with empid,empname and emplastname columns.
2 .Create emplog table with empid,empname,emplastname and logdate  columns. ----Logdate which store date and time of updation,deletion of data.

Query For triggers

create trigger trigemp on emp for delete,insert,update as  
if exists (select empid from deleted) and  
exists(select empid from deleted) 
begin  
insert into emplog select *,getdate() from deleted end 
if not exists (select empid from inserted )and  
exists(select empid from deleted) 
begin insert into emplog select *,getdate() from deleted en

Image1.png

Done.

Ebook Download
View all
Learn
View all