Database Trigger Use Complete Example

Database Trigger Use Complete Example

Trigger is a database function which is automatically gets executed in response to certain events like insert, update or delete on a particular table or view. Mostly trigger used for maintaining integrity of the information on database and it can not be explicitly invoked to achieve the output. Let’s say for example we have an employee table where data’s are normalized in many other table Salaries, Performance so whenever new record inserted for new employee in employee table all data should also get populated in Salaries and Performance table.

Types of Trigger: 

• AFTER Triggers
• BEFORE Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events
• Row Triggers and Statement Triggers

AFTER Triggers:  This triggers execute whenever any DML statement fired against the table like insert, update or delete. These trigger only supported for table not the views. Below are its sub types:

• AFTER INSERT Trigger
• AFTER UPDATE Trigger
• AFTER DELETE Trigger

To understand these triggers by example we will have to create the table and insert some sample data’s: 

create table OTC.dbo.Employee (
	Id int identity not null,
	FirstName varchar (20) null,
	LastName varchar (20) null,
	Contact int null 
)

INSERT INTO OTC.dbo.Employee(FirstName,LastName,Contact) VALUES ('Java' ,'Honk' ,123456789)
go
INSERT INTO OTC.dbo.Employee(FirstName,LastName,Contact) VALUES ('Java2' ,'Honk' ,123456789)
go
INSERT INTO OTC.dbo.Employee(FirstName,LastName,Contact) VALUES ('Java3' ,'Honk' ,123456789)
go
INSERT INTO OTC.dbo.Employee(FirstName,LastName,Contact) VALUES ('Java4' ,'Honk' ,123456789)

• Check if data is inserted or not:

• Now create Employee helper table name Employee_History so whenever new employee record inserted into Employee table we could fire trigger to insert record in this table to keep history of record for audit purpose.

create table OTC.dbo.Employee_History (
 Id int not null,
 FirstName varchar (20) null,
 LastName varchar (20) null,
 Update_Time date not null 
)

• AFTER INSERT Trigger: Now we have test tables are in place, lets create after insert trigger which will insert the record in the table whenever data is inserted into Employee table:

create trigger dbo.Employee_Insert_Trigger on dbo.Employee
for insert
as
declare @Id int
declare @FirstName varchar(20)
declare @LastName varchar(20)

select @Id=i.Id from inserted i	
select @FirstName=i.FirstName from inserted i	
select @LastName=i.LastName from inserted i	

insert into OTC.dbo.Employee_History (Id,FirstName,LastName,Update_Time) 
values(@Id,@FirstName,@LastName,getdate())

PRINT 'AFTER INSERT trigger executed...'

GO

• As you see above we gave trigger name then “on dbo.Employee” means it will be fired against Employee table “for insert”. Then we have declared variable and mapped it with inserted data’s, once all data’s populated then insert statement will insert the data into Employee_History table. Now to test let’s insert one rows in Employee table:

INSERT INTO OTC.dbo.Employee(FirstName,LastName,Contact) VALUES ('Java6' ,'Honk' ,123456789)

• Now check Employee_History table if trigger fired and data inserted or not and as you see below trigger is fired and data got inserted in the table:

• AFTER UPDATE Trigger: This trigger works same way as insert trigger, only difference is this trigger will be fired only if any update statement executed on Employee table. Let’s create this trigger:

create trigger dbo.Employee_Update_Trigger on dbo.Employee
for update
as
declare @Id int
declare @FirstName varchar(20)
declare @LastName varchar(20)

select @Id=u.Id from update u	
select @FirstName=u.FirstName from update u	
select @LastName=u.LastName from update u	

update OTC.dbo.Employee_History set FirstName=@FirstName,
LastName=@LastName, Update_Time =getDate()
where Id=@Id

PRINT 'AFTER Update trigger executed...'

GO

• Execute update statement on Employee and see if data’s updated on Employee_History table or not:

UPDATE OTC.dbo.Employee SET FirstName = 'JavaUpdated', LastName = 'HonkUpdated'
,Contact = 123456 where Id=6

• Check the table if data updated or not. As you see trigger got fired and data’s is updated:

• AFTER DELETE Trigger: It also work on same way as other two works let’s create delete trigger on Employee table:

create trigger dbo.Employee_Delete_Trigger on dbo.Employee
for delete
as
declare @Id int

select @Id=d.Id from deleted d

delete from OTC.dbo.Employee_History where Id=@Id

PRINT 'AFTER Delete trigger executed...'
go

• Execute delete statement on table Employee and see if record is deleted or not:

DELETE FROM OTC.dbo.Employee WHERE Id = 6

• Check if data in the table where Id=6 deleted or not. It’s got deleted:

Reference:

• Oracle Trigger
• MSSQL Trigger