FirebirdSQL logo

OLD and NEW Context Variables

For DML triggers, the Firebird engine provides access to sets of OLD and NEW context variables (or, “records”).Each is a record of the values of the entire row: one for the values as they are before the data-changing event (the BEFORE phase) and one for the values as they will be after the event (the AFTER phase).They are referenced in statements using the form NEW.column_name and OLD.column_name, respectively.The column_name can be any column in the table’s definition, not just those that are being updated.

The NEW and OLD variables are subject to some rules:

  • In all triggers, OLD is read-only

  • In BEFORE UPDATE and BEFORE INSERT code, the NEW value is read/write, unless it is a COMPUTED BY column

  • In INSERT triggers, references to OLD are invalid and will throw an exception

  • In DELETE triggers, references to NEW are invalid and will throw an exception

  • In all AFTER trigger code, NEW is read-only

Database Triggers

A trigger associated with a database or transaction event can be defined for the following events:

Connecting to a database

ON CONNECT

Before the trigger is executed, a transaction is automatically started with the default isolation level (snapshot (concurrency), write, wait)

Disconnecting from a database

ON DISCONNECT

Before the trigger is executed, a transaction is automatically started with the default isolation level (snapshot (concurrency), write, wait)

When a transaction is started

ON TRANSACTION START

The trigger is executed in the transaction context of the started transaction (immediately after start)

When a transaction is committed

ON TRANSACTION COMMIT

The trigger is executed in the transaction context of the committing transaction (immediately before commit)

When a transaction is cancelled

ON TRANSACTION ROLLBACK

The trigger is executed in the transaction context of the rolling back transaction (immediately before roll back)

DDL Triggers

DDL triggers fire on specified metadata change events in a specified phase.BEFORE triggers run before changes to system tables.AFTER triggers run after changes to system tables.

DDL triggers are a specific type of database trigger, so most rules for and semantics of database triggers also apply for DDL triggers.

Semantics

  1. BEFORE triggers are fired before changes to the system tables.AFTER triggers are fired after system table changes.

    Important
    Important Rule

    The event type [BEFORE | AFTER] of a DDL trigger cannot be changed.

  2. When a DDL statement fires a trigger that raises an exception (BEFORE or AFTER, intentionally or unintentionally) the statement will not be committed.That is, exceptions can be used to ensure that a DDL operation will fail if the conditions are not precisely as intended.

  3. DDL trigger actions are executed only when committing the transaction in which the affected DDL command runs.Never overlook the fact that what is possible to do in an AFTER trigger is exactly what is possible to do after a DDL command without autocommit.You cannot, for example, create a table and then use it in the trigger.

  4. With “CREATE OR ALTER” statements, a trigger is fired one time at the CREATE event or the ALTER event, according to the previous existence of the object.With RECREATE statements, a trigger is fired for the DROP event if the object exists, and for the CREATE event.

  5. ALTER and DROP events are generally not fired when the object name does not exist.For the exception, see point 6.

  6. The exception to rule 5 is that BEFORE ALTER/DROP USER triggers fire even when the username does not exist.This is because, underneath, these commands perform DML on the security database, and the verification is not done before the command on it is run.This is likely to be different with embedded users, so do not write code that depends on this.

  7. If an exception is raised after the DDL command starts its execution and before AFTER triggers are fired, AFTER triggers will not be fired.

  8. Packaged procedures and functions do not fire individual {CREATE | ALTER | DROP} {PROCEDURE | FUNCTION} triggers.