Database Trigger Insert Update Delete Together example

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:

• Oracle Trigger
• MSSQL Trigger