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

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, the position is 0. If multiple triggers have the same position and phase, those triggers will be executed in an undefined order, while respecting the total order by position and phase.

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.

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

7.5.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.5.3. 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 default transaction is automatically started

Disconnecting from a database

ON DISCONNECT

Before the trigger is executed, a default transaction is automatically started

When a transaction is started

ON TRANSACTION START

The trigger is executed in the current transaction context

When a transaction is committed

ON TRANSACTION COMMIT

The trigger is executed in the current transaction context

When a transaction is cancelled

ON TRANSACTION ROLLBACK

The trigger is executed in the current transaction context

7.5.4. Creating Triggers

Syntax 

   |CREATE TRIGGER trigname {
   |    <relation_trigger_legacy>
   |  | <relation_trigger_sql2003>
   |  | <database_trigger> }
   |AS
   |  [<declarations>]
   |BEGIN
   |  [<PSQL_statements>]
   |END
   | 
   |<relation_trigger_legacy> ::=
   |  FOR {tablename | viewname}
   |  [ACTIVE | INACTIVE]
   |  {BEFORE | AFTER} <mutation_list>
   |  [POSITION number]
   | 
   |<relation_trigger_sql2003> ::=
   |  [ACTIVE | INACTIVE]
   |  {BEFORE | AFTER} <mutation_list>
   |  [POSITION number]
   |  ON {tablename | viewname}
   | 
   |<database_trigger> ::=
   |  [ACTIVE | INACTIVE]
   |  ON <db_event>
   |  [POSITION number]
   | 
   |<mutation_list> ::=
   |  <mutation> [OR <mutation> [OR <mutation>]]
   | 
   |<mutation> ::= { INSERT | UPDATE | DELETE }
   | 
   |<db_event> ::=
   |    CONNECT
   |  | DISCONNECT
   |  | TRANSACTION START
   |  | TRANSACTION COMMIT
   |  | TRANSACTION ROLLBACK

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 triggersSee ">CREATE TRIGGER 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 

   |ALTER TRIGGER trigname
   |  [ACTIVE | INACTIVE]
   |  [{BEFORE | AFTER} <mutation_list> | ON <db_event>]
   |  [POSITION number]
   |  [
   |    AS
   |      [<declarations>]
   |    BEGIN
   |      [<PSQL_statements>]
   |    END
   |  ]
   | 
   |<mutation_list> ::=
   |  <mutation> [OR <mutation> [OR <mutation>]]
   | 
   |<mutation> ::= { INSERT | UPDATE | DELETE }
   | 
   |<db_event> ::=
   |  { CONNECT
   |  | DISCONNECT
   |  | TRANSACTION START
   |  | TRANSACTION COMMIT
   |  | TRANSACTION ROLLBACK }

For more information about modifying triggersSee ALTER TRIGGER, CREATE OR ALTER TRIGGER, RECREATE 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) 

  |DROP TRIGGER trigname

For more information about deleting triggersSee DROP TRIGGER in Chapter 5, Data Definition (DDL) Statements.