Database Trigger Insert Update Delete Together example
In last tutorial you saw what is trigger, how to create it, how to use it on the database separately. In this tutorial you will how to achieve all three DML functionality in single trigger. Table creation script are same as shown in previous tutorial here I will show you details of the trigger . Create trigger script:
create trigger dbo.Employee_Trigger on dbo.Employee for insert,update,delete as declare @Id int declare @FirstName varchar(20) declare @LastName varchar(20) if (select count(*) from OTC.dbo.Employee a, inserted b where a.Id = b.Id) >= 1 --Means its insert because there is not data in the table BEGIN insert into OTC.dbo.Employee_History (Id,FirstName,LastName,Update_Time) values(@Id,@FirstName,@LastName,getdate()) end if (select count(*) from OTC.dbo.Employee a, inserted b where a.Id = b.Id) >= 1 --Means its update because there is data in the table BEGIN update OTC.dbo.Employee_History set FirstName=@FirstName, LastName=@LastName, Update_Time =getDate() where Id=@Id end else --Should be delete event (It's good to keep separate column to indicate insert, upate or delete to indicate by number) BEGIN delete from OTC.dbo.Employee_History where Id=@Id end
Reference: