CREATE TRIGGER

---

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:

Usage

Embedded/Interactive/ODBC/JDBC.

Description

A trigger is created on a table or view (table reference).

For a complete description of triggers, see the Mimer SQL Programmer's Manual, 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.

Restrictions

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 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.

A trigger can be created on tables that have columns defined as LARGE OBJECT data type, with the restriction that it is not possible to refer to such columns in the new table in an instead of trigger.

Notes

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.

If the body of the trigger contains operations on tables located in a databank with work option, these operations will not be part of the atomic statement that constitute the trigger execution.

Example

 CREATE 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, Triggers.

Standard Compliance

Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.
SQL-2003	Features outside core	Feature T212, "Enhanced trigger capability" support for statement triggers.
	Mimer SQL extension	Support for INSTEAD OF as event time is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se