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.
NOTE The Database Engine allows you to create triggers using either Transact-SQL or CLR programming languages such as C# and Visual Basic. This section describes the use of Transact-SQL to implement triggers. The implementation of triggers using CLR programming languages is not covered in this edition of the book. You can find it in the previous edition.
1. Creating a DML Trigger
A DML 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}
NOTE The preceding syntax covers only DML triggers. DDL triggers have a slightly different syntax, which will be shown later in this chapter.
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.) 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.)
NOTE The Database Engine allows you to create multiple triggers for each table and for each action (INSERT, UPDATE, and DELETE). By default, there is no defined order in which multiple triggers for a given modification action are executed. (You can define the order by using the first and last triggers, as described 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.)
2. 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 virtual tables, which play a significant role in a triggered action.
3. 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 virtual table is used if the DELETE or UPDATE clause is specified in the CREATE TRIGGER statement. The inserted virtual 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 virtual table is created. Similarly, for each INSERT statement executed in the triggered action, the inserted virtual 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 virtual tables are created (in this sequence).
The materialization of the inserted and deleted virtual tables is done using row versioning, which is discussed in detail in Chapter 13. 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 virtual table, it accesses the data from the version store. In the case of the inserted virtual table, the trigger accesses the most recent versions of the rows.
NOTE Row versioning uses the tempdb database as the version store. For this reason, you must expect significant growth of this system database if your database contains many triggers that are often used.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.