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:
- the trigger action must not contain any SQL statement that performs an update (i.e. DELETE, INSERT and UPDATE statements are not permitted)
- a routine whose access clause is MODIFIES SQL DATA must 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 SIGNAL statement.
New and Old Table Aliases
It is possible, by using the optional REFERENCING clause in the CREATE TRIGGER statement, to specify the existence of two special tables called the old table alias and the new table alias.
The name of each table is specified separately in the REFERENCING clause and they must not have the same name. Both tables are accessible from within the trigger action.
Each specified table is created just prior to the execution of the trigger and each is local to the entire scope of the trigger action, including the optional WHEN clause.
Note: A REFERENCING clause is not permitted in a CREATE TRIGGER statement which has BEFORE specified for the trigger time.
The old table alias and new table alias contain copies of those database table rows affected by the data manipulation operation that caused the execution of the trigger (i.e. the trigger event).
In the case of a trigger executing AFTER the trigger event, the alias tables show the database table rows actually affected by the trigger event.
In the case of a trigger executing INSTEAD OF the trigger event, the alias tables show the database table rows that would have been affected by the trigger event had its data manipulations actually been performed.
The old table alias shows each affected database table row in the state it was in before normal execution of the trigger event.
The new table alias shows how each affected table row appears (or would appear, in the case of INSTEAD OF triggers) after normal execution of the trigger event.
If the trigger event was an INSERT, the old table alias cannot be specified. If the trigger event was a DELETE, the new table alias cannot be specified.
The rows of the alias tables are created before the trigger executes and do not change during execution of the trigger.
If the trigger action contains data manipulation statements that attempt to alter the alias tables, an error will be raised when the CREATE TRIGGER statement is executed.
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 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 alias and the new table alias 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 newly-invoked trigger executes in the normal way, in its own execution context, with appropriate versions of any old table alias and new table alias, see New and Old Table Aliases.
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
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40