CREATE TRIGGER

CREATE TRIGGER

Multi-action triggers
CREATE TRIGGER no longer
increments table change count
PLAN allowed in trigger code

`CREATE TRIGGER`

Available in: DSQL, ESQL

Description

Creates a trigger, i.e. a block of PSQL code that is executed automatically before or after certain mutations to a table or view.

Syntax

 CREATE TRIGGER name FOR {table | view}
    [ACTIVE | INACTIVE]
    {BEFORE | AFTER} <action_list>
    [POSITION number]
    AS
    <trigger_body>

 <action_list> ::= <action> [OR <action> [OR <action>]]

 <action>      ::= INSERT | UPDATE | DELETE

Multi-action triggers

Added in: 1.5

Description

Triggers can now be defined to fire upon multiple operations (INSERT and/or UPDATE and/or DELETE). Three new Boolean context variables (INSERTING, UPDATING and DELETING) have been added so you can execute code conditionally within the trigger body depending on the type of operation.

Example

 create trigger biu_parts for parts
    before insert or update
 as
    begin
    /* conditional code when inserting: */
   if (inserting and new.id is null)
      then new.id = gen_id(gen_partrec_id, 1);

    /* common code: */
    new.partname_upper = upper(new.partname);
 end

Note: In multi-action triggers, both context variables OLD and NEW are always available. If you use them in the wrong situation (i.e. OLD while inserting or NEW while deleting), the following happens:

If you try to read their field values, NULL is returned.
If you try to assign values to them, a runtime exception is thrown.

`CREATE TRIGGER` no longer increments table change count

Changed in: 1.0

Description

In contrast to InterBase, Firebird does not increment the metadata change counter of the associated table when CREATE, ALTER or DROP TRIGGER is used. For a full discussion, see ALTER TRIGGER no longer increments table change count.