EXPLAIN THE MAIN WORK OF THE TRIGGER IN SQL SERVER

Posted by Rajamani under Sql Server on 11/20/2012 | Points: 10 | Views : 1091 | Status : [Member] | Replies : 3
How to create the trigger? what is the use of the trigger?




Responses

Posted by: Sandhyab on: 11/20/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

Creating a Trigger:

Syn:

CREATE TRIGGER <trigger_name>
ON {table|View}
{FOR [AFTER] | INSTEAD OF } { INSERT [,DELETE,UPDATE]}
AS
BEGIN
<SQL statements>
END

For:

You can use the FOR clause to specify when a trigger is executed.


AFTER:

The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views, they can only be specified for tables. You can specify multiple AFTER triggers for each triggering action (INSERT, UPDATE, or DELETE). If you have multiple AFTER triggers for a table, you can use sp_settriggerorder to define which AFTER trigger fires first and which fires last. AFTER is the default in SQL Server 2000.

INSTEAD OF:
The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views. You can define only one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). INSTEAD OF triggers can be used to perform enhance integrity checks on the data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers also let you specify actions that allow views, which would normally not support updates, to be Updatable.

Thanks & Regards



Rajamani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saratvaddilli on: 11/21/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Please Go through this link once
http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Rajamani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Krv on: 11/21/2012 [Member] Starter | Points: 25

Up
0
Down
Trigger is an special kind of stored procedure, which executes automatically whenever there is change in the data / schema

Thanks,
R.Vasanth
MCTS(SQL Server & BI)
http://bigdatatech.blogspot.com

Rajamani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response