In This Article we will see how to create trigger in SQL server 2005.
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'
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
HOW TO Drop a Trigger?
Syntax: DROP TRIGGER [triggername]
Eg: DROP TRIGGER afterinsert_trigger