<< CREATE TRIGGER statement | Triggered-SQL-statement >>

Statement versus row triggers

<< CREATE TRIGGER statement | Triggered-SQL-statement >>

Derby Reference Manual

Many triggered-SQL-statements need to refer to data that is currently being changed by
the database event that caused them to fire. The triggered-SQL-statement might need to
refer to the new (post-change or "after") values.

Derby provides you with a number of ways to refer to data that is currently being changed
by the database event that caused the trigger to fire. Changed data can be referred
to in the triggered-SQL-statement using transition variables or transition tables. The
referencing clause allows you to provide a correlation name or alias for these transition
variables by specifying OLD/NEW AS correlation-Name .

For example, if you add the following clause to the trigger definition:

REFERENCING OLD AS DELETEDROW

you can then refer to this correlation name in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id

The OLD and NEW transition variables map to a java.sql.ResultSet with a single row.
Note: Only row triggers (see

Statement versus row triggers

) can use the transition

variables. INSERT row triggers cannot reference an OLD row. DELETE row triggers
cannot reference a NEW row.

For statement triggers, transition tables serve as a table identifier for the
triggered-SQL-statement or the trigger qualification. The referencing clause allows
you to provide a correlation name or alias for these transition tables by specifying
OLD_TABLE/NEW_TABLE AS correlation-Name

For example:

REFERENCING OLD_TABLE AS DeletedHotels

allows you to use that new identifier (DeletedHotels) in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id IN
(SELECT hotel_id FROM DeletedHotels)

The old and new transition tables map to a java.sql.ResultSet with cardinality equivalent
to the number of rows affected by the triggering event.
Note: Only statement triggers (see

Statement versus row triggers

) can use the transition

tables. INSERT statement triggers cannot reference an OLD table. DELETE statement
triggers cannot reference a NEW table.

The referencing clause can designate only one new correlation or identifier and only one
old correlation or identifier. Row triggers cannot designate an identifier for a transition
table and statement triggers cannot designate a correlation for transition variables.

Statement versus row triggers
You have the option to specify whether a trigger is a statement trigger or a row trigger. If
it is not specified in the CREATE TRIGGER statement via FOR EACH clause, then the
trigger is a statement trigger by default.

· statement triggers

A statement trigger fires once per triggering event and regardless of whether any
rows are modified by the insert, update, or delete event.

· row triggers

A row trigger fires once for each row affected by the triggering event. If no rows are
affected, the trigger does not fire.

Note: An update that sets a column value to the value that it originally contained (for
example, UPDATE T SET C = C) causes a row trigger to fire, even though the value of
the column is the same as it was prior to the triggering event.