* A trigger is a special type of stored procedure
that is executed automatically by SQL Server
when a table is modified by any one of the three statements:
UPDATE, INSERT, or DELETE.
* Triggers, like other stored procedures,
can contain simple or complex T-SQL statements.
Unlike other types of stored procedures,
triggers execute automatically when specified data modifications occur
and cannot be executed manually by name.
* When a trigger executes, it is said to have fired.
A trigger is created on one database table,
but it can access other tables and objects in other databases.
* Triggers cannot be created on temporary tables or system tables,
only on user-defined tables or views. The table on which a trigger is defined is called a trigger table.
There are five types of triggers:
1. UPDATE,
an UPDATE trigger fires when an update is performed on a table
2. INSERT,
an INSERT trigger fires when data is inserted into the table
3. DELETE,
and a DELETE trigger fires when data is deleted from a table
4. INSTEAD OF,
An INSTEAD OF trigger is executed instead of an insert, update, or delete operation
5. AFTER.
The AFTER trigger fires after a triggering action and provides a mechanism to control the execution order of multiple triggers.
The update, insert, and delete operations are known as data modification events.
You can create a trigger that will fire at the occurrence of more than one data modification event.
For example,
you can create a trigger that will fire when either an UPDATE or INSERT statement is executed, which we'll refer to as an UPDATE/INSERT trigger. You can eve
n create a trigger that will fire when any of the three data modification events occurs
(an UPDATE/INSERT/DELETE trigger).
You should know some other general rules about triggers.
These rules are listed here:
a. Triggers are executed only after the statement that causes them to fire is completed.
For example,
an UPDATE trigger won't fire until the UPDATE statement is completed.
b. If a statement attempts to perform an operation
that violates a constraint on a table or causes some other error,
the associated trigger will not fire.
c. A trigger is included as part of a single transaction with the statement that invokes it.
Therefore, a rollback statement can be called from within the trigger
and will roll back both the trigger and the data modification event.
Also, if a severe error occurs, such as a user disconnect,
SQL Server will automatically roll back the entire transaction.
d. A trigger fires only once for a statement, even if that statement affects many rows of data.
When a trigger fires, the results,
if any, are returned to the calling application, just as with stored procedures.
Normally, results are not returned from an INSERT, an UPDATE, or a DELETE statement
(which are the statements that could cause the trigger to fire).
Results are normally returned from SELECT queries.
Thus, to avoid having results returned to the application from within a trigger,
eliminate the use of SELECT statements and variable assignments in the trigger definition.
If you do want results to be returned from within a trigger,
you must code special handling into the application
wherever modifications to the table containing the trigger are allowed
so that the application receives the returned data and handles it properly.
* If you need to assign a variable within a trigger,
use the statement SET NOCOUNT ON at the beginning of the trigger
to prevent any result rows from being returned.
The SET NOCOUNT statement specifies
whether to return a message indicating how many rows were affected by a query or statement
(for example, "23 rows affected").
By default, SET NOCOUNT is set to OFF,
which means that the affected-rows message is returned.
Note that this setting does not affect the return of any actual results from a SELECT statement;
it affects only the rowcount messages returned.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook