CREATE TRIGGER Statement

Creates a trigger, a special kind of stored procedure that is executed automatically when a user attempts the specified data-modification statement on the specified table. Triggers are often used for enforcing business rules and data integrity. Referential integrity can be defined by using FOREIGN KEY constraints with the CREATE TABLE statement. If constraints exist on the "trigger table," they are checked prior to the trigger execution. If constraints are violated, the trigger is not run.

Triggers have been commonly used in earlier versions of SQL Server to enforce complex business rules and referential integrity (integrity rules about relationships between the primary and foreign keys of tables or views). SQL Server 6.0 provides declarative referential integrity through the CREATE TABLE statement. Triggers are useful to ensure appropriate actions when cascading deletions or updates need to occur.

In performance terms, trigger overhead is usually very low. The time involved in running a trigger is spent mostly in referencing other tables, which may be either in memory or on disk.

Syntax

CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE, DELETE}
[WITH ENCRYPTION]
AS sql_statements

Or, using the IF UPDATE clause:

CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE}
[WITH ENCRYPTION]
AS
IF UPDATE (column_name)
[{AND | OR} UPDATE (column_name)...] sql_statements

where

trigger_name

Is the name of the trigger. A trigger name must conform to the rules for identifiers and must be unique within the database.

table_name

Specifies the table on which the trigger will be executed; sometimes called the trigger table.

INSERT, UPDATE, DELETE

Are keywords that specify which data modification statements, when attempted against this table, will activate the trigger. Any combination (in any order) of these are allowed in the trigger definition.

ENCRYPTION

Encrypts the syscomments entries that contain the text of the CREATE TRIGGER statement.

Important When a database is upgraded, syscomments entries are required in order to re-create triggers. Use encryption only when absolutely necessary; never delete entries from syscomments.

AS sql_statements

Specify trigger conditions and actions. Trigger conditions specify additional criteria that determine whether the attempted INSERT, DELETE, or UPDATE statements will cause the trigger action(s) to be carried out.

The trigger actions specified in the SQL statements go into effect when the user action (UPDATE, INSERT, or DELETE) is attempted. If multiple trigger actions are specified, they are grouped within a BEGIN...END block.

Triggers can include any number and kind of SQL statements but should not include the SELECT statement. A trigger is meant to check or change data based on a data modification statement; it should not return data to the user. The SQL statements in a trigger often include control-of-flow language. A few special tables are used in CREATE TRIGGER statements:

• Deleted and inserted are logical (conceptual) tables. They are structurally like the table on which the trigger is defined ¾ that is, the table on which the user action is attempted ¾ and hold the old values or new values of the rows that would be changed by the user action. For details, see "Inserted and Deleted Tables," later in this topic.

The following SQL statements are not allowed in a trigger:

• All CREATE statements (DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, and VIEW)
• All DROP statements
• ALTER TABLE and ALTER DATABASE
• TRUNCATE TABLE
• GRANT and REVOKE
• UPDATE STATISTICS
• RECONFIGURE
• LOAD DATABASE and LOAD TRANSACTION
• All DISK statements
• SELECT INTO (because it creates a table)

IF UPDATE

Tests for an INSERT or UPDATE action to a specified column. It is not used with DELETE. More than one column can be specified. Because you specify the table name in the ON clause, do not use the table name in front of the column name with IF UPDATE.

Remarks

In earlier releases of SQL Server, triggers were commonly used to enforce referential integrity (rules about the relationships between the primary and foreign keys of tables). SQL Server 6.0 provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE); however, DRI does not provide cross-database referential integrity in this release.

When creating a trigger, it must be the first statement in the batch and can apply to only one table. You can create a trigger only in the current database; however, a trigger can reference objects outside the current database. If you use an owner name to qualify a trigger, qualify the table name the same way. A table can have a maximum of three different triggers ¾ one each for INSERT, UPDATE, and DELETE. However, the same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

If a trigger is defined for an operation (INSERT, UPDATE, or DELETE) that already has a trigger association, the existing trigger is replaced. No warning message is given before the replacement occurs. When you drop a table, all triggers associated with it are also dropped.

Triggers can be nested 16 levels deep. 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 will be exceeded and the trigger will be canceled. If nested triggers are not desired, use the sp_configure System Stored Procedure to set the nested triggers option to 0 (off). The default configuration allows nested triggers.

A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger is activated only once.