-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: manivasakan_p
Message 3 in Discussion
Triggers
Microsoft� SQL Server� 2000 triggers are a special class of stored procedure defined
to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against
a table or view. Triggers are powerful tools that sites can use to enforce their
business rules automatically when data is modified. Triggers can extend the integrity
checking logic of SQL Server constraints, defaults, and rules, although constraints
and defaults should be used instead whenever they provide all the needed
functionality.
Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with
the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific
class of data modification actions. When FOR UPDATE is specified, the IF UPDATE
(column_name) clause can be used to target a trigger to updates affecting a particular
column.
Triggers can automate the processing for a company. In an inventory system, update
triggers can detect when a stock level reaches a reorder point and generate an order
to the supplier automatically. In a database recording the processes in a factory,
triggers can e-mail or page operators when a process exceeds defined safety limits.
The following trigger generates an e-mail whenever a new title is added in the pubs
database:
CREATE TRIGGER reminder
ON titles
FOR INSERT
AS
EXEC master..xp_sendmail 'MaryM',
'New title, mention in the next report to distributors.'
Triggers contain Transact-SQL statements, much the same as stored procedures.
Triggers, like stored procedures, return the result set generated by any SELECT
statements in the trigger. Including SELECT statements in triggers, except statements
that only fill parameters, is not recommended. This is because users do not expect to
see any result sets returned by an UPDATE, INSERT, or DELETE statement.
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.
All other AFTER triggers besides the first and last fire in an undefined order which
you cannot control.
AFTER is the default in SQL Server 2000. You could not specify AFTER or INSTEAD OF in
SQL Server version 7.0 or earlier, all triggers in those versions operated as AFTER
triggers.
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.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
