Triggers

Table of Contents

Firing Order (Order of Execution)
DML Triggers
Database Triggers
Creating Triggers
Modifying Triggers
Deleting a Trigger

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

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.

DML Triggers

Table of Contents

Trigger Options
OLD and NEW Context Variables

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:

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.

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

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

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.

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>]
[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.

Deleting a Trigger

The DROP TRIGGER statement is used to delete stored procedures.

Syntax (complete):

DROP TRIGGER trigname;

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