CREATE TRIGGER statement [T-SQL]

Syntax

General use

CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR { INSERT, UPDATE, DELETE }
AS statement-list

Specifying additional conditions

CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE}
AS
[ IF UPDATE ( column-name )
[ { AND | OR } UPDATE ( column-name ) ] ... ]
  statement-list
[ IF UPDATE ( column-name )
[ { AND | OR} UPDATE ( column-name ) ] ... ]
  statement-list

Remarks

CREATE TRIGGER acquires an exclusive table lock on the table.

The rows deleted or inserted are held in two temporary tables. In the Transact-SQL form of triggers, they can be accessed using the table names "deleted", and "inserted", as in Adaptive Server Enterprise. In the Watcom SQL CREATE TRIGGER statement, these rows are referenced using the REFERENCING clause.

Trigger names must be unique in the database.

Transact-SQL triggers are executed AFTER the triggering statement has executed.

Since the ORDER clause is not supported when creating Transact-SQL triggers, the value of trigger_order is set to 1. The SYSTRIGGER system table has a unique index on: table_id, event, trigger_time, and trigger_order. For a particular event (insert, update, delete), statement-level triggers are always AFTER and trigger_order cannot be set, so there can be only one of each type per table, assuming any other triggers do not set an order other than 1.

Privileges

You must be the owner of the table, or have ALTER privilege on the table, or have ALTER ANY TABLE system privilege. Additionally, you must have the CREATE ANY TRIGGER or CREATE ANY OBJECT system privilege

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.
Transact-SQL
ROW triggers are not supported by Adaptive Server Enterprise. The SQL Anywhere Transact-SQL dialect does not support Transact-SQL INSTEAD OF triggers, though these are supported by Adaptive Server Enterprise.

If an owner is specified for trigger_name, it is ignored. SQL Anywhere triggers do not have owners.