Triggers in SQL

Introduction

 
In the previous chapter, we learned how to work with GROUP BY, WHERE, and HAVING clauses in SQL and how to use them with various options.
 
In this chapter, we will learn about Trigger in SQL, Types of Triggers, and to use Triggers in SQL with various conditions.
 

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:
  1. Create Table Student  
  2. (  
  3. IId int Not Null primary key,  
  4. Name Nvarchar(MAX) Not NUll,  
  5. Age Int Not Null,  
  6. Class int not Null  
  7. )  
Now insert some values into the table as in the following:
  1. Insert Into Student  
  2. Select 1,'A',12,10 Union All  
  3. Select 2,'B',16,11 Union All  
  4. Select 3,'C',15,9 Union All  
  5. Select 4,'D',13,12 Union All  
  6. Select 5,'E',14,11 Union All  
  7. Select 6,'F',17,8 Union All  
  8. Select 7,'G',12,7 Union All  
  9. Select 8,'H',17,12  
Now the table will look like the following:
 
table
 
The following is the syntax of triggers,
  1. CREATE [OR REPLACE ] TRIGGER Trigger_Name   
  2. ON Table_Name  
  3.  {BEFORE | AFTER | INSTEAD OF }   
  4. {INSERT [OR] | UPDATE [OR] | DELETE}   
  5.   AS  
  6. Begin  
  7. Declaration Part  
  8. {  
  9. }  
  10. Select Part  
  11. {  
  12. // Initialize Variables   
  13. }  
  14.     
  15. Executable-Code  
  16. EXCEPTION  
  17. Exception-Handling-Code  
  18. 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 by views. An after the 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.
  1. Create Trigger My_Trigger On Student 
     
  2.   
  3. AFTER Insert  
  4. AS  
  5. Declare @IId int ;  
  6. Declare @Name Nvarchar(50) ;  
  7. Declare @Age Int;  
  8. Declare @Class int;  
  9.   
  10. Select @IId= Tab.IId From inserted Tab;  
  11. Select @Name= Tab.Name From inserted Tab;  
  12. Select @Age= Tab.Age From inserted Tab;  
  13. Select @Class= Tab.Class From inserted Tab;  
  14.   
  15. Set @IId=@IId+1;  
  16. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)  
  17. 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.
  1. Create Trigger After_Delete On Student  
  2. AFTER Delete  
  3. AS  
  4. Declare @IId int ;  
  5. Declare @Name Nvarchar(50) ;  
  6. Declare @Age Int;  
  7. Declare @Class int;  
  8.   
  9. Select @IId= Tab.IId From Deleted Tab;  
  10. Select @Name= Tab.Name From Deleted Tab;  
  11. Select @Age= Tab.Age From Deleted Tab;  
  12. Select @Class= Tab.Class From Deleted Tab;  
  13. if @IId>10  
  14. begin  
  15. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)  
  16. Print 'Data Deleted Successfully'  
  17. End  
  18. else   
  19. Begin  
  20. Print 'Data not Deleted Successfully'  
  21. 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.
  1. Create Trigger After_Update On Student  
  2. AFTER Update  
  3. AS  
  4. Declare @IId int ;  
  5. Declare @Name Nvarchar(50) ;  
  6. Declare @Age Int;  
  7. Declare @Class int;  
  8.   
  9. Declare @IId1 int ;  
  10. Declare @Name1 Nvarchar(50) ;  
  11. Declare @Age1 Int;  
  12. Declare @Class1 int;  
  13.   
  14. Select @IId= Tab.IId From Deleted Tab;  
  15. Select @Name= Tab.Name From Deleted Tab;  
  16. Select @Age= Tab.Age From Deleted Tab;  
  17. Select @Class= Tab.Class From Deleted Tab;  
  18.   
  19. Select @IId1= Tab.IId From inserted Tab;  
  20. Select @Name1= Tab.Name From inserted Tab;  
  21. Select @Age1= Tab.Age From inserted Tab;  
  22. Select @Class1= Tab.Class From inserted Tab;  
  23. Set @IId1=@IId1+1;  
  24. if @IId>10  
  25. begin  
  26. Delete From Student Where IId=@iid  
  27. Insert Into Student(IId,Name , Age ,Class) Values (@IId1,@Name1,@Age1,@Class1)  
  28. Print 'Data Updated Successfully'  
  29. End  
  30. else   
  31. Begin  
  32. Print 'Data not Updated Successfully'  
  33. 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
 
