Post
Ask Question

Trigger In SQL Server

In this article we will learn about SQL triggers. I will demonstrate SQL trigger with examples and explain why we should use triggers. 

Table content

  • What is a trigger?
  • Types of SQL Trigger
  • Syntax and Example

What is a trigger?

Triggers are special kind of SQL code that is automatically executed in response to certain events on a particular table.

Triggers are used to maintain the intigrity of a table's data.

In other words, triggers are a special kind of store procedure that is automatically executed when any event occurs, such as insert, update or deletion on particular table.

Types of Triggers

  1. DML Trigger
  2. DDL Trigger
  3. Logon Trigger

DML Triggers (Data manipulation language triggers) are fired automatically in response to DML events like Insert, Update, Delete.

DML triggers are again classified in two types: After Trigger and Instead trigger.

  1. After Trigger/For trigger
    After any event is triggered, like Insert, Update or Delete, the trigger will fire when the respective statement execution is complete. Both after trigger and for trigger are the same.
     
  2. Instead of trigger
    Instead of the actual triggering action, like Insert, Update and Delete, this type deletes statements. In other words, the trigger fires before the start the execution of the respective action that fired it.

Lets start with three tables (Employee, Department, Gender, EmployeeAudit) and insert data into all tables. This will help us to understand triggers with examples.

Create Table Employee
(
Id int identity(1,1) not null,
Name varchar(100),
Salary decimal(10,2),
Gender int,
DepartmentId int
)

Create Table Gender
(
Id int identity(1,1) not null,
Name varchar(50)
)

Create Table Department
(
Id int identity(1,1) not null,
Name varchar(50),
)

Let's insert records into all three tables:

------------Insert data in Department --------------
insert into Department(Name) values('IT')
insert into Department(Name) values('HR')
insert into Department(Name) values('Sales')
insert into Department(Name) values('Customer Support')
insert into Department(Name) values('HouseKeeping')

------------Insert data in Gender --------------
insert into Gender(Name) values('Male')
insert into Gender(Name) values('Female')


------------Insert data in Employee-----------

insert into Employee(Name,Salary,Gender,DepartmentId) values('Jignesh',5000,1,1)
insert into Employee(Name,Salary,Gender,DepartmentId) values('Dharmi',6000,2,2)
insert into Employee(Name,Salary,Gender,DepartmentId) values('Vinay',7000,1,1)
insert into Employee(Name,Salary,Gender,DepartmentId) values('Ram',5000,2,3)

select * from Department
select * from Gender
select * from Employee

Create table "EmployeeAudit"  which will hold Employee audit related data.

Create Table EmployeeAudit
(
Id int Identity(1,1) not null,
AuditData varchar(500)
)

Now let's check the trigger syntax and show an example in the next code snippet.

CREATE TRIGGER Trigger_Name
ON { Table name or view name }   
[ WITH <Options> ]  
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }

Lets create the trigger Insert for as below:

CREATE TRIGGER tr_Employee_ForInsert
On Employee
For INSERT
AS
BEGIN
	Declare @Id int
	Declare @Name varchar(100)
	select @Id =Id, @Name =Name from inserted

	insert into EmployeeAudit(AuditData) values ('Empoyee Added with Id : ' + cast(@Id as varchar(10)) +' Name :' +@Name +'') 
 
END

I can view my created trigger, "Go to employee table," and expand the trigger folder. You will able to see a trigger with name "tr_Employee_ForInsert".

Let's check the EmployeeAudit table after trigger creation. There is no data in the EmployeeAudit table, as per the below screen.

Now let's insert new records and check our trigger. Our trigger should fire when any insert statement is executed on the Employee table.

insert into Employee(Name,Salary,Gender,DepartmentId) values ('Mike',8000,1,1)

After executing, insert script two messages will result in a grid as per the below snippet. One is for inserted data in the employee table, and the other is triggered to insert in the EmployeeAudit table.

Now we can check our trigger. It should have insert one record in the EmployeeAudit table as well.

As per the above snippet, I have inserted a new employee with the name Mike. We are able to see the record inserted in both tables. So, we checked that the insert for trigger has created a record in the EmployeeAudit table.

Now I will create an Update For trigger:

CREATE TRIGGER tr_employee_ForUpdate
on Employee
For Update
as
Begin
 select * from inserted
 select * from deleted
end

Here I created an update for the trigger on the employee table with two select statements. These show how the trigger handles data using two magic tables: Inserted and Deleted. 

Inserted Table

The inserted table will hold the new data that you have inserted into that table.

Deleted Table

Deleted tabel will hold the data that you have deleted.

Let's check with an example how data will store in these two tables. I will update one record and check how data will store in these tables.

Now let's change the trigger to log history for employeeAudit table to hold historical data:

ALTER TRIGGER tr_employee_ForUpdate
on Employee
For Update
as
Begin

 insert into employeeAudit (AuditData)
 select 'Empoyee name change from ''' + d.Name + ' to ' + i.Name + ' and  Salary change from '+ cast(d.Salary as varchar(10)) +' to '+  cast(i.Salary as varchar(10)) +' for employee Id : ' + cast(i.Id as varchar(10)) +''
 from inserted i
 inner join deleted d on d.Id = i.Id
end

Now I will update one record and see how data will store in EmployeeAudit:

Instead Of Insert Trigger

This trigger was executed instead of firing a statement (insert, update, delete). Triggers are useful when maintaining database referential integrity for tables.

Create View VW_EmployeeDetails
As
select emp.Id, emp.Name as EmployeeName, emp.Salary, g.Name as gender, dept.Name as Department from Employee emp With (NoLock)
inner join Department dept with (NoLock)
on dept.Id = emp.DepartmentId
inner join Gender g With (NoLock) 
on emp.Gender = g.Id

CREATE Trigger tr_VW_EmployeeDetails_InsteadOfInster
On VW_EmployeeDetails
Instead of Insert
As
Begin
	Declare @DeptId int
	select @DeptId from Department d
	inner join inserted i on i.Department = d.Name

	Declare @GenderId int
	select @GenderId from Gender d
	inner join inserted i on i.gender = d.Name

	if(@DeptId is null)
	Begin 
		Raiserror('Invalid Department name, Please enter correct department and try again',16,1)
		return
	End 

    if(@GenderId is null)
	Begin 
		Raiserror('Invalid Gender, Please enter correct Gender and try again',16,1)
		return
	End
	insert into Employee (Name, Salary,Gender,DepartmentId)
	select EmployeeName,Salary, @GenderId, @DeptId from inserted
End

The instead of Insert trigger is used to update the view in the correct way. To insert in views that are based on multiple tables, as per the above example, you will be able to see that there are three tables used to create views (Employee, Gender and Department). When you want to insert a new record to this view, it will affect the other two tables as well. So, SQL will throw an error when inserting the record in the respective table.The instead of insert trigger will help us to maintain referential integrity between tables.

Let's check by inserting some invalid records with an incorrect Department or Gender that does not exist in the master table. If incorrect values are provided, then the "Instead of Insert" trigger should throw an error. Now we'll check this with an example using incorrect data.

Department table with the below data:

View has the below data from the underlined tables:

Try to insert information into "VW_EmployeeDetails" with an invaild department.

When you try to insert into a department that does not exist in our department table, then the "Instead of Insert" trigger will throw an error, saying, "Invalid Department name, Please enter correct department and try again."

In the above scenario, the statement will not insert a record due to the instead of insert trigger fire and will validate whether the department is valid or not to maintain referential intigrity.

Now we will try to insert a new record into the view "VW_EmployeeDetails", providing both the correct department and gender. 

insert into VW_EmployeeDetails values(7,'Priya',10000,'Female','Customer Support')
select * from VW_EmployeeDetails

In the above scenario, we inserted a record with valid department and valid genders. The record was inserted successfully into the respective table.

Like hte insert statement, we can create an Instead of trigger for updating and deletion, as well. The syntax to create update and delete of the instead of trigger is as below:

CREATE TRIGGER TR_INSTEADOF_Update_VW_EmployeeDetails 
ON VW_EmployeeDetails
INSTEAD OF UPDATE AS
BEGIN
 -- Logic for instead of Update/delete trigger.
END

I will explain DDL triggers and Logon triggers in an upcoming article. 

In this article I demonstrated DML triggers, For trigger and Instead of trigger. I have explained how two special tables (magic tables), Inserted and deleted tables, work in the back end.

Good luck!


Similar Articles