SQL trigger syntax reference

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ 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 ]
    }
}

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

EncryptionSyscommentsThe table contains entries in 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, seeSp_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:

• DeletedAndInsertedIs a logical (concept) table. 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 exampleDeletedFor all values in the table, use:

  SELECT *    FROM deleted    

• If the compatibility level is 70, SQL server will not allow reference in the delete, insert, or update trigger.InsertedAndDeletedTableText,NtextOrImageColumn. InaccessibleInsertedAndDeletedTableText,NtextAndImageValue. To retrieve the new value in the insert or update triggerInsertedJoin the table with the original update table. When the compatibility level is 65 or lowerInsertedOrDeletedThe table allows null valuesText,NtextOrImageColumn. If these columns cannot be empty, a zero-length string is returned.

  When the compatibility level is 80 or higher, SQL Server allows update on tables or views through the instead of triggerText,NtextOrImageColumn.

N

It indicates that the trigger can contain placeholders of multiple Transact-SQL statements. For if update(Column)Statement, which can be updated repeatedly(Column)The clause contains multiple columns.

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 an independent update after the first operation.(Column)Clause. In the insert operation, if update returns true because these columns insert explicit or implicit (null) values.

DescriptionIf update(Column)The sub-statement function is equivalent to the IF, if... else, or while statement, and you can use the begin... end statement block. For more information, see control flow language.

 

You can use Update (Column).

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 returnVarbinaryBit mode, indicating which columns in the table are inserted or updated.

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 exampleT1Include columnsC1,C2,C3,C4AndC5. Assume that the tableT1There is an update trigger on, to check the columnC2, C3AndC4Whether updates exist. The value is 14. to check whether only columns existC2There is an update. The value is 2.

Comparison_operator

Is a comparison operator. Use equal sign (=) to checkUpdated_bitmaskWhether all columns specified in. Check with a greater than sign (>)Updated_bitmaskWhether any or some of the specified columns 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.

AvailableSp_settriggerorderSpecify the first and last executed after triggers for 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 feature set on the modified trigger is removed, andSp_settriggerorderReset the sorting value.

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:

Alter Database	Create Database	Disk init
Disk resize	DROP DATABASE	Load Database
Load log	Reconfigure	Restore database
Restore log

DescriptionBecause 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.

DescriptionIf 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

WhenSp_dboptionEnableRecursive triggersSQL Server also allows recursive calls to triggers.

Recursive triggers allow two types of recursion:

• Indirect Recursion

• Direct Recursion

When indirect recursion is used, the application updates the table.T1To stimulate the trigger.Tr1, This trigger updates the tableT2. In this case, the triggerT2Will be triggered and updatedT1.

When direct recursion is used, the application updates the table.T1To stimulate the trigger.Tr1, This trigger updates the tableT1. Because the tableT1Updated, triggerTr1And so on.

In the following example, both indirect trigger recursion and direct trigger recursion are used. Assume thatT1Two update triggers are defined inTr1AndTr2. TriggerTr1Recursively update a tableT1. Update statementTr1AndTr2Each execution is performed once. WhileTr1Will triggerTr1(Recursion) andTr2. Specify the trigger'sInsertedAndDeleted The table contains only the rows corresponding to the update statement of the wake-up trigger.

DescriptionOnly EnabledSp_dboptionOfRecursive triggersBefore the above behavior occurs. There is no definite execution sequence for multiple triggers defined for a given event. Each trigger should be self-contained.

 

DisableRecursive triggersYou can only disable direct recursion. To disable indirect recursion, useSp_configureSetNested triggersSet the 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, useSp_configureSetNested triggersSet the option to 0 (off ). Nested triggers are allowed by default. If the nested trigger is disabled, the recursive trigger is also disabled.Sp_dboptionOfRecursive triggersThe setting is irrelevant.

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 or trigger is referenced by a Transact-SQL stored procedure, trigger, or batch process, only when the compatibility level is set (by executingSp_dbcmptlevelIf it is set to 65, a warning will be issued during 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

The create trigger permission is granted to the table owner who defines the trigger by default,SysAdminFixed server role members andDb_ownerAndDb_ddladminFixed database role members, 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 triesTitlesWhen adding or changing data in the table, the following example shows a message to the client.

DescriptionMessage 50009 isSysmessagesUser-defined messages in. For more information about creating user-defined messages, see sp_addmessage.

 

USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = 'reminder' AND type = 'TR')DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATEAS RAISERROR (50009, 16, 10)GO

