TRIGGERS

Syedshakeer
Posted by in Sql Server category on for Intermediate level | Views : 4721 red flag
Rating: 3 out of 5  
 1 vote(s)

In this Article you can learn how to create and use DML TRIGGERS .
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



Page copy protected against web site content infringement by Copyscape

About the Author

Syedshakeer
Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
http://www.dotnetfunda.com
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Posted by: Raja on: 2/13/2011 | Points: 25
Short and sweet article, for in depth knowledge on triggers readers can visit http://msdn.microsoft.com/en-us/library/ms189799.aspx.

Thanks!

Login to post response

Comment using Facebook(Author doesn't get notification)