Triggers in SQL

Triggers

Triggers are stored programs that are automatically executed or fired when some events occur. It is a database object that is bound to a table and is executed automatically. We cannot explicitly call any trigger. Triggers provide data integrity and used to access and check data before and after modification using DDL or DML query.

Triggers are used in the following events:

1. DML (Data manipulation Language)(Insert ,Update ,Delete).
   
2. DDL (Data Definition Language)(Create , Alter, Drop).
   
3. Database Operation such as LOGON, LOGOFF, SERVERERROR, SHUTDOWN, STARTUP.

Now we will understand what is triggers and how it works?

First create a table as in the following:

Create Table Student
(
IId int Not Null primary key,
Name Nvarchar(MAX) Not NUll,
Age Int Not Null,
Class int not Null
)

Now insert some values into the table as in the following:

Insert Into Student
Select 1,'A',12,10 Union All
Select 2,'B',16,11 Union All
Select 3,'C',15,9 Union All
Select 4,'D',13,12 Union All
Select 5,'E',14,11 Union All
Select 6,'F',17,8 Union All
Select 7,'G',12,7 Union All
Select 8,'H',17,12

Now the table will look like the following:



The following is the syntax of triggers: 

CREATE [OR REPLACE ] TRIGGER Trigger_Name


ON Table_Name

 {BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
  AS

Begin

Declaration Part
{
}
Select Part
{
// Initialize Variables
}
 
Executable-Code
EXCEPTION
Exception-Handling-Code
END;

Types of DML Triggers

Triggers are of the following three types:

1. After Triggers
2. Instead Of Triggers

After Triggers

After triggers are invoked after DML (insert, update and delete) operations. They are not supported for views. An after trigger is also divided into the following 3 parts:

• After Insert
• After Delete
• After Update

After Insert

Example

An After Insert Trigger is called after any data is inserted into the table.

Create Trigger My_Trigger On Student

AFTER Insert
AS
Declare @IId int ;
Declare @Name Nvarchar(50) ;
Declare @Age Int;
Declare @Class int;

Select @IId= Tab.IId From inserted Tab;
Select @Name= Tab.Name From inserted Tab;
Select @Age= Tab.Age From inserted Tab;
Select @Class= Tab.Class From inserted Tab;

Set @IId=@IId+1;
Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
Print 'Inserted Successfully'

Now we will insert some values into the Student Table.

Insert Into Student(IId,Name,Age,Class) values(10,'Pankaj',21,12)

Output

(1 row(s) affected)
Inserted Successfully

(1 row(s) affected)

In the preceding example we will create a trigger for an insert query so when any data is inserted into the student table, then after the insert query this trigger will be invoked and some desired operation will be done. In the insert trigger we use the “INSERTED” magic table.

After Delete

After Delete Triggers are called after any data is deleted from the table.

Create Trigger After_Delete On Student
AFTER Delete
AS
Declare @IId int ;
Declare @Name Nvarchar(50) ;
Declare @Age Int;
Declare @Class int;

Select @IId= Tab.IId From Deleted Tab;
Select @Name= Tab.Name From Deleted Tab;
Select @Age= Tab.Age From Deleted Tab;
Select @Class= Tab.Class From Deleted Tab;
if @IId>10
begin
Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
Print 'Data Deleted Successfully'
End
else
Begin
Print 'Data not Deleted Successfully'
End

Now we will delete some data from the table:

Delete from Student Where IId=99

Output

(1 row(s) affected)
Data Deleted Successfully

(1 row(s) affected)

Now we will take another example:

Delete from Student Where IId=9

Output

Data not Deleted Successfully

(0 row(s) affected)

In the After Delete trigger we used the “Deleted” magic table. This table contains all the data that was deleted by the query.

After Update

An After Update Trigger is called after any type of update is done on the table.

Create Trigger After_Update On Student
AFTER Update
AS
Declare @IId int ;
Declare @Name Nvarchar(50) ;
Declare @Age Int;
Declare @Class int;

Declare @IId1 int ;
Declare @Name1 Nvarchar(50) ;
Declare @Age1 Int;
Declare @Class1 int;

Select @IId= Tab.IId From Deleted Tab;
Select @Name= Tab.Name From Deleted Tab;
Select @Age= Tab.Age From Deleted Tab;
Select @Class= Tab.Class From Deleted Tab;

Select @IId1= Tab.IId From inserted Tab;
Select @Name1= Tab.Name From inserted Tab;
Select @Age1= Tab.Age From inserted Tab;
Select @Class1= Tab.Class From inserted Tab;
Set @IId1=@IId1+1;
if @IId>10
begin
Delete From Student Where IId=@iid
Insert Into Student(IId,Name , Age ,Class) Values (@IId1,@Name1,@Age1,@Class1)
Print 'Data Updated Successfully'
End
else
Begin
Print 'Data not Updated Successfully'
End

We will now to update some data.

Update Student Set Name='Rahul' , Age=30 where IId=101

Output

(1 row(s) affected)

(1 row(s) affected)
Data Updated Successfully

(1 row(s) affected)

The preceding example shows that in an After Update trigger we can use both the “Inserted” and “Deleted” magic tables. The Update query also does two steps, the first is to delete data from the table. In that case the “Deleted” magic table contains the deleted data and the second step is the insertion of the data into the table. In that step the “Inserted” magic table contains the inserted data.

So we can use both magic tables in an “After Update” trigger.

Instead of Triggers

These can be used as an interceptor for anything that anyone tries to do on our table or view. This type of trigger fires before SQL Server starts the execution of the action that fired it. We can have an INSTEAD OF insert/update/delete trigger on a table that was successfully executed but does not include the actual insert/update/delete to the table. Assume we have an Instead Of Trigger for a Delete operation on a table. When any data is deleted from the table then this trigger will execute first but after the execution of this trigger the data will not delete from the table unless we issue another delete instruction from within the trigger.

Types of Instead Of Triggers

1. Instead Of Insert
2. Instead Of Update
3. Instead Of Delete

Example

Table before execution of an “Instead Of Delete”.



Now we create an “Instead Of Trigger“.

Create Trigger Insted_Delete On Student
Instead Of Delete
as
begin
Declare @IId int ;
Declare @Name Nvarchar(50) ;
Declare @Age Int;
Declare @Class int;

Select @IId= Tab.IId From Deleted Tab;
Select @Name= Tab.Name From Deleted Tab;
Select @Age= Tab.Age From Deleted Tab;
Select @Class= Tab.Class From Deleted Tab;
Set @IId=@IId+1;
Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
Print 'Data Updated Successfully'
End

Then we try to delete some data from the data and examine the result.



We can see that we execute the query for the delete of the student record from table that have an IId equal to 8 but in the result record with IId =3 it is not deleted because the data will not delete from the table unless we issue another delete instruction from within the trigger.

Now we create a another trigger that contains a Delete Command.

Create Trigger Insted_Delete1 On Student
Instead Of Delete
as
begin
Declare @IId int ;
Declare @Name Nvarchar(50) ;
Declare @Age Int;
Declare @Class int;

Select @IId= Tab.IId From Deleted Tab;
Select @Name= Tab.Name From Deleted Tab;
Select @Age= Tab.Age From Deleted Tab;
Select @Class= Tab.Class From Deleted Tab;
Delete From Student Where IId=@IId
Set @IId=@IId+1;
Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
Print 'Data Updated Successfully'
End

Now we execute a delete operation on the table.



Cyclic Cascading in a Trigger

This is an undesirable situation where more than one trigger enters into an infinite loop. When creating a trigger we should ensure that such a situation cannot happen. This situation can occur when two triggers generate a call for each other.

Let us create two triggers as in the following:

• The first trigger is for an insert as in the following:
  

  Create Trigger [dbo].[Insert_] On [dbo].[Student]
  For Insert
  as
  begin
  Declare @IId int ;
  Declare @Name Nvarchar(50) ;
  Declare @Age Int;
  Declare @Class int;
  
  Select @IId= Tab.IId From inserted Tab;
  Select @Name= Tab.Name From inserted Tab;
  Select @Age= Tab.Age From inserted Tab;
  Select @Class= Tab.Class From inserted Tab;
  
  
  Update Student set Name=@Name ,Age=@Age where IId=@IId
  Print 'Data Inserted Successfully'
  End

• And the second trigger is for an update as in the following:
  

  Create Trigger [dbo].[Update_] On [dbo].[Student]
  For Update
  as
  begin
  Declare @IId int ;
  Declare @Name Nvarchar(50) ;
  Declare @Age Int;
  Declare @Class int;
  
  Select @IId= Tab.IId From inserted Tab;
  Select @Name= Tab.Name From inserted Tab;
  Select @Age= Tab.Age From inserted Tab;
  Select @Class= Tab.Class From inserted Tab;
  Set @IId=@IId+1;
  Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)
  Print 'Data Updated Successfully'
  End

Now we insert some data into the table and check what will be happen.

Insert into Student values(9000,'A',32,5000)

Output

Msg 217, Level 16, State 1, Procedure Update_, Line 15
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

The first trigger (Insert_) contains an update statement. When this statement executes it create a call for Update_ trigger that contains an insert statement so when this statement executes it will create a call for the Insert_ trigger. So an infinite loop will occur.

So during the creation of a trigger we must be sure to remove such a condition because this cyclic situation continues and will enter into an infinite loop that will crash the database.