Sameer Gaikwad Triggers in SQL mesameergaikwad

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 are used to access and check data before and after modification using DDL or DML queries.

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, SERVER ERROR, 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:

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 a 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 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 execution of an “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.

We can see that we execute the query for the delete of the student record from a 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.

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.

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.

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.