A trigger is a mechanism that is invoked when a particular action occurs on a particular table. Each trigger has three general parts:
- A name
- The action
- The execution
The maximum size of a trigger name is 128 characters. The action of a trigger can be either a DML statement (INSERT, UPDATE, or DELETE) or a DDL statement. Therefore, there are two trigger forms: DML triggers and DDL triggers. The execution part of a trigger usually contains a stored procedure or a batch.
Creating a DML Trigger
A trigger is created using the CREATE TRIGGER statement, which has the following form:
CREATE TRIGGER [schema_name.]trigger_name
ON {table_name | view_name}
[WITH dml_trigger_option [,…]]
{FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE]}
[WITH APPEND]
{AS sql_statement | EXTERNAL NAME method_name}
Code language: PHP (php)
schema_name is the name of the schema to which the trigger belongs. trigger_name is the name of the trigger. table_name is the name of the table for which the trigger is specified. (Triggers on views are also supported, as indicated by the inclusion of view_name.)
AFTER and INSTEAD OF are two additional options that you can define for a trigger. (The FOR clause is a synonym for AFTER.) AFTER triggers fire after the triggering action occurs. INSTEAD OF triggers are executed instead of the corresponding triggering action. AFTER triggers can be created only on tables, while INSTEAD OF triggers can be created on both tables and views. Examples showing the use of these two trigger types are provided later in this chapter.
The INSERT, UPDATE, and DELETE options specify the trigger action. (The trigger action is the type of Transact-SQL statement that activates the trigger.) These three statements can be written in any possible combination. The DELETE statement is not allowed if the IF UPDATE option is used.
As you can see from the syntax of the CREATE TRIGGER statement, the AS sql_statement specification is used to determine the action(s) of the trigger. (You can also use the EXTERNAL NAME option, which is explained later in this chapter.)
Only the database owner, DDL administrators, and the owner of the table on which the trigger is defined have the authority to create a trigger for the current database. (In contrast to the permissions for other CREATE statements, this permission is not transferable.)
Modifying a Trigger’s Structure
Transact-SQL also supports the ALTER TRIGGER statement, which modifies the structure of a trigger. The ALTER TRIGGER statement is generally used to modify the body of the trigger. All clauses and options of the ALTER TRIGGER statement correspond to the clauses and options with the same names in the CREATE TRIGGER statement.
The DROP TRIGGER statement removes one or more existing triggers from the current database.
The following section describes deleted and inserted tables, which play a significant role in a triggered action.
Using deleted and inserted Virtual Tables
When creating a triggered action, you usually must indicate whether you are referring to the value of a column before or after the triggering action changes it. For this reason, two virtual tables with special names are used to test the effect of the triggering statement:
- deleted Contains copies of rows that are deleted from the triggered table
- inserted Contains copies of rows that are inserted into the triggered table
The structure of these tables is equivalent to the structure of the table for which the trigger is specified.
The deleted table is used if the DELETE or UPDATE clause is specified in the CREATE TRIGGER statement. The inserted table is used if the INSERT or UPDATE clause is specified in the CREATE TRIGGER statement. This means that for each DELETE statement executed in the triggered action, the deleted table is created. Similarly, for each INSERT statement executed in the triggered action, the inserted table is created.
An UPDATE statement is treated as a DELETE, followed by an INSERT. Therefore, for each UPDATE statement executed in the triggered action, the deleted and inserted tables are created (in this sequence).
The materialization of inserted and deleted tables is done using row versioning, which is discussed in detail in Chapter “Concurrency Control“. When DML statements such as INSERT, UPDATE, and DELETE are executed on a table with corresponding triggers, all changes to the table are always versioned. When the trigger needs the information from the deleted table, it accesses the data from the version store. In the case of the inserted table, the trigger accesses the most recent versions of the rows.