Types of Triggers

Triggers are of 3 types in SQL Server 2005:
1.    DML Triggers
•         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
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 TRIGGER:-

 

Syntax:

create trigger triggername

on tablename

INSTEAD OF INSERT

As

[SQL Statement/PRINT command]

 

GO

 

Example:-

create trigger insteadofinsert_trigger

on emp

INSTEAD OF INSERT

as

PRINT 'INSTEAD OF INSERT TRIGGER EXECUTED SUCESSFULLY'

GO

Creating INSTEAD OF UPDATE TRIGGER:-

 

create trigger insteadofupdate_trigger

on emp

INSTEAD OF UPDATE

as

PRINT 'INSTEAD OF UPDATE TRIGGER EXECUTED SUCESSFULLY'

GO

Creating INSTEAD OF DELETE TRIGGER:-

 

create trigger insteadofdelete_trigger

on emp

INSTEAD OF DELETE

as

PRINT 'INSTEAD OF DELETE TRIGGER EXECUTED SUCESSFULLY'

GO

HOW TO Drop a Trigger?

 

Syntax: DROP TRIGGER triggername

 

Eg: DROP TRIGGER insteadofdelete_trigger

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.