One trigger applies to exactly one table or view and only one phase in an event (BEFORE or AFTER the event). A single 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 some of them, a default transaction is started.

    7.5.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.5.2. DML Triggers

    DML triggers are those that fire when a DML operation changes the state of data: modifies rows in tables, inserts new rows or deletes 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:

    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 all AFTER trigger code, the NEW variables are read-only

    7.5.3. Database Triggers

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

    7.5.4. Creating Triggers

    Syntax

    The header must contain a name for the trigger that is unique among trigger names. It must include the event or events that will fire the trigger. Also, for a DML trigger it is mandatory to specify the event phase and the name of the table or view that is to “own” the trigger.

    The body of the trigger can be headed by the declarations of local variables and cursors, if any. Within the enclosing main BEGIN…​END wrapper will be one or more blocks of PSQL statements, which may be empty.

    For more information about creating triggers

    See in Chapter 5, Data Definition (DDL) Statements.

    7.5.5. Modifying Triggers

    Altering the status, phase, table or view event(s), firing position and code in the body of a DML trigger are all possible. However, you cannot modify a DML trigger to convert it to a database trigger, nor vice versa. Any element not specified is left unchanged by ALTER TRIGGER. The alternative statements CREATE OR ALTER TRIGGER and RECREATE TRIGGER will replace the original trigger definition entirely.

    Syntax

    For more information about modifying triggers

    See , CREATE OR ALTER TRIGGER, in Chapter 5, Data Definition (DDL) Statements.

    7.5.6. Deleting a Trigger

    The DROP TRIGGER statement is used to delete triggers.

    Syntax (complete)

    For more information about deleting triggers

    See in Chapter 5, Data Definition (DDL) Statements.