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


Top 5 Contributors of the Month
Easy Web
Imran Ghani

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

 Subscribe to Articles

Triggers in SQL Server

Posted By:Shashi Ray       Posted Date: September 30, 2008    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

Triggers are a special type of procedure that are fired automatically when an query is executed on a table or a view. Triggers provide a powerful way of control how action queries modify the data in your database. With the triggers you can enforce design rules, implement business logic and prevent data inconsistency with a flexibility that cant be duplicated any other way.
 

Triggers


Triggers are a special type of procedure that are fired automatically when an query is executed on a table or a view. Triggers provide a powerful way of control how action queries modify the data in your database. With the triggers you can enforce design rules, implement business logic and prevent data inconsistency with a flexibility that cant be duplicated any other way.

Trigger Creation


The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.

CREATE TRIGGER trigger_name
ON {table_name|view_name}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]
As sql_statements


FOR is same as AFTER but it is for backward compatibility. Each trigger is associated with the table or view names in the ON clause. Although each trigger is associated with a single table or view, a single table can have many number of AFTER triggers. Since two or more triggers on table can cause confusion to manage and to debug however its better to place all the related code in one trigger for each action. A view can't have AFTER triggers.

CREATE TRIGGER Shashi_INSERT_UPDATE
ON Shashi AFTER INSERT,UPDATE
AS
UPDATE Shashi
SET ln_name = UPPER(ln_name) 
WHERE Shashi_id in (SELECT Shashi_id from Inserted)

The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case the trigger fires after an insert or update operation is performed on the table. If you closely observe in the trigger body we have used a sub query and a table named Inserted in from clause, this is a special table that's created by SQL Server during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger is executing, you can only refer to it in the trigger code. In addition to the inserted table you have one more table i.e. deleted which contains the information about the rows deleted. These tables are called Magic tables.

An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires. AFTER triggers can be used to enforce referential integrity.
An INSTEAD of trigger can be associated with a table or view. However INSTEAD OF triggers are used most often to provide better control of updatable views.
INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that performs the operation. Each table or view can have only one INSTEAD OF trigger for each type of action.

How to delete or Change a Trigger
To change the definition of a trigger you can use ALTER TRIGGER or else to drop trigger use DROP TRIGGER.

The syntax of the DROP triggers statement.

DROP TRIGGER trigger_name [,.]



The syntax of the ALTER TRIGGER statement

ALTER TRIGGER trigger_name
ON {table_name|view_name}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]
As sql_statements

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

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