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
WHENclause of the
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
ROLLBACKstatement 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 TRIGGERstatement is executed.
If the trigger time specified for the trigger is
BEFORE, the following restrictions apply to the trigger action:
- the trigger action must not contain any SQL statement that performs data update (i.e.
UPDATEstatements are not permitted)
- a routine whose access clause is
MODIFIES SQL DATAmust not be invoked from within 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
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).
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.
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 Information Technology AB
Phone: +46 18 780 92 00