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.

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.

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.