The table before the execution of an “Instead Of Delete”.
 
Instead Of Delete
 
Now we create an “Instead Of Trigger“.
  1. Create Trigger Insted_Delete On Student  
  2. Instead Of Delete  
  3. as  
  4. begin  
  5. Declare @IId int ;  
  6. Declare @Name Nvarchar(50) ;  
  7. Declare @Age Int;  
  8. Declare @Class int;  
  9.   
  10. Select @IId= Tab.IId From Deleted Tab;  
  11. Select @Name= Tab.Name From Deleted Tab;  
  12. Select @Age= Tab.Age From Deleted Tab;  
  13. Select @Class= Tab.Class From Deleted Tab;  
  14. Set @IId=@IId+1;  
  15. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)  
  16. Print 'Data Updated Successfully'  
  17. End  
Then we try to delete some data from the data and examine the result.
 
examine the result
 
We can see that we execute the query for the delete of the student record from the 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 another trigger that contains a Delete Command.
  1. Create Trigger Insted_Delete1 On Student  
  2. Instead Of Delete  
  3. as  
  4. begin  
  5. Declare @IId int ;  
  6. Declare @Name Nvarchar(50) ;  
  7. Declare @Age Int;  
  8. Declare @Class int;  
  9.   
  10. Select @IId= Tab.IId From Deleted Tab;  
  11. Select @Name= Tab.Name From Deleted Tab;  
  12. Select @Age= Tab.Age From Deleted Tab;  
  13. Select @Class= Tab.Class From Deleted Tab;  
  14. Delete From Student Where IId=@IId  
  15. Set @IId=@IId+1;  
  16. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)  
  17. Print 'Data Updated Successfully'  
  18. End  
Now we execute a delete operation on the table.
 
result
 

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:
    1. Create Trigger [dbo].[Insert_] On [dbo].[Student]  
    2. For Insert  
    3. as  
    4. begin  
    5. Declare @IId int ;  
    6. Declare @Name Nvarchar(50) ;  
    7. Declare @Age Int;  
    8. Declare @Class int;  
    9.   
    10. Select @IId= Tab.IId From inserted Tab;  
    11. Select @Name= Tab.Name From inserted Tab;  
    12. Select @Age= Tab.Age From inserted Tab;  
    13. Select @Class= Tab.Class From inserted Tab;  
    14.   
    15.   
    16. Update Student set Name=@Name ,Age=@Age where IId=@IId  
    17. Print 'Data Inserted Successfully'  
    18. End  
  • And the second trigger is for an update as in the following:
    1. Create Trigger [dbo].[Update_] On [dbo].[Student]  
    2. For Update  
    3. as  
    4. begin  
    5. Declare @IId int ;  
    6. Declare @Name Nvarchar(50) ;  
    7. Declare @Age Int;  
    8. Declare @Class int;  
    9.   
    10. Select @IId= Tab.IId From inserted Tab;  
    11. Select @Name= Tab.Name From inserted Tab;  
    12. Select @Age= Tab.Age From inserted Tab;  
    13. Select @Class= Tab.Class From inserted Tab;  
    14. Set @IId=@IId+1;  
    15. Insert Into Student(IId,Name , Age ,Class) Values (@IId,@Name,@Age,@Class)  
    16. Print 'Data Updated Successfully'  
    17. End  
Now we insert some data into the table and check what will 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 creates 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.
 
Note
 
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.
 

Summary

 
In this chapter, we learned about what is Trigger in SQL and how to use Triggers in SQL with various options. 
Author
Pankaj Kumar Choudhary
Pankaj Kumar Choudhary
72 26.6k 13.5m