FAQ on Trigger in SQL Server

FAQ on Trigger in SQL Server

71. What is Trigger? What is its use? What are the types of Triggers?
What are the new kinds of triggers in sql 2000? When should one use
"instead of Trigger"?
Microsoft® SQL Serve 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.
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.
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.
An INSTEAD OF trigger can take actions such as:
• Ignoring parts of a batch.
• Not processing a part of a batch and logging the problem rows.
• Taking an alternative action if an error condition is encountered.
In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0
onwards, this restriction is gone, and you could create multiple
triggers per each action. But in 7.0 there's no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify
which trigger fires first or fires last using sp_settriggerorder.
Triggers can't be invoked on demand. They get triggered only when an
associated action (INSERT, UPDATE, DELETE) happens on the table on
which they are defined.
Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks,
but wherever possible, use constraints for this purpose, instead of
triggers, as constraints are much faster. Till SQL Server 7.0,
triggers fire only after the data modification operation happens. So
in a way, they are called post triggers. But in SQL Server 2000 you
could create pre triggers also.

72. Difference between trigger and Stored procedure?

73. 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.'

For More SQL SERVER Frequently Asked Interview Questions