SQL trigger details

SQL trigger syntax
Syntax
Create trigger trigger_name
ON {table | view}
[With encryption]
{
{FOR | AFTER | instead of} {[INSERT] [DELETE] [UPDATE]}
[With append]
[Not for replication]
AS
[{If update (column)
[{AND | OR} UPDATE (column)]
[... N]
| IF (COLUMNS_UPDATED () updated_bitmask)
Column_bitmask [... n]
}]
SQL _statement [... n]
}
}
Parameters
Trigger_name
Is the name of the trigger. The trigger name must comply with the identifier rules and must be unique in the database. You can choose whether to specify the trigger owner name.
Table | view
Is the table or view on which the trigger is executed, sometimes called the trigger table or trigger view. You can choose whether to specify the table or view owner name.
WITH ENCRYPTION
Encrypt entries in the syscomments table that contain the create trigger statement text. Use with encryption to prevent publishing of triggers as part of SQL Server replication.
AFTER
The specified trigger is triggered only when all specified operations in the SQL statement are successfully executed. This trigger can be executed only after all reference cascade operations and constraints check are completed successfully.
If only the FOR keyword is specified, AFTER is the default setting.
You cannot define an AFTER trigger on a view.
INSTEAD
Specify the trigger to be executed, instead of the SQL statement to be triggered, to replace the trigger statement operation.
In a table or view, each INSERT, UPDATE, or DELETE statement can define up to one INSTEAD trigger. However, you can define a view on each view with an instead of trigger.
The instead of trigger cannot be defined in the updatable view with check option. If an instead of trigger is added to the updatable view WITH the check option specified, SQL Server generates an error. You must use alter view to delete this option before defining the instead of trigger.
{[DELETE] [,] [INSERT] [,] [UPDATE]}
It specifies which data modification statements will be executed on a table or view to activate the trigger keywords. You must specify at least one option. These keywords can be combined in any order in the trigger definition. If more than one option is specified, separate them with commas.
For an instead of trigger, the DELETE option cannot be used ON a table with the on delete cascade operation reference relationship. Similarly, you cannot use the UPDATE option ON a table that has an on update cascade reference relationship.
WITH APPEND
Specify other triggers of the existing type. This optional clause is required only when the compatibility level is 65 or lower. If the compatibility level is 70 or higher, you do not need to use the with append clause to add other triggers of the existing type (this is the default action for the create trigger with the compatibility level set to 70 or higher ). For more information, see sp_dbcmptlevel.
The with append clause cannot be used WITH the instead of trigger, or if an AFTER trigger is explicitly declared, this clause cannot be used. With append can be used only when FOR (no instead of or AFTER) is specified FOR backward compatibility. Later versions will not support with append and FOR (will be interpreted as AFTER ).
NOT FOR REPLICATION
Indicates that the trigger should not be executed when the replication process changes the table involved by the trigger.
AS
Is the action to be performed by the trigger.
SQL _statement
Is the condition and operation of the trigger. Specify other criteria for the trigger condition to determine whether the DELETE, INSERT, or UPDATE statement causes the trigger to be executed.
When the DELETE, INSERT, or UPDATE operation is attempted, the trigger operation specified in the Transact-SQL statement takes effect.
A trigger can contain any number and types of Transact-SQL statements. A trigger is designed to check or change data based on data modification statements. It should not return data to users. The Transact-SQL statement in a trigger often contains the control flow language. The create trigger statement uses several special tables:
* Deleted and inserted are logical (concept) tables. These tables are structured similar to tables that define triggers (that is, tables in which user operations are attempted). These tables are used to save the old values or new values of rows that may be modified by user operations. For example, to retrieve all values in the deleted table, use:
SELECT *
FROM deleted
* If the compatibility level is 70, SQL Server will not allow reference to the text, ntext, or image columns in the inserted and deleted tables in the DELETE, INSERT, or UPDATE triggers. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve new values in an INSERT or UPDATE trigger, join the inserted table with the original UPDATE table. If the compatibility level is 65 or lower, a null value is returned for the text, ntext, or image columns that allow null values in the inserted or deleted tables. If these columns cannot be empty, returns a zero-length string.
When the compatibility level is 80 or higher, SQL Server allows the INSTEAD trigger to update text, ntext, or image columns on tables or views.
N
It indicates that the trigger can contain placeholders of multiple Transact-SQL statements. If update (column) statements can contain multiple columns by repeating the UPDATE (column) clause.
If update (column)
The INSERT or UPDATE operation on the specified column cannot be used for the DELETE operation. You can specify multiple columns. Because the table name is specified in the ON clause, do not include the table name before the column name in the if update clause. To test the INSERT or UPDATE operation on multiple columns, specify a separate UPDATE (column) clause after the first operation. In the INSERT operation, if update returns TRUE because these columns INSERT explicit or implicit (NULL) values.
The function of the if update (column) clause is equivalent to the IF, IF... ELSE, or WHILE statement, and the in... END statement block can be used. For more information, see Control Flow Language.
UPDATE (column) can be used anywhere in the trigger body ).
Column
Is the name of the column to test the INSERT or UPDATE operation. This column can be any data type supported by SQL Server. However, the computed column cannot be used in this environment. For more information, see data types.
IF (COLUMNS_UPDATED ())
Test whether the column mentioned is inserted or updated. It is only used in the INSERT or UPDATE trigger. COLUMNS_UPDATED returns the varbinary bit mode, indicating which columns are inserted or updated in the table.
The COLUMNS_UPDATED function returns bits from left to right. The leftmost bits are the least important bits. The leftmost bit indicates the first column in the table; the next bit to the right indicates the second column, and so on. If the trigger created on the table contains more than eight columns, COLUMNS_UPDATED returns multiple bytes, and the leftmost is the least important bytes. In the INSERT operation, COLUMNS_UPDATED returns the TRUE value for all columns because these columns INSERT an explicit or implicit (NULL) value.
COLUMNS_UPDATED can be used anywhere in the trigger body.
Bitwise_operator
Is a bitwise operator used for comparison operations.
Updated_bitmask
Is an integer bit mask, indicating the columns actually updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and C5. Assume that an UPDATE trigger exists on table t1. To check whether columns C2, C3, and C4 are updated, set the value to 14. To check whether only columns C2 are updated, set the value to 2.
Comparison_operator
Is a comparison operator. Use equal sign (=) to check whether all columns specified in updated_bitmask are actually updated. Use the greater sign (>) to check whether any or some of the specified columns in updated_bitmask have been updated.
Column_bitmask
Is the integer mask of the columns to be checked. It is used to check whether these columns have been updated or inserted.
Note
Triggers are often used to force business rules and data integrity. SQL Server uses the TABLE creation statement (ALTER TABLE and CREATE TABLE) to declare the integrity of the reference (DRI). However, DRI does not provide the integrity of the reference between databases. To forcibly reference integrity (rules related to the relationship between the table's primary key and foreign key ), use the primary key and foreign key constraints (the primary key and foreign key keywords of alter table and create table ). If the trigger table has constraints, check these constraints AFTER the instead of trigger is executed and before the AFTER trigger is executed. If the constraint is violated, roll back the instead of trigger operation and do not execute (fire) AFTER trigger.
Sp_settriggerorder can be used to specify the first and last executed AFTER triggers on the table. Only one first execution and the last AFTER trigger can be specified for each INSERT, UPDATE, and DELETE operation on the table. If there are other AFTER triggers on the same table, these triggers are executed randomly.
If the alter trigger statement changes the first or last TRIGGER, the first or last attribute set on the modified TRIGGER is removed, and the sorting value must be reset with sp_settriggerorder.
The AFTER trigger is executed only when the SQL statement (including all reference cascade operations and constraints checks associated with the updated or deleted objects) is successfully executed. The AFTER trigger checks the running effect of the trigger statement and the effect of all UPDATE and DELETE reference cascade operations caused by the trigger statement.
Trigger restrictions
Create trigger must be the first statement in batch processing and can only be applied to one table.
Triggers can only be created in the current database, but triggers can reference external objects of the current database.
If you specify the trigger owner name to limit the trigger, specify the table name in the same way.
In the same create trigger statement, you can define the same TRIGGER operation for multiple user operations (such as INSERT and UPDATE.
If the foreign key OF a table defines cascade in the DELETE/UPDATE operation, the instead of delete/UPDATE trigger cannot be defined in the table.
You can specify any SET statement in the trigger. The SET option selected is valid during trigger execution and is restored to the previous setting after the trigger is executed.
Like using stored procedures, when a trigger is triggered, results are returned to the calling application. To avoid returning results to applications due to trigger excitation, do not include SELECT statements for returned results or statements for assigning values to variables in triggers. Triggers that contain SELECT statements that return results to users or statements that assign values to variables must be specially processed. The returned results must be written to each application that allows modification to the trigger table. If you must assign values to variables in the trigger, use the set nocount statement at the beginning of the trigger to avoid returning any result SET.
The DELETE trigger cannot capture the truncate table statement. Although the truncate table statement does not actually have a WHERE clause to DELETE (it deletes all rows), it does not have a log record and therefore cannot execute a trigger. Because the permissions of the truncate table statement are granted to the TABLE owner by default and cannot be transferred, only the TABLE owner must consider the accidental use of the truncate table statement to avoid the DELETE trigger.
The WRITETEXT statement does not activate the trigger, whether there is a log record or no log record.
The following statements are not allowed in triggers:

The code is as follows:	Copy code
ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG

Because SQL Server does not support user-defined triggers in system tables, we recommend that you do not create user-defined triggers in system tables.
Multiple triggers
SQL Server allows you to create multiple triggers for each data modification event (DELETE, INSERT, or UPDATE. FOR example, if you execute create trigger for update on a table with an existing update trigger, another update trigger is created. In earlier versions, only one trigger is allowed for each data modification event (INSERT, UPDATE, or DELETE) on each table.
If the TRIGGER name is different, the default action of create trigger (compatible level: 70) is to add other triggers to the existing TRIGGER. If the trigger name is the same, SQL Server returns an error message. However, if the compatibility level is equal to or less than 65, the new TRIGGER created using the create trigger statement replaces any existing TRIGGER of the same type, even if the TRIGGER name is different. For more information, see sp_dbcmptlevel.
Recursive trigger
When recursive triggers is enabled in sp_dboption, SQL Server also allows recursive calls of triggers.
Recursive triggers allow two types of recursion:
* Indirect recursion
* Direct recursion
When indirect recursion is used, the application updates table T1 to stimulate trigger TR1, which updates table T2. In this case, trigger T2 will activate and update T1.
When direct recursion is used, the application updates table T1 to stimulate the trigger TR1, which updates table T1. Because table T1 is updated, trigger TR1 is triggered again, and so on.
In the following example, both indirect trigger recursion and direct trigger recursion are used. Assume that two UPDATE triggers TR1 and TR2 are defined in table T1. Trigger TR1 recursively updates table T1. The UPDATE statement executes TR1 and TR2 each time. The execution of TR1 triggers the execution of TR1 (recursion) and TR2. The inserted and deleted tables for the given trigger only contain rows corresponding to the UPDATE statement for the wake-up trigger.
This indicates that the above behavior will only occur when the recursive triggers setting of sp_dboption is enabled. There is no definite execution sequence for multiple triggers defined for a given event. Each trigger should be self-contained.
Disabling recursive triggers can only disable direct recursion. To disable indirect recursion, use sp_configure to set the nested triggers server option to 0.
If any trigger executes the rollback transaction statement, no other trigger is executed regardless of the nesting level.
Nested triggers
A trigger can be nested with up to 32 layers. If a trigger changes the table containing another trigger, the second trigger is activated, and then the trigger can call the third trigger, and so on. If any trigger in the chain triggers an infinite loop, it will exceed the nested limit, thus canceling the trigger. To disable nested triggers, use sp_configure to set the nested triggers option to 0 (off ). Nested triggers are allowed by default. If the nested trigger is disabled, the recursive trigger is also disabled, regardless of the recursive triggers setting of sp_dboption.
Latency name resolution
SQL Server allows Transact-SQL stored procedures, triggers, and batch processing to reference tables that do not exist during compilation. This capability is called latency name resolution. However, if a table defined in a stored procedure, trigger, or batch reference in a stored procedure or trigger is used, the compatibility level setting (by executing the sp_dbcmptlevel setting) is 65, will be issued at the time of creation. If batch processing is used, a warning is issued during compilation. If the referenced table does not exist, an error message is returned at runtime. For more information, see latency name parsing and compilation.
Permission
By default, the create trigger permission is granted to the table owner, sysadmin fixed server role members, db_owner, and db_ddladmin fixed database role members who define the TRIGGER, and cannot be transferred.
To retrieve data in a table or view, you must have the SELECT statement permission in the table or view. To UPDATE the table or view content, you must have the INSERT, DELETE, and UPDATE statement permissions in the table or view.
If an instead of trigger exists in the view, you must have the INSERT, DELETE, and UPDATE privileges in the view to issue INSERT, DELETE, and UPDATE statements to the view, whether or not such operations are performed on The View.
Example
A. Use A trigger with A reminder message
When someone tries to add or modify data in the titles table, the following example displays a message to the client.
Message 50009 is a user-defined message in sysmessages. For more information about creating user-defined messages, see sp_addmessage.

The code is as follows:

Copy code

USE pubs If exists (SELECT name FROM sysobjects WHERE name = 'Reminder' AND type = 'tr ') Drop trigger reminder GO Create trigger reminder ON titles For insert, UPDATE As raiserror (50009, 16, 10) GO B. Use a trigger with a reminder email When the titles table is changed, the following example sends an email to the specified person (MaryM ). USE pubs If exists (SELECT name FROM sysobjects WHERE name = 'Reminder' AND type = 'tr ') Drop trigger reminder GO Create trigger reminder ON titles For insert, UPDATE, DELETE AS EXEC master .. xp_sendmail 'marym ', 'Don't forget to print a report for the distributors .' GO

C. Use the trigger business rules between the employee and jobs tables
Because CHECK constraints can only reference columns that define column-level or table-level constraints, any constraints between tables (in the following example, business rules) must be defined as triggers.
In the following example, a trigger is created. When job_lvls is inserted or updated, the trigger checks the work level of the specified employee (thereby determining the salary) whether it is within the scope defined for this job. To obtain an appropriate range, you must reference the jobs table.

The code is as follows:

Copy code

USE pubs If exists (SELECT name FROM sysobjects WHERE name = 'employee _ insupd 'AND type = 'tr ') Drop trigger employee_insupd GO Create trigger employee_insupd ON employee For insert, UPDATE AS /* Get the range of level for this job type from the jobs table .*/ DECLARE @ min_lvl tinyint, @ Max_lvl tinyint, @ Emp_lvl tinyint, @ Job_id smallint SELECT @ min_lvl = min_lvl, @ Max_lvl = max_lvl, @ Emp_lvl = I. job_lvl, @ Job_id = I. job_id FROM employee e inner join inserted I ON e. emp_id = I. emp_id JOIN jobs j ON j. job_id = I. job_id IF (@ job_id = 1) and (@ emp_lvl <> 10) BEGIN RAISERROR ('job id 1 expects the default level of 10. ', 16, 1) ROLLBACK TRANSACTION END ELSE If not (@ emp_lvl BETWEEN @ min_lvl AND @ max_lvl) BEGIN RAISERROR ('the level for job_id: % d shocould be between % d and % d .', 16, 1, @ job_id, @ min_lvl, @ max_lvl) ROLLBACK TRANSACTION END D. Use delay name resolution In the following example, two triggers are created to describe the delay in name resolution. USE pubs If exists (SELECT name FROM sysobjects WHERE name = 'trigger' AND type = 'tr ') Drop trigger trig1 GO -- Creating a trigger on a nonexistent table. Create trigger trig1 On authors For insert, UPDATE, DELETE AS SELECT a. au_lname, a. au_fname, x.info FROM authors a inner join does_not_exist x ON a. au_id = x. au_id GO -- Here is the statement to actually see the text of the trigger. SELECT o. id, c. text FROM sysobjects o inner join syscomments c ON o. id = c. id WHERE o. type = 'tr' and o. name = 'trig1' -- Creating a trigger on an existing table, but with a nonexistent -- Column. USE pubs If exists (SELECT name FROM sysobjects WHERE name = 'trig2 'AND type = 'tr ') Drop trigger trig2 GO Create trigger trig2 ON authors For insert, UPDATE AS DECLARE @ fax varchar (12) SELECT @ fax = phone FROM authors GO -- Here is the statement to actually see the text of the trigger. SELECT o. id, c. text FROM sysobjects o inner join syscomments c ON o. id = c. id WHERE o. type = 'tr' and o. name = 'trig2'

E. Use COLUMNS_UPDATED
In the following example, two tables are created: one employeeData table and one auditEmployeeData table. Members of the human resources department can modify the employeeData table, which contains sensitive employee salary information. If an employee's social insurance number (SSN), annual salary, or bank account is changed, an audit record is generated and inserted into the auditEmployeeData audit table.
By using the COLUMNS_UPDATED () function, you can quickly test the changes made to these columns containing sensitive employee information. COLUMNS_UPDATED () takes effect only when you try to detect changes to the first eight columns in the table.

The code is as follows:

Copy code

USE pubs If exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'employeedata ') Drop table employeeData If exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'audioitemployeedata ') Drop table auditEmployeeData GO Create table employeeData ( Emp_id int not null, Emp_bankAccountNumber char (10) not null, Emp_salary int not null, Emp_SSN char (11) not null, Emp_lname nchar (32) not null, Emp_fname nchar (32) not null, Emp_manager int NOT NULL ) GO Create table auditEmployeeData ( Audit_log_id uniqueidentifier default newid (), Audit_log_type char (3) not null, Audit_emp_id int not null, Audit_emp_bankAccountNumber char (10) NULL, Audit_emp_salary int NULL, Audit_emp_SSN char (11) NULL, Audit_user sysname DEFAULT SUSER_SNAME (), Audit_changed datetime default getdate () ) GO Create trigger updEmployeeData ON employeeData FOR update /* Check whether columns 2, 3 or 4 has been updated. if any or all of columns 2, 3 or 4 have been changed, create an audit record. the bitmask is: power (2, (2-1) + power (2, (3-1) + power (2, (4-1) = 14. to check if all columns 2, 3, and 4 are updated, use = 14 in place of> 0 (below ). */ IF (COLUMNS_UPDATED () & 14)> 0 /* Use IF (COLUMNS_UPDATED () & 14) = 14 to see if all of columns 2, 3, and 4 are updated .*/ BEGIN -- Audit OLD record. Insert into auditEmployeeData (Audit_log_type, Audit_emp_id, Audit_emp_bankAccountNumber, Audit_emp_salary, Audit_emp_SSN) SELECT 'old ', Del. emp_id, Del. emp_bankAccountNumber, Del. emp_salary, Del. emp_SSN FROM deleted del -- Audit NEW record. Insert into auditEmployeeData (Audit_log_type, Audit_emp_id, Audit_emp_bankAccountNumber, Audit_emp_salary, Audit_emp_SSN) SELECT 'new ', Ins. emp_id, Ins. emp_bankAccountNumber, Ins. emp_salary, Ins. emp_SSN FROM inserted ins END GO /* Inserting a new employee does not cause the UPDATE trigger to fire .*/ Insert into employeeData VALUES (101, 'USA-987-01 ', 23000, 'R-m53550m', n'mendel', n'roand', 32) GO /* Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced .*/ UPDATE employeeData SET emp_salary = 51000 WHERE emp_id = 101. GO SELECT * FROM auditEmployeeData GO /* Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced .*/ UPDATE employeeData SET emp_bankAccountNumber = '133146a0 ', emp_SSN = 'R-m53550m' WHERE emp_id = 101. GO SELECT * FROM auditEmployeeData GO

F. Use COLUMNS_UPDATED to test more than 8 columns
If you must test the update that affects columns other than the first eight columns in the table, you must use the UBSTRING function to test the proper bit returned by COLUMNS_UPDATED. The following test affects the update of columns 3rd, 5th, or 9th in the Northwind. dbo. MERS table.

The code is as follows:	Copy code
USE Northwind Drop trigger tr1 GO Create trigger tr1 ON MERS FOR UPDATE IF (SUBSTRING (COLUMNS_UPDATED (), 1, 1) = power (2, (3-1 )) + Power (2, (5-1 ))) AND (SUBSTRING (COLUMNS_UPDATED (), 2, 1) = power (2, (1-1 ))) ) PRINT 'columns 3, 5 and 9 updated' GO UPDATE MERS SET ContactName = ContactName, Address = Address, Country = Country GO