FirebirdSQL logo

Triggers

A trigger is another form of executable code that is stored in the metadata of the database for execution by the server.A trigger cannot be called directly.It is called automatically (“fired”) when data-changing events involving one particular table or view occur, or on a specific database or DDL event.

A trigger applies to exactly one table or view or database event, and only one phase in an event (BEFORE or AFTER the event).A single DML trigger might be written to fire only when one specific data-changing event occurs (INSERT, UPDATE or DELETE), or it might be written to apply to more than one of those.

A DML trigger is executed in the context of the transaction in which the data-changing DML statement is running.For triggers that respond to database events, the rule is different: for DDL triggers and transaction triggers, the trigger runs in the same transaction that executed the DDL, for other types, a new default transaction is started.

Firing Order (Order of Execution)

More than one trigger can be defined for each phase-event combination.The order in which they are executed — also known as “firing order” — can be specified explicitly with the optional POSITION argument in the trigger definition.You have 32,767 numbers to choose from.Triggers with the lowest position numbers fire first.

If a POSITION clause is omitted, or if several matching event-phase triggers have the same position number, then the triggers will fire in alphabetical order.

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.