FirebirdSQL logo

DML Triggers

DML triggers are those that fire when a DML operation changes the state of data: updating rows in tables, inserting new rows or deleting rows.They can be defined for both tables and views.

Trigger Options

Six base options are available for the event-phase combination for tables and views:

Before a new row is inserted

BEFORE INSERT

After a new row is inserted

AFTER INSERT

Before a row is updated

BEFORE UPDATE

After a row is updated

AFTER UPDATE

Before a row is deleted

BEFORE DELETE

After a row is deleted

AFTER DELETE

These base forms are for creating single phase/single-event triggers.Firebird also supports forms for creating triggers for one phase and multiple-events, BEFORE INSERT OR UPDATE OR DELETE, for example, or AFTER UPDATE OR DELETE: the combinations are your choice.

Note

“Multi-phase” triggers, such as BEFORE OR AFTER …​, are not possible.

The Boolean context variables INSERTING, UPDATING and DELETING can be used in the body of a trigger to determine the type of event that fired the trigger.

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)