602SQL Documentation Index  
    UpTriggers Executing UPDATE Triggers with a Specified Column List­>

Trigger Description 

trigger_description ::= TRIGGER [scheme.]trigger_name [ BEFORE | AFTER ] action ON table_name 
    [ reference ] [ granularity ] [ WHEN ( condition ) ] statement
action ::= INSERT | DELETE | UPDATE [ OF [ rule ] column { , column }… ] 
reference ::= REFERENCING { OLD [ ROW ] [ AS ] old_row | NEW [ ROW ] [ AS ] new_row }…  
granularity ::= FOR EACH { ROW | STATEMENT }
condition ::= = | ANY

The trigger name must unique in an application context. Table name must designate a table existing in the same application and the columns must be the names of some columns of this table. The INSERT, DELETE or UPDATE specification sets what table operation executes the trigger. If there are column names specified after the UPDATE keyword, only those column changes will execute the trigger; if the column list is not specified, the trigger will be executed on any table change. See executing UPDATE triggers with specified column list.

The old_row and new_row identifiers allow you to refer to the contents of the database record before and after the action that executed the trigger in the condition and the statement. The notation old_row.column or new_row.column designates the old (new) column value. If the INSERT action is specified, old_row may not be set, if the DELETE action is specified, new_row may not be set, because these constructs are inaccessible in these cases. The old_row and new_row identifiers may not be the same.

The FOR EACH ROW specification sets the trigger to be executed for each row separately. The FOR EACH STATEMENT specification sets the trigger to be executed only once for the whole SQL statement that modifies more rows simultaneously. If none of these is specified, FOR EACH STATEMENT is taken. The new and old row specifications are allowed only when FOR EACH ROW is set. Transient trigger tables (temporary tables that contain the deleted or inserted records) are not yet implemented in 602SQL, therefore the FOR EACH STATEMENT specification cannot be used for reading old and new values in the modified rows. It's therefore recommended to use the FOR EACH ROW specification always in this 602SQL version.

The condition is evaluated first when a trigger is executed (it's possible to refer to the old and new column values in the condition), and if the condition is fulfilled the statement (single statement or statement block) is executed. No statement that opens or closes a transaction may be used in the condition or statement - otherwise an error occurs - sqlstate 2D000 (SQ_INVAL_TRANS_TERM).

The BEFORE trigger always sees the original data version in the table, the AFTER trigger sees the modified data version. Other clients learn about these changes (done by the SQL statement or by the triggers) after the transaction is executed.

Usage example:

this trigger guarantees, that the column name will be written in uppercase when inserting a record into the Companies table

TRIGGER UpperCase AFTER INSERT ON Companies
REFERENCING NEW ROW AS newrow
FOR EACH ROW
BEGIN
 SET newrow.name=UPPER(newrow.name);
END

See

    UpTriggers Executing UPDATE Triggers with a Specified Column List­>