In this article, I am going to explain you Triggers in SQL-Server with examples. I have summarize all types of triggers as shown below. Links are provided for each triggers.

What is Trigger in SQL-Server

A trigger is a special kind of stored procedure that invokes or gets executed in response to certain action on table like insert, update or delete. You can not explicitly invoke triggers.

Types of Trigger

Below is types of triggers.1. DML Triggers (Data Manipulation Language Triggers)2. DDL Triggers (Data Definition Language Triggers)3. CLR Triggers4. Logon Triggers

DML Triggers

DML triggers are fired automatically in response to DML events that is INSERT, UPDATE and DELETE.Below is types of DML triggers.1. After Triggers (also called as FOR trigger)2. Instead of Triggers

1. After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after Sql Server completes the execution of the action successfully that fired it.Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.AFTER Insert (for insert) → Click here to read more.AFTER Update (for update) → Click here to read moreAFTER Delete (for delete)

2. Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different 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.Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger. INSTEAD of InsertINSTEAD of UpdateINSTEAD of Delete

DISABLING and ENABLING Trigger

We can disable and enable trigger using SQL Server Management Studio or using SQL below query.1. Using Management StudioExpand your database → go to Tables and expand it → Go to your respective table and expand it → go to Triggers → Right click on trigger name and now you can modify, disable and delete ..etc.2. SQL Query

--To disable
DISABLE TRIGGER tr_Employee_AfterUpdate on Employee
--To enable
ENABLE TRIGGER tr_Employee_AfterUpdate on Employee

--Before enabling/disabling you can check if exists
IF OBJECT_ID ('tr_Employee_AfterUpdate', 'TR') IS NOT NULL
begin
   DISABLE TRIGGER tr_Employee_AfterUpdate on Employee
   print 'Trigger disabled'
end
else
begin
   print 'No trigger found'
end

DROPPING Trigger

We can drop or delete triggers using SQL Server Management Studio or using SQL below query.1. Using Management StudioAlready explained. 2. SQL Query

--To delete or drop
DROP TRIGGER tr_Employee_AfterUpdate

--Before deleting you can check if exists
IF OBJECT_ID ('tr_Employee_AfterUpdate', 'TR') IS NOT NULL
begin
DROP TRIGGER tr_Employee_AfterUpdate
   print 'Trigger deleted'
end
else
begin
   print 'No trigger found'
end

List All triggers Associated with Table

SELECT 
 t.name AS TableName,
 tr.name AS TriggerName  
FROM sys.triggers tr
INNER JOIN sys.tables t ON t.object_id = tr.parent_id
WHERE 
t.name in ('Table_Name');

List All triggers Associated with database

SELECT OBJECT_NAME(parent_id) as Parent_Object_Name, *
FROM sys.triggers

SELECT 
     sysobjects.name AS trigger_name ,
    USER_NAME(sysobjects.uid) AS trigger_owner, 
    s.name AS table_schema ,
    OBJECT_NAME(parent_obj) AS table_name ,
    OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS 'Update Trigger',
    OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS 'Delete Trigger', 
    OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS 'Insert Trigger',
    OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS  'After',
    OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger')AS 'InsteadOf',
    OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS 'Disabled' 
FROM sysobjects 

INNER JOIN sysusers 
ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

SELECT
    [so].[name] AS [trigger_name],
    USER_NAME([so].[uid]) AS [trigger_owner],
    USER_NAME([so2].[uid]) AS [table_schema],
    OBJECT_NAME([so].[parent_obj]) AS [table_name],
    OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS 'Update Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS 'Delete Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS 'Insert Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS 'After',
    OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS 'InsteadOf',
    OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS 'Disabled' 
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'