Triggers
Triggers are stored programs that are automatically executed or fired when some events occur on data. A trigger is a database object bound to a table and executed automatically. We cannot explicitly call a trigger.
Triggers are used in the following events:
- DML (Data Manipulation Language)(Insert, Update, Delete).
- DDL (Data Definition Language)(Create, Alter, Drop).
- Database Operations such as LOGON, LOGOFF, SERVERERROR, SHUTDOWN, STARTUP.
Now we will understand what triggers are and how they work.
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:
![table]()
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,
- After Triggers
- 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 three parts:
- After Insert
- After Delete
- After Update
After Insert
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 that 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)
We used the "Deleted" magic table in the After Delete trigger. 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 continue 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 we can use both the "Inserted" and "Deleted" magic tables in an After Update trigger. 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; the second step is inserting the data into the table. The "Inserted" magic table contains the inserted data in that step.
So we can use both magic tables in an "After Update" trigger.
Instead of Triggers
These can be used as an interceptor for anything anyone tries to do on our table or view. This type of trigger fires before the SQL Server starts the execution of the action that fired it. For example, 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 data is deleted from the table, this trigger will execute first. Still, 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
- Instead Of Insert
- Instead Of Update
- Instead Of Delete
Example
The table before execution of an "Instead Of Delete."
![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.
![examine the result]()
We can see that we execute the query for the deletion of the student record from the table that has 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.
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.
![result]()
Cyclic Cascading in a Trigger
This undesirable situation is where more than one trigger enters an infinite loop. Therefore, when creating a trigger, we should ensure that such a situation cannot happen. For example, 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
So 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 the Update_ trigger that contains an insert statement, so when it executes, it will create a call for the Insert_ trigger. So an infinite loop will occur.
So while creating 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.
Check out Triggers in SQL Server: A Beginner's Guide (c-sharpcorner.com) to learn more.