3 4
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: UPDATE, INSERT, DELETE, INSTEAD OF, and AFTER. Like their names imply, an UPDATE trigger fires when an update is performed on a table, an INSERT trigger fires when data is inserted into the table, and a DELETE trigger fires when data is deleted from a table. An INSTEAD OF trigger is executed instead of an insert, update, or delete operation. 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 even 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:
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.