ASA SQL Reference
  SQL Statements

CREATE TRIGGER statement [T-SQL]

Description 

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

Usage 

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 

CREATE TRIGGER statement

Standards and compatibility 

• SQL/92    Transact-SQL extension.

• SQL/92    Transact-SQL extension.

• Sybase    Anywhere supports a subset of the Adaptive Server Enterprise syntax.