Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.
CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
sql_statement [...n]
}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (column)
[{AND | OR} UPDATE (column)]
[...n]
| IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}
The trigger actions specified in the Transact-SQL statements go into effect when the user action (DELETE, INSERT, or UPDATE) is attempted.
Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. The Transact-SQL statements in a trigger often include control-of-flow language. A few special tables are used in CREATE TRIGGER statements:
SELECT *
FROM deleted
Note The IF UPDATE (column) clause functions identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END block. For more information, see Control-of-Flow Language.
UPDATE(column) can be used anywhere inside the body of the trigger.
COLUMNS_UPDATED can be used anywhere inside the body of the trigger.
Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE); however, DRI does not provide cross-database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY keywords of ALTER TABLE and CREATE TABLE). If constraints exist on the trigger table, they are checked prior to trigger execution. If either PRIMARY KEY or FOREIGN KEY constraints are violated, the trigger is not executed (fired).
Note Whether SQL Server interprets an empty string as a single space or as a true empty string is controlled by the setting of sp_dbcmptlevel. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
CREATE TRIGGER must be the first statement in the batch and can apply to only one table.
A trigger is created only in the current database; however, a trigger can reference objects outside the current database.
If the trigger owner name is specified (to qualify the trigger), qualify the table name in the same way.
The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.
Any SET statement can be specified inside a trigger. The SET option chosen remains in effect during the execution of the trigger and then reverts to its former setting.
When a trigger fires, results are returned to the calling application, just as with stored procedures. To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.
A trigger cannot be created on a view.
A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger. Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.
The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.
These Transact-SQL statements are not allowed in a trigger:
| ALTER DATABASE | ALTER PROCEDURE | ALTER TABLE |
| ALTER TRIGGER | ALTER VIEW | CREATE DATABASE |
| CREATE DEFAULT | CREATE INDEX | CREATE PROCEDURE |
| CREATE RULE | CREATE SCHEMA | CREATE TABLE |
| CREATE TRIGGER | CREATE VIEW | DENY |
| DISK INIT | DISK RESIZE | DROP DATABASE |
| DROP DEFAULT | DROP INDEX | DROP PROCEDURE |
| DROP RULE | DROP TABLE | DROP TRIGGER |
| DROP VIEW | GRANT | LOAD DATABASE |
| LOAD LOG | RESTORE DATABASE | RESTORE LOG |
| REVOKE | RECONFIGURE | |
| TRUNCATE TABLE | UPDATE STATISTICS |
Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.
SQL Server allows multiple triggers to be created for each data modification event (DELETE, INSERT, or UPDATE). For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, then an additional update trigger is created. In earlier versions, only one trigger for each data modification event (INSERT, UPDATE, DELETE) was allowed for each table.
Note The default behavior for CREATE TRIGGER (with the compatibility level of 70) is to add additional triggers to existing triggers, if the trigger names differ. If trigger names are the same, SQL Server returns an error message. However, if the compatibility level is equal to or less than 65, any new triggers created with the CREATE TRIGGER statement replace any existing triggers of the same type, even if the trigger names are different. For more information, see sp_dbcmptlevel.
SQL Server also allows recursive invocation of triggers when the recursive triggers setting is enabled in sp_dboption.
Recursive triggers allow two types of recursion to occur:
With indirect recursion, an application updates table T1, which fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.
With direct recursion, the application updates table T1, which fires trigger TR1, updating table T1. Because table T1 has been updated, trigger TR1 fires again, and so on.
This example uses both indirect and direct trigger recursion. Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. In addition, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a given trigger contain rows corresponding only to the UPDATE statement that invoked the trigger.
Note The above behavior occurs only if the recursive triggers setting of sp_dboption is enabled. There is no defined order in which multiple triggers defined for a given event are executed. Each trigger should be self-contained.
If any of the triggers do a ROLLBACK TRANSACTION, regardless of the nesting level, no further triggers are executed.
Triggers can be nested up to 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.
SQL Server allows Transact-SQL stored procedures, triggers, and batches to refer to tables that do not exist at compile time. This ability is called deferred name resolution. However, if the Transact-SQL stored procedure, trigger, or batch refers to a table defined in the stored procedure or trigger, a warning is issued at creation time only if the compatibility level setting (set by executing sp_dbcmptlevel) is equal to 65. A warning is issued at compile time if a batch is used. An error message is returned at execution time if the table referenced does not exist. For more information, see Deferred Name Resolution and Compilation.
CREATE TRIGGER permission defaults to the table owner on which the trigger is defined, or to members of the db_owner and db_ddladmin fixed database roles, and is not transferable.
This example trigger prints a message to the client when anyone tries to add or change data in the titles table.
Note Message 50009 is a user-defined message in sysmessages. For information about creating user-defined messages, see sp_addmessage.
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
This example sends an e-mail message to a specified person (MaryM) when the titles table changes.
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
Because CHECK constraints can reference only the columns on which the column- or table-level constraint has been defined, any cross-table constraints (in this case, business rules) must be defined as triggers.
This example creates a trigger that, when an employee job level is inserted or updated, checks that the specified employee job level (job_lvls), on which salaries are based, is within the range defined for the job. To get the appropriate range, the jobs table must be referenced.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
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 should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
This example creates two triggers to illustrate deferred name resolution.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' 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'
This example creates two tables: an employeeData table and an auditEmployeeData table. The employeeData table, which holds sensitive employee payroll information, can be modified by members of the human resources department. If the employee’s social security number (SSN), yearly salary or bank account number is changed, an audit record is generated and inserted into the auditEmployeeData audit table.
By using the COLUMNS_UPDATED() function, it is possible to test quickly for any changes to these columns that contain sensitive employee information.
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 = 'auditEmployeeData')
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 AS
-- 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'Roland', 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
| ALTER TABLE | sp_depends |
| ALTER TRIGGER | sp_help |
| CREATE TABLE | sp_helptext |
| DROP TRIGGER | sp_rename |
| Using Identifiers | sp_spaceused |
| Programming Stored Procedures |