1.1.1

Changed data can be referenced in the triggered-sql-statement using transition variables. The referencing clause
enables you to provide a correlation name or alias for these transition variables by specifying REFERENCING
OLD AS correlation-name or REFERENCING 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 the 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 can use transition variables. INSERT row triggers cannot reference an OLD row,
and DELETE row triggers cannot reference a NEW row.
The ReferencingClause can designate only one new correlation or identifier and only one old correlation or
identifier.
A row trigger fires once for each row affected by the triggering event. If no rows are affected, the trigger does
not fire.
triggered-sql-statement
The action defined by the trigger is called the triggered-sql-statement. This statement 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 triggered-sql-statement can reference database objects other than the table where the trigger is declared. If
any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be recompiled on the
next execution, the invocation throws an exception and the statement that caused the trigger to fire is rolled back.
Example
This trigger is an after trigger on created dom.flights. For every UPDATE event made on
dom.flights, the trigger inserts a row in dom.flights_history:
CREATE TRIGGER trig1
AFTER UPDATE ON dom.flights REFERENCING OLD AS UPDATEDROW
FOR EACH ROW MODE DB2SQL
INSERT INTO dom.flights_history
VALUES(UPDATEDROW.FLIGHT_ID, UPDATEDROW.AIRCRAFT, 'INSERTED
FROM trig1');
The following trigger fires after a DELETE event:
CREATE TRIGGER trig2
AFTER DELETE ON flights
REFERENCING OLD AS OLD
FOR EACH ROW
DELETE FROM flightavailability WHERE FLIGHT_ID =
OLD.FLIGHT_ID;
vFabric SQLFire User's Guide508
vFabric SQLFire Reference