In this Article you can learn how to create and use DML TRIGGERS .
Types of TriggersTriggers are of 3 types in SQL Server 2005:
1. DML Triggers• AFTER Triggers
• INSTEAD OF Triggers
2. DDL Triggers
3. CLR TriggersNote: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 DELETE1) creating After INSERT Trigger:-Syntax:create trigger triggername
on tablename
AFTER INSERT
As
[SQL Statement/PRINT command]
GOEg: 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