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

There are two types of triggers, row triggers and statement triggers. A row trigger is executed once for each row that is modified by a data manipulation operation. A statement trigger is invoked once for a data manipulation operation.

A row trigger is defined by specifying for each row in the trigger definition. If for each statement is specified or the for each clause is omitted, the trigger will be a statement trigger. Note that a statement trigger will always be invoked, regardless of if any rows are modified by the data manipulation operation. A row trigger will only be executed if any row is affected by the data manipulation.

The trigger time specifies when a trigger is executed. For a more detailed description of this, see Mimer SQL Programmer's Manual, Triggers.

It is possible to create multiple triggers for the same event and time and if so the triggers will be executed in the order they are created.

In a statement trigger it is possible to refer to temporary tables that contains the data affected by the data manipulation operation. These tables are named in the referencing clause and are commonly referred to as the old and new table. These tables are read only.

The old table shows the data as it were before the data manipulation operation and the new table shows the data after the statement has taken place.

The old table can be used if the trigger event is delete or update. The new table can be used if the trigger event is update or insert. The temporary tables will only be created if there is at least one statement trigger that references the old or new table.

In a row trigger it is possible to refer to the row being affected by the data manipulation operation. The old and new row variables can be seen as implicit parameters for the triggers. The old row variable is read only in all cases but the new row variable can be modified if the trigger time is before. The old and new row are defined as records where each field corresponds to a column in the table reference. To refer to individual fields a dot notation is used. See example below.

If the trigger time is INSTEAD OF the table reference must be a view. This is the only trigger time that can be specified for a trigger defined on a view.

If there is an INSTEAD OF trigger defined for a view this means that the data manipulation operation for a view will not be performed, but the trigger will be executed instead. In the trigger it is possible to do data manipulations on the tables on which the view is defined. Thus it is possible to make any view updatable by creating an instead of trigger. An instead of trigger may also use the old and new tables to access the data affected by the data manipulation operation that caused the trigger to be 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 alias-name must not appear twice in the list.

OLD ROW and NEW ROW may each be specified only once in the alias-list and the same alias-name must not appear twice in the list.

OLD ROW and NEW ROW may only be specified if FOR EACH ROW is specified.

OLD TABLE or OLD ROW may not be specified if the trigger-event is INSERT.

NEW TABLE or NEW ROW may not be specified if the trigger-event is DELETE.

AFTER and INSTEAD OF are currently not supported for row triggers.

If the trigger time is BEFORE and FOR EACH STATEMENT is specified, 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 data update (i.e. DELETE, INSERT and UPDATE statements are not permitted)

•a routine which possibly MODIFIES SQL DATA may 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 restrictions that it is not possible to refer to such columns in the new table in an instead of trigger and that it is not possible to modify such fields in the new row variable.

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. Situations like this can be handled using condition handlers. (See DECLARE HANDLER.)

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.

If the procedural-sql-statement in a trigger contains a compound statement, it is possible to declare condition handlers for handling errors that may occur in the trigger code. See DECLARE HANDLER.

Examples

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

 

CREATE TABLE versions(document_id int, version_date date);

 

CREATE TRIGGER set_version_date BEFORE UPDATE ON versions

REFERENCING NEW ROW AS new_version OLD ROW AS old_version FOR EACH ROW

   IF  old_version.version_date = new_version.version_date THEN

       SET new_version.version_date = current_date;

   END IF;

 

For more information, see the Mimer SQL Programmer's Manual, Triggers.

Standard Compliance