<< User-defined SQLExceptions | Programming Derby-style table functions >>

Performing referential actions

Derby Developer's Guide

A trigger action does have some limitations, though; for example, it cannot contain
dynamic parameters or alter the table on which the trigger is defined. See "TriggerAction"
in the Derby Reference Manual for details.

Performing referential actions

Derby provides referential actions. Examples in this section are included to illustrate how
to write triggers.

You can choose to use standard SQL referential integrity to obtain this functionality,
rather than writing triggers. See the Derby Reference Manual for more information on
referential integrity.

Accessing before and after rows

Many trigger actions need to access the values of the rows being changed.

Such trigger actions need to know one or both of the following:

· the "before" values of the rows being changed (their values before the database

event that caused the trigger to fire)

· the "after" values of the rows being changed (the values to which the database

event is setting them)

Derby provides transition variables and transition tables for a trigger action to access
these values. See "Referencing Old and New Values: The Referencing Clause" in the
Derby Reference Manual.

Examples of trigger actions

The following trigger action copies a row from the flights table into the flight_history table
whenever any row gets inserted into flights and adds the comment "inserted from trig1" in
the status column of the flight_history table.

CREATE TRIGGER trig1
AFTER UPDATE ON flights
REFERENCING OLD AS UPDATEDROW
FOR EACH ROW MODE DB2SQL
INSERT INTO flights_history
VALUES (UPDATEDROW.FLIGHT_ID, UPDATEDROW.SEGMENT_NUMBER,
UPDATEDROW.ORIG_AIRPORT, UPDATEDROW.DEPART_TIME,
UPDATED ROW.DEST_AIRPORT, UPDATEDROW.ARRIVE_TIME,
UPDATEDROW.MEAL, UPDATEDROW.FLYING_TIME, UPDATEDROW.MILES,
UPDATEDROW.AIRCRAFT,'INSERTED FROM trig1');

Triggers and exceptions

Exceptions raised by triggers have a statement severity; they roll back the statement that
caused the trigger to fire.

This rule applies to nested triggers (triggers that are fired by other triggers). If a trigger
action raises an exception (and it is not caught), the transaction on the current connection
is rolled back to the point before the triggering event. For example, suppose Trigger A
causes Trigger B to fire. If Trigger B throws an exception, the current connection is rolled
back to the point before the statement in Trigger A that caused Trigger B to fire. Trigger
A is then free to catch the exception thrown by Trigger B and continue with its work. If
Trigger A does not throw an exception, the statement that caused Trigger A, as well as
any work done in Trigger A, continues until the transaction in the current connection is
either committed or rolled back. However, if Trigger A does not catch the exception from