What are the Different Types of Triggers?


There are four types of triggers in SQL Server

  • DML Triggers:- DML triggers fired when any insert, update, Delete event fired on table. DML triggers can be categories into two parts.
    • After Triggers : These triggers work on tables only. They are not supported by Views.
      • After Insert Triggers: When user insert rows in “Table_Name” table. This trigger will insert same rows to Table_Name_Log table which has same structure as Table_Name.

        CREATE TRIGGER Trigger_Name ON [dbo].[Table_Name]
        FOR INSERT
        AS
        --Statement
        

      • After Update Triggers: When user update rows in “Table_Name” table. This trigger will insert old rows from “Table_Name” to Table_Name_Log table which has same structure as Table_Name.

        CREATE TRIGGER Trigger_Name ON [dbo].[Table_Name]
        FOR UPDATE
        AS
        --Statement
        

      • After Delete Triggers: When user delete rows in “Table_Name” table. This trigger will insert old rows from “Table_Name” to Table_Name_Log table which has same structure as Table_Name.

        CREATE TRIGGER Trigger_Name ON [dbo].[Table_Name]
        FOR DELETE
        AS
        --Statement
        

    • Instead of Trigger : These triggers work on both tables and Views. INSTEAD OF trigger can be defined to perform error or value checking on one or more columns.
      • Instead of Insert Triggers: This trigger is usually used with View to insert data in one or more tables.

        CREATE TRIGGER Trigger_Name ON [dbo].[Table_Name]
        Instead of Insert
        AS
        --Statement
        

      • Instead of Update Triggers: This trigger is usually used with View to update data in one or more tables.

        CREATE TRIGGER Trigger_Name ON [dbo].[Table_Name]
        Instead of Update
        AS
        --Statement
        

      • Instead of Delete Triggers:  This trigger is usually used with View to delete data in one or more tables.

        CREATE TRIGGER Trigger_Name ON [dbo].[Table_Name]
        Instead of Delete
        AS
        --Statement
        

  • DDL Triggers:- These triggers are fired when drop, alter, create commands are called. DDL triggers are always after triggers.
  • Logon Triggers:- This trigger is fired when user login to SQL server and authentication passed. If the authentication failed this trigger will not be fired. This trigger is used to track number of login users.
  • CLR Triggers:- These triggers are special type of triggers which are run using .Net Framework with SQL server. These triggers are programmed using .Net languages (C#, VB,VB.Net).