Types of triggers in sql server with example

Types of Triggers

Triggers are of 3 types in SQL Server 2005:
1.   DMLTriggers
. AFTER         Triggers
.INSTEAD       OF      Triggers
2. DDL Triggers
3. CLR  Triggers

Note:DDL and CLR Triggers cannot work in SQL Server 2000
DML Trigger:-These Trigger is fired only when INSERT, UPDATE, and DELETE Statement occurs in table.

Explanation   on        DML    Trigger:
Let us create a Table and insert some records in that Table.
1)After Triggers:
After Triggers can be created in 3 ways.1) After         INSERT
2) After         UPDATE
3) After         DELETE
1) creating After INSERT Trigger:-
Syntax:create trigger           triggername             on        tablename     AFTER INSERT
As
[SQL    Statement/PRINT   Command]
GO
Eg:
create trigger           afterinsert_trigger             on        emp    AFTER INSERT
as
PRINT 'AFTER TRIGGER EXECUTED SUCESSFULLY'
GO

When you execute the afterinsert_trigger it gives message as 'The Command(s) created successfully'
You can see the is trigger is created.

Now insert one record in a emp table. You can see the trigger will be fired automatically when the row is inserted in a table successfully.

Creating AFTER UPDATE TRIGGER:-

create trigger afterupdate_trigger

on emp

AFTER UPDATE

as

PRINT 'AFTER UPDATE TRIGGER EXECUTED SUCESSFULLY'

GO

 

 

Creating AFTER DELETE TRIGGER:

Create trigger afterdelete_trigger

On emp

AFTER DELETE

as

PRINT 'AFTER DELETE TRIGGER EXECUTED SUCESSFULLY'

GO

 

Instead Of Update Trigger

Creating INSTEAD OF UPDATE TRIGGER:-

create trigger insteadofupdate_trigger
on emp
INSTEAD OF UPDATE
as
PRINT 'INSTEAD OF UPDATE TRIGGER EXECUTED SUCESSFULLY'
GO

               

 

Instead of Delete Trigger

Creating INSTEAD OF DELETE TRIGGER:-

create trigger insteadofdelete_trigger
on emp
INSTEAD OF DELETE
as
PRINT 'INSTEAD OF DELETE TRIGGER EXECUTED SUCESSFULLY'
GO