CREATE TRIGGER

<< | FB 2.0 Language Reference | >>

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.

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.

See also:
CREATE OR ALTER EXCEPTION
ALTER TRIGGER
CREATE EXCEPTION
CREATE PROCEDURE
DROP EXCEPTION
DROP TRIGGER
Trigger
EXECUTE PROCEDURE
DDL - Data Definition Language

back to top of page
<< | FB 2.0 Language Reference | >>