SQL Language

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:

  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: 

    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.

  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

Table before 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 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.

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 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.

Up Next
    Ebook Download
    View all
    Basic SQL Queries
    Read by 10.6k people
    Download Now!
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs