CREATE TRIGGER statement [T-SQL]

Use this statement to create a new trigger in the database in a manner compatible with Adaptive Server Enterprise.

Syntax 1
CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR { INSERT, UPDATE, DELETE }
AS statement-list
Syntax 2
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

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 accessed using the REFERENCING clause.

Trigger names must be unique in the database.

Transact-SQL triggers are executed AFTER the triggering statement.

Permissions

Must have RESOURCE authority and have ALTER permissions on the table, or must have DBA authority.

CREATE TRIGGER locks all the rows on the table, and thus requires exclusive use of the table.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.