B. Use a trigger with a reminder email

WhenTitlesWhen the table is changed, the following example sends an email to the specified person (Marym ).

USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = 'reminder' AND type = 'TR')DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETEASEXEC 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, create a trigger when you insert or update an employee's work level (Job_lvls), The trigger checks whether the specified employee's work level (which determines the salary) is within the scope defined for the job. To obtain an appropriate range, you must referenceJobsTable.

USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = 'employee_insupd' AND type = 'TR')DROP TRIGGER employee_insupdGOCREATE TRIGGER employee_insupdON employeeFOR INSERT, UPDATEAS/* 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 smallintSELECT @min_lvl = min_lvl,@max_lvl = max_lvl,@emp_lvl = i.job_lvl,@job_id = i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_idJOIN jobs j ON j.job_id = i.job_idIF (@job_id = 1) and (@emp_lvl   10)BEGINRAISERROR ('Job id 1 expects the default level of 10.', 16, 1)ROLLBACK TRANSACTIONENDELSEIF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)BEGINRAISERROR ('The level for job_id:%d should be between %d and %d.',16, 1, @job_id, @min_lvl, @max_lvl)ROLLBACK TRANSACTIONEND

D. Use delay name resolution

In the following example, two triggers are created to describe the delay in name resolution.

USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = 'trig1' AND type = 'TR')DROP TRIGGER trig1GO-- Creating a trigger on a nonexistent table.CREATE TRIGGER trig1on authorsFOR INSERT, UPDATE, DELETEASSELECT a.au_lname, a.au_fname, x.infoFROM authors a INNER JOIN does_not_exist xON a.au_id = x.au_idGO-- Here is the statement to actually see the text of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments cON o.id = c.idWHERE o.type = 'TR' and o.name = 'trig1'-- Creating a trigger on an existing table, but with a nonexistent-- column.USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = 'trig2' AND type = 'TR')DROP TRIGGER trig2GOCREATE TRIGGER trig2ON authorsFOR INSERT, UPDATEASDECLARE @fax varchar(12)SELECT @fax = phoneFROM authorsGO-- Here is the statement to actually see the text of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments cON o.id = c.idWHERE o.type = 'TR' and o.name = 'trig2'

E. Use columns_updated

In the following example, create two tables: oneEmployeedataTable and oneAuditemployeedataTable. Members of the Human Resources Department can modifyEmployeedataTable, 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 insertedAuditemployeedataAudit 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.

USE pubsIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'employeeData')DROP TABLE employeeDataIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'auditEmployeeData')DROP TABLE auditEmployeeDataGOCREATE 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)GOCREATE 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())GOCREATE TRIGGER updEmployeeDataON employeeDataFOR 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_SSNFROM 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_SSNFROM inserted insENDGO/*Inserting a new employee does not cause the UPDATE trigger to fire.*/INSERT INTO employeeDataVALUES ( 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 employeeDataSET emp_salary = 51000WHERE emp_id = 101GOSELECT * FROM auditEmployeeDataGO/*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 employeeDataSET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'   WHERE emp_id = 101GOSELECT * FROM auditEmployeeDataGO

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. Impact of the next testNorthwind. DBO. CustomersUpdate of columns 3rd, 5th, or 9th in the table.

USE NorthwindDROP TRIGGER  tr1GOCREATE TRIGGER tr1 ON CustomersFOR UPDATE ASIF ( (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'GOUPDATE CustomersSET ContactName=ContactName,Address=Address,Country=CountryGO