<< Statement versus row triggers | The CREATE TYPE statement >>

Triggered-SQL-statement

<< Statement versus row triggers | The CREATE TYPE statement >>

Derby Reference Manual

Triggered-SQL-statement
The action defined by the trigger is called the triggered-SQL-statement (in

Syntax

above,

see the last line). It has the following limitations:

· It must not contain any dynamic parameters (?).
· It must not create, alter, or drop the table upon which the trigger is defined.
· It must not add an index to or remove an index from the table on which the trigger is

defined.

· It must not add a trigger to or drop a trigger from the table upon which the trigger is

defined.

· It must not commit or roll back the current transaction or change the isolation level.
· Before triggers cannot have INSERT, UPDATE or DELETE statements as their

action.

· Before triggers cannot call procedures that modify SQL data as their action.
· The NEW variable of a Before trigger cannot reference a generated column.

The triggered-SQL-statement can reference database objects other than the table upon
which the trigger is declared. If any of these database objects is dropped, the trigger is
invalidated. If the trigger cannot be successfully recompiled upon the next execution, the
invocation throws an exception and the statement that caused it to fire will be rolled back.

For more information on triggered-SQL-statements, see the Derby Developer's Guide.

Order of execution
When a database event occurs that fires a trigger, Derby performs actions in this order:

· It fires No Cascade Before triggers.
· It performs constraint checking (primary key, unique key, foreign key, check).
· It performs the insert, update, or delete.
· It fires After triggers.

When multiple triggers are defined for the same database event for the same table for
the same trigger time (before or after), triggers are fired in the order in which they were
created.

Examples

-- Statements and triggers:

CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
FOR EACH ROW MODE DB2SQL
values app.notifyEmail('Jerry', 'Table x is about to be updated');

CREATE TRIGGER FLIGHTSDELETE
AFTER DELETE ON FLIGHTS
REFERENCING OLD_TABLE AS DELETEDFLIGHTS
FOR EACH STATEMENT
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
(SELECT FLIGHT_ID FROM DELETEDFLIGHTS);

CREATE TRIGGER FLIGHTSDELETE3
AFTER DELETE ON FLIGHTS
REFERENCING OLD AS OLD
FOR EACH ROW
DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;

Note: You can find more examples in the Derby Developer's Guide.

Trigger recursion

The maximum trigger recursion depth is 16.

Related information
Special system functions that return information about the current time or current user are
evaluated when the trigger fires, not when it is created. Such functions include:

CURRENT_DATE function