CREATE TRIGGER

Table of Contents

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

Tip

Find a more recent version at Firebird 5.0 Language Reference: TRIGGER

Available in: DSQL, ESQL

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.

Syntax:

CREATE TRIGGER trigger-name for table-name
   [ACTIVE | INACTIVE]
   {BEFORE | AFTER} <actions>
   [POSITION number]
   AS
   <trigger_body>

<actions>       ::= <single_action> [OR <single_action> [OR <single_action>]]
<single_action> ::= INSERT | UPDATE | DELETE

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.

PLAN allowed in trigger code

Changed in: 1.5

Description: Before Firebird 1.5, a trigger containing a PLAN statement would be rejected by the compiler. Now a valid plan can be included and will be used.