Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Trigger Action


The trigger action, like the body of a routine, consists of a single procedural SQL statement. In addition, the execution of the SQL statement can be made conditional on the evaluation of a search condition.

The search condition is specified in the optional WHEN clause of the CREATE TRIGGER statement.

As for routines, it is recommended that a compound SQL statement always be used for the trigger action.

Note: The entire trigger action must be executed in a single atomic execution context, therefore if a compound SQL statement is used, it must be defined as ATOMIC, see The ATOMIC Compound SQL Statement.

The SQL statement(s) of the trigger action are always executed within the transaction started for the trigger event. The normal restrictions on the use of certain procedural SQL statements within a transaction apply.

In addition, because the trigger action must be atomic, a COMMIT or ROLLBACK statement cannot be executed within it.

The creator of the trigger must hold the appropriate access rights, with grant option, for all the operations performed within the trigger action. This is checked when the CREATE TRIGGER statement is executed.

If the trigger time specified for the trigger is BEFORE, the following restrictions apply to the trigger action:

If an exception is raised from the trigger action, it can be handled within the trigger by declaring a handler in the normal way for a compound SQL statement, see Declaring Exception Handlers.

If there is no handler declared in the trigger action to handle the exception, it will propagate to the environment executing the trigger event and will be dealt with appropriately there. The default behavior at that level will be to undo the effect of the trigger event and all the operations performed in the trigger action.

It is possible to explicitly raise an exception from within the trigger action, or from within an exception handler declared in it, by executing the SIGNAL statement.

Altered Table Rows

When the rows of the database table on which the trigger was created are examined from within the trigger action, they will always reflect the actual data manipulations performed by the trigger event and the trigger action.

In the case of an AFTER statement trigger, all rows inserted by the trigger event will be visible, all rows deleted by the trigger event will not be found and all rows updated by the trigger event will appear in their altered state.

In the case of an INSTEAD OF trigger, none of the data manipulations specified by the trigger event will seen when the table is examined because the trigger event does not actually perform any of its data change operations.

The rows of the old table and the new table will always show the changes that were specified by the trigger event, even if these changes were not actually performed on the database table (as is the case for INSTEAD OF triggers).

Recursion

Any data manipulation statements occurring in a trigger action will be executed in the normal way. It is, therefore, possible that the execution of a data manipulation statement in the trigger action may lead to the execution of another trigger or the recursive execution of the current trigger.

In either case, the execution context of the current trigger action is preserved and the newly invoked trigger executes in the normal way, in its own execution context, with appropriate versions of any old table and new table or old row and new row variables.

Example:

The following trigger is called recursively. An update statement causes the trigger to fire even when no rows are updated, hence the presence of a when clause to avoid an infinite recursive invocation.

 CREATE TRIGGER products_after_update
    AFTER UPDATE ON products
 REFERENCING NEW TABLE AS pdt
 WHEN ( EXISTS (SELECT * FROM pdt) )
 BEGIN ATOMIC
    UPDATE products
       SET product_search = product_search_code(product),
           product = (SELECT capitalize(TRIM(product))
                        FROM pdt
                        WHERE product_id = products.product_id)
      WHERE product_id IN (SELECT product_id FROM pdt
                             WHERE product_search <>
                                       product_search_code(products.product)
                             OR product <> capitalize(TRIM(products.product));
 END

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX