Triggers in T-SQL

Triggers are set of codes which executes automatically when certain event occurs. Unlike stored procedure triggers do not have parameters to pass. We can have BEGIN and END to specify set of codes which are included in trigger definition. These events are may be DDL statements or DML statements or Login events. We have three types of triggers based on events:

1. DDL triggers
2. DML triggers
3. Logon triggers

DDL triggers: In DDL triggers we have After/For triggers. After/For triggers itself specifies after occurring of DDL statement this trigger will be fired automatically.
    CREATE TRIGGER <trigger-name> ON <Object-name>
      FOR/AFTER CREATE/ALTER/DROP
      AS
      BEGIN
            <statements>
      END

DML triggers: In DML triggers we have AFTER/FOR triggers and INSTEAD OF triggers. AFTER/FOR trigger behave same way as it does in DDL triggers. While in INSTEAD OF trigger it fired instead of particular DML statement. In DML triggers to catch data for particular DML operations we have two table access from SQL Server. These tables are called MAGIC TABLES.

MAGIC TABLES are INSERTED and DELETED. In INSERTED data will pop-up when you run INSERT and UPDATE statement. While in DELETED data will pop-up when you run UPDATE and DELETE statement. 

   CREATE TRIGGER <trigger-name> ON <Object-name>
      FOR/AFTER/INSTEAD OF INSERT/UPDATE/DELETE
      AS
      BEGIN
            <statements>
      END

LOGON triggers: LOGON trigger fires when user login into the Database system.
 
   CREATE TRIGGER <trigger-name> ON ALL SERVER
      FOR/AFTER LOGON
      AS
      BEGIN
            <statements>
      END



Triggers are database objects too that is the reason we can CREATE, ALTER and DROP. We can also Enable or Disable trigger for certain purposes.