CREATE TRIGGER

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

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.

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.