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, 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:

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.

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:

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 triggers

See “>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 triggers

See 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 triggers

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