Creates a trigger which is invoked by data changes in a named table or view.
where trigger-event is:
and alias-list is:
and trigger-action is:
A trigger is created on a table or view (table reference).
For a complete description of triggers, see the Mimer SQL Programmer's Manual, chapter 13, Triggers.
The trigger-name should follow the normal rules for naming database objects, see Naming Objects.
If trigger-name is specified in its unqualified form, the trigger will be created in the schema which has the same name as the current ident.
If trigger-name is specified in its fully qualified form (i.e. schema-name.trigger-name) the trigger will be created in the named schema (in this case, the current ident must be the creator of the specified schema).
The trigger-action will be executed when the data manipulation operation specified by trigger-event occurs on table-reference and any search-condition specified in the WHEN clause of the trigger-action evaluates to true.
If the trigger time is AFTER, the trigger will be invoked after the data manipulation operation specified by trigger-event has been performed on table-reference.
If the trigger time is BEFORE, the trigger will be invoked before the data manipulation operation specified by trigger-event has been performed on table-reference.
If the trigger time is INSTEAD OF, the trigger will be executed in place of the data manipulation operation specified by trigger-event, i.e. the data manipulation statement invoking the trigger will not actually change any data in table-reference. In this case the only data changes possible are those performed by the trigger-action.
In the alias-list, OLD TABLE and NEW TABLE are used to create two special temporary tables showing those rows of table-reference selected by the data manipulation statement invoking the trigger.
The temporary table created by using OLD TABLE shows the affected rows as they were before the data manipulation statement was executed.
The temporary table created by using NEW TABLE shows the affected rows in a state consistent with the data manipulation statement having been executed. In the case of an AFTER trigger, the same rows can be found in table-reference but for an INSTEAD OF trigger, the matching rows of table-reference will actually appear as they do in the OLD TABLE alias because the data manipulation statement is not actually executed.
The trigger and table-reference must belong to the same schema.
Two triggers with the same name cannot belong to the same schema.
If the trigger time is AFTER or BEFORE, then table-reference must be the name of a base table which is located in a databank with the TRANS or LOG option.
If the trigger time is INSTEAD OF, then table-reference must be the name of a view.
OLD TABLE and NEW TABLE may each be specified only once in the alias-list and the same table-alias-name must not appear twice in the same list.
OLD TABLE may not be specified if the trigger-event is INSERT.
NEW TABLE may not be specified if the trigger-event is DELETE.
If the trigger time is BEFORE, the REFERENCING keyword and alias-list must not be specified.
If the procedural-sql-statement of the trigger-action is a COMPOUND STATEMENT, it must be ATOMIC.
The creator of the trigger must hold the appropriate access rights, with grant option, for all operations performed in the trigger action.
The trigger-action must not contain a COMMIT or ROLLBACK statement.
If the trigger time 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.
The trigger-action is always executed in the transaction started for the data manipulation operation which caused the trigger to be invoked. Thus, if the data manipulation operation is subject to a rollback, all operations performed in the trigger-action will also be undone and an unhandled error occurring in the trigger-action will be treated like an error in the triggering data manipulation statement.
During the execution of the trigger-action, the effect of changes made in the transaction are visible.
The scope of the trigger-action is the optional WHEN clause and the procedural-sql-statement.
The tables specified by using OLD TABLE and NEW TABLE in the alias-list are temporary and are local to scope of the trigger-action. It is not possible to perform any data change operations on either table and the data contained in each will not otherwise change during the time it exists.
Data manipulation operations performed in the trigger-action may cause the trigger to be invoked recursively. Trigger execution in a recursive situation will proceed normally in every respect.
ExampleCREATE TRIGGER mimer_store_book.titles_after_insert AFTER INSERT ON mimer_store_book.titles REFERENCING NEW TABLE AS btl BEGIN ATOMIC ... ... END -- of trigger mimer_store_book.titles_after_insert
For more information, see the Mimer SQL Programmer's Manual, chapter 13, Triggers.
MIMER_EXTENSION Support for triggers is a Mimer SQL extension. SQL-99 EXTENDED Support for INSTEAD OF triggers is a Mimer SQL extension.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40