.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Jean Paul
satyapriyanayak
SP
Ashutosh Jha
Simon Robert VoIP

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to create a trigger?

Posted By:Syed Shakeer Hussain       Posted Date: June 29, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  
 

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 give
s 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


 Subscribe to Articles

     

Further Readings:

Responses
Author: komal         Company URL: http://www.dotnetspark.com
Posted Date: August 10, 2010

greattttt article easy to undestand and well described..
Author: Syed Shakeer Hussain         Company URL: http://www.dotnetspark.com
Posted Date: August 10, 2010

Hi Komal,
Thank you..for reading my article..
Author: Mallikarjuna Chigicherla         Company URL: http://mallisoftpro.wordpress.com/
Posted Date: August 13, 2010

Very Nice Article,
Thanks for posting,


Author: SantoshG         Company URL:
Posted Date: February 07, 2011

Very simple & the best article.
I have almost ended up searching 10-12 sites but this the Best one.

Thanks for making it so simple.
Keep it up.

All the Best.

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend