SQL-J Language Reference
Page 17 of 121

CREATE TRIGGER statement

A trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table.

Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions.

You can define any number of triggers for a single table, including multiple triggers on the same table for the same event.

You can create a trigger in any schema except SYS. The trigger need not reside in the same schema as the table on which it is defined.

You cannot create triggers on any table in the SYS schema.

Syntax

CREATE TRIGGER TriggerName
{ BEFORE | AFTER }
{ INSERT | DELETE | UPDATE [ OF ColumnName [, ColumnName]* ]
ON TableName
[ ReferencingClause ]
[ FOR EACH { ROW | STATEMENT } ]
TriggerAction

ReferencingClause

REFERENCING
{
    { OLD | NEW } [ ROW ] [ AS ] CorrelationName |
    { OLD | NEW } TABLE [ AS ] Identifier
}

Before or After: When Triggers Fire

Triggers are either before or after triggers (in Syntax above, see the second line):

  • Before triggers fire before the statement's changes are applied and before any constraints have been applied. Before triggers can be either row or statement triggers (see Statement vs. Row Triggers).
  • After triggers fire after all constraints have been satisfied and after the changes have been applied to the target table. After triggers can be either row or statement triggers (see Statement vs. Row Triggers).

Insert, Delete, or Update: What Causes the Trigger to Fire

A trigger is fired by one of the following database events, depending on how you define it (in Syntax above, see the third line):

  • INSERT
  • UPDATE
  • DELETE

You can define any number of triggers for a given event on a given table. For update, you can specify columns.

Referencing Old and New Values: The Referencing Clause

Many trigger actions need to refer to data that is currently being changed by the database event that caused them to fire. The trigger action may need to refer to the old (pre-change, or "before") values or the new (post-change or "after") values.

Cloudscape 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. The easiest way to refer to the changed data in the trigger action is use the transition variables or transition tables.

For row triggers, the transition variables NEW and OLD refer to the after and before image of the changes made to a row being modified by the current event. For example, you can refer to the transition variable "OLD" in the trigger action:

DELETE FROM HotelAvailability WHERE hotel_id = OLD.hotel_id

The referencing clause allows you to provide a correlation name or alias for these transition variables by specifying OLD/NEW AS correlationName if you do not wish to use the default variable names.

For example, if you add the following clause to the trigger definition (the word "row" is optional):

REFERENCING OLD ROW AS DELETEDROW

you can then refer to this correlation name in the trigger action:

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 vs. 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 trigger action or the trigger qualification. The default identifier for transition tables are NEW and OLD for the after and before row sets, respectively; you must specify the word TABLE after the keyword NEW or OLD.

For example, you can refer to the transition table "OLD" in the trigger action:

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

You can supply an alternate identifier to be used in place of the default identifiers by adding a referencing clause that specifies OLD/NEW TABLE AS identifier. For example:

REFERENCING OLD TABLE AS DeletedHotels

allows you to use that new identifier (DeletedHotels) in the trigger action:

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

NOTE: There are other ways to reference old and new values; see Trigger Execution Context and Transition Row VTIs.

Statement vs. Row Triggers

By default, a trigger is a statement trigger unless you specify that it is a row trigger (in Syntax above, see the sixth line):

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

TriggerAction

The action defined by the trigger is called the trigger action (in Syntax above, see the last line). It can be any valid SQL-J statement, including one with Java method calls. 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.
  • It must not execute a stored prepared statement with the USING clause.
  • If it is a before trigger, it must not perform an insert, update, or delete on the table on which it was defined.
  • It does not issue a SET CONSTRAINTS statement or SET TRIGGERS statement on the table on which the trigger is defined.

For more information on trigger actions, see Programming Trigger Actions in the Cloudscape Developer's Guide.

Order of Execution

When a database event occurs that fires a trigger, Cloudscape performs actions in this order:

  • It fires 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.

Trigger Execution Context

You can access information about a trigger's context from a new structure, COM.cloudscape.database.TriggerExecutionContext. You obtain it by calling COM.cloudscape.database.Factory.getTriggerExecutionContext() from within a TriggerAction (or from within a Java method called by a TriggerAction). For example, within a Java method (executed by a TriggerAction):

TriggerExecutionContext context =
    COM.cloudscape.database.Factory.getTriggerExecutionContext();
ResultSet oldrow = context.getOldRow();

Within an SQL statement:

CREATE TRIGGER . . .
DELETE FROM Hotels WHERE city_id = Factory.getTriggerExecutionContext().
    getOldRow().getInt("CITY_ID");

In addition to access to before and after rows, the TriggerExecutionContext provides the following information:

  • The columns changed by the triggering event.
  • The type of event that fired the trigger.
  • The SQL text of the triggering database event (for example, the text of the INSERT statement that caused the trigger to fire). (When this trigger is fired by the replay of a statement during a refresh, the statement text is null.)
  • The "before" image of the row being changed.
  • The "after" image of the row being changed.
  • The target table unique identifier.
  • The name of the target table.

For complete information on how to use this object, see the Javadoc for COM.cloudscape.database.TriggerExecutionContext.

Transition Row VTIs

The class COM.cloudscape.vti.TriggerOldTransitionRows is a VTI wrapper around a TriggerExecutionContext.getOldRowSet(). Selecting an instantiation of this VTI is the equivalent of the OLD TABLE transition table. The only difference is that this VTI is accessible from within an SQL-J statement executed by a Java method called by the trigger action; the OLD transition table is accessible only by the trigger action's SQL.

For example, the following two trigger actions are identical:

DELETE FROM HotelAvailability WHERE hotel_id IN 
    (SELECT hotel_id FROM OLD)
DELETE FROM HotelAvailability WHERE hotel_id IN
    (SELECT hotel_id FROM NEW TriggerOldTransitionRows() AS DeletedHotels)

You may want to call a Java method in the trigger action to perform some set of actions. In the body of this method, you would not be able to use the OLD or NEW transition tables, so you would have to use the VTI instead.

For example:

-- TriggerActions is a class alias
CREATE TRIGGER ShowNewBookings
AFTER INSERT
ON HotelBookings
FOR EACH STATEMENT
CALL TriggerActions.showNewBookings(getCurrentConnection())

public static void showNewBookings(Connection conn) throws SQLException 
{
    Statement s = conn.createStatement();
    Util.println("New hotel bookings:");
    ResultSet rs = s.executeQuery(
        "SELECT * FROM new TriggerNewTransitionRows() AS EQ");
    while (rs.next()) {
        Util.println(rs.getInt(1) + " " + 
             rs.getDate(3) + " " + rs.getDate(4));
    }
    rs.close();
    s.close();
}

The class COM.cloudscape.vti.TriggerNewTransitionRows is a VTI wrapper around a TriggerExecutionContext.getNewRowSet(). Selecting from this VTI is the equivalent of the NEW TABLE transition table. The only difference is that this VTI is accessible from within an SQL-J statement executed by a Java method called by the trigger action; the NEW transition table is accessible only by the trigger action's SQL.

For examples of using the VTIs within Java methods called by the trigger action, see Programming Trigger Actions in the Cloudscape Developer's Guide.

CREATE TRIGGER Examples

-- A statement and before trigger,
-- uses a REFERENCING clause to give an alternate
-- name to a transition table.
-- When a hotel is deleted, delete related rows in
-- the HOtelAvailability table.
CREATE TRIGGER HotelsCascadingDelete
BEFORE DELETE
ON Hotels
REFERENCING OLD Table AS DeletedHotels
FOR EACH STATEMENT
DELETE FROM HotelAvailability WHERE hotel_id IN
(SELECT hotel_id FROM DeletedHotels)

-- a statement and before trigger that uses special VTI
-- Before deleting a row in the hotels table,
-- delete related rows in the HotelAvailability table,
-- which are in a foreign key relationship with the Hotels
-- table
CREATE TRIGGER HotelsCascadingDelete1
BEFORE DELETE
ON Hotels
FOR EACH STATEMENT
DELETE FROM HotelAvailability WHERE hotel_id IN
(SELECT hotel_id FROM NEW TriggerOldTransitionRows() AS DeletedHotels)

-- use a transition variable in a row trigger
CREATE TRIGGER HotelsCascadingDelete3
BEFORE DELETE
ON Hotels
FOR EACH ROW
DELETE FROM HotelAvailability WHERE hotel_id = OLD.hotel_id

-- For each row being deleted,
-- call a Java method. This method deletes related
-- rows in the HotelBookings, unless the bookings are
-- for a future date. In that case, it changes the booking
-- to a new hotel (one that isn't being deleted
)
CREATE TRIGGER HotelsCascadingDelete2
BEFORE DELETE
ON Hotels
FOR EACH ROW
CALL TriggerActions.deleteOrRedoHotelBooking(
    getCurrentConnection(), CURRENT_JDATE())

-- automatically update the column that records
-- the return value of a method call

CREATE TRIGGER pseudoMethodIndexInsert
AFTER
INSERT
ON CustomizedTours
FOR EACH STATEMENT
UPDATE CustomizedTours SET getTotalCost = customized_tour.getTotalCost()
WHERE group_id IN (SELECT group_id FROM new     TriggerNewTransitionRows() AS NewRows)

-- perform some actions if only specific columns
-- are modified
CREATE TRIGGER hotelsAltered
AFTER UPDATE OF normal_rate, high_season_rate
ON Hotels
FOR EACH STATEMENT
    CALL TriggerActions.hotelChanged(getCurrentConnection())

NOTE: You can find more examples in Programming Trigger Actions in the Cloudscape Developer's Guide.

Trigger Recursion

It is possible for one trigger to cause another trigger to fire, and thus it is possible for triggers to recurse infinitely. Cloudscape supplies the property cloudscape.language.triggerMaximumRecursionLevel, which specifies the maximum recursion level, to allow you to limit trigger recursion. The default value of this property is 16. For more information, see cloudscape.language.triggerMaximumRecursionLevel in Tuning Cloudscape.

Special Functions and Triggers

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:

Cloudscape synchronization and Triggers

In a Cloudscape synchronization triggers are not published by default. For information, see the Cloudscape Synchronization Guide.

Dependency System

The trigger action may 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.