ASA SQL Reference
  SQL Statements

CREATE TRIGGER statement [SQL Remote]

Description 

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.

Usage 

Anywhere.

Permissions 

Must have RESOURCE authority and have ALTER permissions on the table, or must have DBA authority. CREATE TRIGGER puts a table lock on the table and thus requires exclusive use of the table.

Side effects 

Automatic commit.

See also 

UPDATE statement

Description 

The CREATE TRIGGER statement creates a trigger associated with a table in the database and stores the trigger in the database.

BEFORE UPDATE triggers fire any time an update occurs on a row, regardless of whether or not the new value differs from the old value. AFTER UPDATE triggers will fire only if the new value is different from the old value.

Row and statement-level triggers 

The trigger is declared as either a row-level trigger, in which case it executes before or after each row is modified, or as a statement-level trigger, in which case it executes after the entire triggering statement is completed.

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.

To declare a trigger as a row-level trigger, use the FOR EACH ROW clause. To declare a trigger as a statement-level trigger, you can either use a FOR EACH STATEMENT clause or omit the FOR EACH clause. For clarity, it is recommended that you enter the FOR EACH STATEMENT clause if declaring a statement-level trigger.

Order of firing 

Triggers of the same type (insert, update, or delete) that fire at the same time (before, after, or resolve) can use the ORDER clause to determine the order that the triggers are fired.

Referencing deleted and inserted values 

The REFERENCING OLD and REFERENCING NEW clauses allow you to refer to the deleted and inserted rows. For the purposes of this clause, an UPDATE is treated as a delete followed by an insert.

The REFERENCING REMOTE clause is for use with SQL Remote. It allows you to refer to the values in the VERIFY clause of an UPDATE statement. It should be used only with RESOLVE UPDATE or RESOLVE UPDATE OF column-list triggers.

The meaning of REFERENCING OLD and REFERENCING NEW differs, depending on whether the trigger is a row-level or a statement-level trigger. For row-level triggers, the REFERENCING OLD clause allows you to refer to the values in a row prior to an update or delete, and the REFERENCING NEW clause allows you to refer to the inserted or updated values. The OLD and NEW rows can be referenced in BEFORE and AFTER triggers. The REFERENCING NEW clause allows you to modify the new row in a BEFORE trigger before the insert or update operation takes place.

For statement-level triggers, the REFERENCING OLD and REFERENCING NEW clauses refer to declared temporary tables holding the old and new values of the rows. The default names for these tables are deleted and inserted.

The WHEN clause causes the trigger to fire only for rows where the search-condition evaluates to true.

Updating values with the same value 

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.

Example 

• When a new department head is appointed, update the manager_id column for employees in that department.

  CREATE TRIGGER
  tr_manager BEFORE UPDATE OF dept_head_id ON department
  REFERENCING OLD AS old_dept
  NEW AS new_dept
  FOR EACH ROW
  BEGIN
     UPDATE employee
     SET employee.manager_id=new_dept.dept_head_id
     WHERE employee.dept_id=old_dept.dept_id
  END