What are different Types of Triggers available in SQL Server

How to use DML Triggers in SQL Server
A Trigger is a Database Object. The Trigger is basically a special type of Stored Procedure that will automatically fired/executed when a DDL or DML command statement related with the trigger is executed.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers
The Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

1.Data Manipulation Language (DML) Triggers : 
The DMLTriggers will fire when you perform a DML Operation like INSERT,UPDATE and DELETE on the associated Table. The DML Triggers are mainly 2 Types.

AFTER Trigger (using FOR/AFTER CLAUSE) :
These Triggers will fire/execute AFTER an action such as Insert, Update or Delete is performed on the associated Table.
For example, If you insert a record/row in a Table then the trigger related/associated with the Insert event on that Table will fire only after the row passes through all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Syntax:

Create TRIGGER Trigger_Name
       ON TABLE
FOR/AFTER INSERT
AS
BEGIN
     --Your DML Operation here...

END

GO

----------------------------------------------------------
Scenario :
Suppose I have a Employee Master table, where I want to create the Triggers for Insert/ Update/Delete events to Track the changes/modifications done on that Table. The Audit( changes/modifications) information will be stored in a different Employee Audit table.

Now lets create the Tbl_Emp_Master and Tbl_Emp_Audit tables as follows...

--Employee Master Table
Create Table Tbl_Emp_Master
( Emp_Id Int Not null,
  Emp_Name Varchar(50),
  Dept_Name Varchar(50),
  Job_Title Varchar (50),
  Emp_Status Varchar (50),
  Net_Sal Float,
  )
--Employee Audit Table 
Create Table Tbl_Emp_Audit
(
  Audit_Id Int Identity (1,1) Not Null,
  EmpId Int Not null,
  Audit_Action Varchar(50),
  Audit_TimeStamp DateTime Default Getdate()
  )
GO

Truncate Table Tbl_Emp_Master
Truncate Table Tbl_Emp_Audit
GO

Next Create the DML Triggers on the table Tbl_Emp_Master as follows...
----------------------------------------------------------
AFTER INSERT Trigger:

Now we will create the Insert Trigger on the Table [Tbl_Emp_Master]

Create TRIGGER [dbo].[Trgr_Emp_INSERT]
       ON [dbo].[Tbl_Emp_Master]
AFTER INSERT
AS
BEGIN

       SET NOCOUNT ON;
       Declare @vEmpId Int
       Select @vEmpId = INSERTED.Emp_Id  From INSERTED
Insert Into Tbl_Emp_Audit  ( EmpId,Audit_Action) VALUES(@vEmpId, 'Inserted the Employee Record')
Print 'After INSERT Trigger - [Trgr_Emp_INSERT] has been Fired'
END
GO
After I defined a Insert Trigger on the table [Tbl_Emp_Master] , I am inserting few records into the Table...
Insert Into Tbl_Emp_Master  Values ( 123,'Ravi Teja','Business Intelligence','Report Analyst','Active',25000)

Insert Into Tbl_Emp_Master  Values ( 143,'TriVikram','Story Board','Script Analyst','Active',75000)
Insert Into Tbl_Emp_Master  Values ( 113,'TP Reddy','Information Technology','Software Engineer','In Active',55000)

Output :
Now we will see how the Insert Trigger fired for each Insert event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.

Please note that SQL Server will use the Logical Table "INSERTED" for performing the Insert operation then it will do the Insert in the actual physical Table.
The INSERTED Table can hold all the records for a while,that we can refer in Trigger event.

Select * From Tbl_Emp_Master

Select * From Tbl_Emp_Audit

----------------------------------------------------------

AFTER UPDATE Trigger:
Now we will create the Update Trigger on the Table [Tbl_Emp_Master]
Create TRIGGER [dbo].[Trgr_Emp_UPDATE]
       ON [dbo].[Tbl_Emp_Master]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
       Declare @vEmpId Int
       Declare @vAuditAction Varchar(50)
       Select @vEmpId = INSERTED.Emp_Id  From INSERTED

  IF UPDATE(Dept_Name)
          BEGIN
                SET @vAuditAction = ' Udpated the Employee Dept Name'
          END
   IF UPDATE(Job_Title)
          BEGIN
                SET @vAuditAction = 'Udpated the Employee Job Title'
          END

   IF UPDATE(Emp_Status)
          BEGIN
                SET @vAuditAction = 'Udpated the Employee Status'
          END

  IF UPDATE(Net_Sal)
          BEGIN
                SET @vAuditAction = 'Udpated the Employee Net Salary'
          END
Insert Into Tbl_Emp_Audit  ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
PrInt 'After UPDATE Trigger - [Trgr_Emp_UPDATE] has been Fired'
END
GO

After I defined a Update Trigger on the table [Tbl_Emp_Master] , I am updating the records as follows...
Update Tbl_Emp_Master  Set Job_Title='Data Analyst' Where Emp_Id=123
Update Tbl_Emp_Master  Set  Net_Sal=50000 Where Emp_Id=123

Output :
Now we will see how the Update Trigger fired for each Update event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.

Please note that SQL Server will use the Logical Table "INSERTED" for performing the Update operation then it will do the update in the actual physical Table.

Select * From Tbl_Emp_Master

Select * From Tbl_Emp_Audit

----------------------------------------------------------

AFTER DELETE Trigger:

Now we will create the Delete Trigger on the Table [Tbl_Emp_Master]

Create TRIGGER [dbo].[Trgr_Emp_DELETE]
       ON [dbo].[Tbl_Emp_Master]
AFTER DELETE
AS
BEGIN

       SET NOCOUNT ON;
       Declare @vEmpId Int
       Declare @vAuditAction Varchar(50)
       Select @vEmpId = DELETED.Emp_Id  From DELETED
       SET @vAuditAction = 'Deleted the Employee Record'  

Insert Into Tbl_Emp_Audit  ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)

PrInt 'After DELETE Trigger - [Trgr_Emp_DELETE] has been Fired'
END

GO
After I defined a Update Trigger on the table [Tbl_Emp_Master] , I am updating the records as follows...
Delete From Tbl_Emp_Master Where Emp_Id=143

Output :

Now we will see how the Insert Trigger fired for each Insert event and stored the log/Audit Information into to the Audit Table from the Logical Table INSERTED.

Please note that SQL Server will use the Logical Table "DELETED" for performing the Delete operation then it will do the Delete in the actual physical Table.
Select * From Tbl_Emp_Master

Select * From Tbl_Emp_Audit

----------------------------------------------------------

INSTEAD OF Trigger (using INSTEAD OF Clause):
These Triggers will fire/execute Before an action such as Insert, Update or Delete is performed on the associated Table. This is differs from the AFTER trigger, which fires after the action that caused it to fire.
We can have an INSTEAD OF Insert/Update/Delete trigger on a Table that successfully executed but does not include the actual insert/update/delete to the table.

For example, If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes through all the constraints, such as primary key constraint and some rules. Even If the record/row insertion fails, SQL Server will still fires the Instead of Trigger.

Now I am going explain the INSTEAD OF DELETE Trigger on the table Tbl_Emp_Master for the DML event DELETE.

You can try for other DML Operations(INSTEAD OF INSERT,INSTEAD OF UPDATE) in the same way.
----------------------------------------------------------
INSTEAD OF DELETE (Before Delete)Trigger:
Now we will create the INSTEAD OF DELETE Trigger on the Table [Tbl_Emp_Master] , to perform an operation Instead of Delete when Delete event occurred.

Here in the below example, when we try to Delete an 'Active' Employee record, then it will raise an Error instead of deleting the record.

Create TRIGGER [dbo].[Trgr_Emp_InsteadOfDELETE]
       ON [dbo].[Tbl_Emp_Master]
INSTEAD OF DELETE
AS
BEGIN   

  SET NOCOUNT ON;

  Declare @vEmpId Int
  Declare @vEmpStatus Varchar(50)
  Declare @vAuditAction Varchar(50)

  Select @vEmpId = DELETED.Emp_Id  From DELETED
  Select @vEmpStatus=DELETED.Emp_Status From DELETED

  IF @vEmpStatus='Active'
   Begin
    RAISERROR('You cannot delete an Active Employee',15,1)
    ROLLBACK
    SET @vAuditAction = 'The Active Employee Record Cannot be Deleted'
    Insert Into Tbl_Emp_Audit  ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
   End
  ELSE
   Begin
    Delete From Tbl_Emp_Master Where Emp_Id=@vEmpId
    SET @vAuditAction = 'The InActive Employee Record Deleted'
    Insert Into Tbl_Emp_Audit  ( EmpId,Audit_Action) VALUES(@vEmpId, @vAuditAction)
   End

Print 'INSTEAD Of DELETE Trigger - [Trgr_Emp_InsteadOfDELETE] has been Fired'

END

GO
After I defined a Instead of DeleteTrigger on the table [Tbl_Emp_Master] , I am trying to Delete and Active Employee records as follows...

Delete From Tbl_Emp_Master Where Emp_Id=143
Output :


Now we will see how the Instead of Delete Trigger fired for the Delete event and stored the log/Audit Information into to the Audit Table from the Logical Table DELETED.
Error Message :

Msg 50000, Level 15, State 1, Procedure Trgr_Emp_InsteadOfDELETE, Line 180
You cannot delete an Active Employee
INSTEAD Of DELETE Trigger - [Trgr_Emp_InsteadOfDELETE] has been Fired
Msg 3609, Level 16, State 1, Line 163
The transaction ended in the trigger. The batch has been aborted.

Dropping Triggers :
Drop Trigger [Trgr_Emp_INSERT]
Drop Trigger [Trgr_Emp_UPDATE]
Drop Trigger [Trgr_Emp_DELETE]
Drop Trigger [Trgr_Emp_InsteadOfDELETE]
----------------------------------------------------------

2.Data Definition Language (DDL) Triggers :
The DDL Triggers will fire when you perform a DDL Operation like CREATE,ALTER and DROP on the associated Table.We can use only FOR/AFTER clause in DDL Triggers and we cannot use INSTEAD OF clause.

3.CLR triggers :
CLR triggers are special type of Triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for Triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
We can write code for both DDL and DML Triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.
Logon triggers.

4.Logon Triggers :
Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

----------------------------------------------------------
Notes :
We can Call a Stored Procedure or a Function inside a Trigger
Eg:
Create TRIGGER [dbo].[TriggerName]
       ON [dbo].[TableName]
INSTEAD OF/AFTER [DML]
AS
BEGIN   
  SET NOCOUNT ON;
  Declare @variable1 Int
  Declare @variable2 Varchar(50)
--Calling/Executing a procedure
EXECUTE [dbo].[sp_MyProcedure] Parameter,"Parameter2"
--Calling/Executing a Function
Select * From [dbo].[udf_MyFunction] (Argument1,"Argument2")
END

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------