User's Manual

Chapter 17. Command Reference for Adaptive Server Anywhere
CREATE TRIGGER statement
Function Use this statement to create a new trigger in the database. One form of
trigger is designed specifically for use by SQL Remote.
Syntax CREATE TRIGGER trigger-name trigger-time
trigger-event, . . .
[ ORDER integer ] ON table-name
[ REFERENCING [ OLD AS old-name ]
[ NEW AS new-name ] ]
[ REMOTE AS remote-name ] ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN ( search-condition ) ]
[ IF UPDATE ( column-name ) THEN
[ { AND | OR } UPDATE ( column-name ) ] . . . ]
compound-statement
[ ELSEIF UPDATE ( column-name ) THEN
[ { AND | OR } UPDATE ( column-name ) ] . . .
compound-statement
END IF ] ]
trigger-time:
BEFORE | AFTER | RESOLVE
trigger-event:
DELETE | INSERT | UPDATE
| UPDATE OF column-name [, column-name, .. . ]
Parameters trigger-time Row-level triggers can be defined to execute BEFORE or
AFTER the insert, update, or delete. Statement-level triggers execute
AFTER the statement. The RESOLVE trigger time is for use with
SQL Remote: it fires before row-level UPDATE or UPDATE OF
column-lists only.
BEFORE UPDATE triggers fire any time an UPDATE occurs on a row,
whether or not the new value differs from the old value. AFTER UPDATE
triggers fire only if the new value is different from the old value.
Trigger events Triggers can be fired by one or more of the following
events:
♦ DELETE Invoked whenever a row of the associated table is deleted.
♦ INSERT Invoked whenever a new row is inserted into the table
associated with the trigger.
♦ UPDATE Invoked whenever a row of the associated table is updated.
♦ UPDATE OF column-list Invoked whenever a row of the associated
table is updated and a column in the column-list is modified.
359