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

7.6.1 Firing Order (Order of Execution)

More than one trigger can be defined for each phase-event combination. The order in which they are executed (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.

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

7.6.2.1 Trigger Options

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

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.

7.6.2.2 OLD and NEW Context Variables

For DML triggers, the Firebird engine provides access to sets of OLD and NEW context variables. Each is an array 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, the OLD value 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 the OLD variables are invalid and will throw an exception

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

• In all AFTER trigger code, the NEW variables are read-only

7.6.3 Database Triggers

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

7.6.4 DDL Triggers

DDL triggers fire on specified metadata changes events in a specified phase. BEFORE triggers run before changes to system tables. AFTER triggers run after changes in 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.

7.6.4.1 Semantics

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

   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 some 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 triggers do not fire individual {CREATE | ALTER | DROP} {PROCEDURE | FUNCTION} triggers.

7.6.4.2 The DDL_TRIGGER Context Namespace

When a DDL trigger is running, the DDL_TRIGGER namespace is available for use with RDB$GET_CONTEXT. This namespace contains information on the currently firing trigger.

See also The DDL_TRIGGER Namespace in RDB$GET_CONTEXT in Chapter Built-in Scalar Functions.

7.6.5 Creating Triggers

For information on creating triggers, see CREATE TRIGGER, CREATE OR ALTER TRIGGER, RECREATE TRIGGER in Chapter Data Definition (DDL) Statements.

7.6.6 Modifying Triggers

For information on modifying triggers, see ALTER TRIGGER, CREATE OR ALTER TRIGGER, RECREATE TRIGGER in Chapter Data Definition (DDL) Statements.

7.6.7 Deleting a Trigger

For information on deleting triggers, see DROP TRIGGER in Chapter Data Definition (DDL) Statements.