CREATE TRIGGER

<< | FB 2.5 Language Reference | >>
<< | FB 2.1 Language Reference | >>

CREATE TRIGGER

SQL-2003-compliant syntax for relation
triggers
Database triggers
Domains instead of datatypes
COLLATE in variable declarations
NOT NULL in variable declarations
Multi-action triggers
BEGIN ... END blocks may be empty
CREATE TRIGGER no longer increments
table change count
PLAN allowed in trigger code

`CREATE TRIGGER`

Available in: DSQL, ESQL

Description

Creates a trigger, a block of PSQL code that is executed automatically upon certain database events or mutations to a table or view.

Syntax

 CREATE TRIGGER name
   {<relation_trigger_legacy>
      | <relation_trigger_sql2003>
      | <database_trigger> }
   AS
     [<declarations>]
   BEGIN
     [<statements>]
   END

 <relation_trigger_legacy>                     ::= FOR {tablename | viewname}
                                                   [ACTIVE | INACTIVE]
                                                   {BEFORE | AFTER} <mutation_list>
                                                   [POSITION number]

 <relation_trigger_sql2003>                    ::= [ACTIVE | INACTIVE]
                                                   {BEFORE | AFTER} <mutation_list>
                                                   [POSITION number]
                                                    ON {tablename | viewname}

 <database_trigger>                            ::= [ACTIVE | INACTIVE]
                                                    ON db_event
                                                   [POSITION number]

 <mutation_list>                               ::= mutation [OR mutation [OR mutation]]

 mutation                                      ::= INSERT | UPDATE | DELETE

 db_event                                      ::= CONNECT | DISCONNECT | TRANSACTION START
                                                 | TRANSACTION COMMIT | TRANSACTION ROLLBACK

 number                                        ::= 0..32767 (default is 0)

 <declarations>                                ::= See PSQL::DECLARE for the exact syntax.

"Legacy" and "sql2003" relation triggers are exactly the same. The only thing that differs is the creation syntax.
Triggers with lower position numbers fire first. Position numbers need not be unique, but if two or more triggers have the same position, the firing order between them is undefined.
When defining relation triggers, each mutation type (INSERT, UPDATE or DELETE) may occur at most once in the mutation list.

SQL-2003-compliant syntax for relation triggers

Added in: 2.1

Description

Since Firebird 2.1, an alternative, SQL-2003-compliant syntax can be used for triggers on tables and views. Instead of specifying FOR relationname before the event type and the optional directives surrounding it, you can now put ON relationname after it, as shown in the syntax earlier in this chapter.

Example

 create trigger biu_books
   active before insert or update position 3
   on books
 as
 begin
   if (new.id is null)
     then new.id = next value for gen_bookids;
 end

Database triggers

Added in: 2.1

Description

Since Firebird 2.1, triggers can be defined to fire upon the database events CONNECT, DISCONNECT, TRANSACTION START, TRANSACTION COMMIT and TRANSACTION ROLLBACK. Only the database owner and SYSDBA can create, alter and drop these triggers.

Syntax

 CREATE TRIGGER name
   [ACTIVE | INACTIVE]
   ON db_event
   [POSITION number]
   AS
     [<declarations>]
   BEGIN
     [<statements>]
   END

 db_event                 ::= CONNECT | DISCONNECT | TRANSACTION START
                            | TRANSACTION COMMIT | TRANSACTION ROLLBACK

 number                   ::= 0..32767 (default is 0)

 <declarations>           ::= See PSQL::DECLARE for the exact syntax.

Example

 create trigger tr_connect
   on connect
 as
 begin
   insert into dblog (wie, wanneer, wat)
     values (current_user, current_timestamp, 'verbind');
 end

Execution of database triggers and handling of exceptions:

CONNECT and DISCONNECT triggers are executed in a transaction created specifically for this purpose. If all goes well, the transaction is committed. Uncaught exceptions roll back the transaction, and:
- In the case of a CONNECT trigger, the connection is then broken and the exception returned to the client.
- With a DISCONNECT trigger, exceptions are not reported and the connection is broken as foreseen.
TRANSACTION triggers are executed within the transaction whose opening, committing or rolling-back evokes them. The actions taken after an uncaught exception depend on the type:
- In a START trigger, the exception is reported to the client and the transaction is rolled back.
- In a COMMIT trigger, the exception is reported, the trigger's actions so far are undone and the commit is cancelled.
- In a ROLLBACK trigger, the exception is not reported and the transaction is rolled back as foreseen.
It follows from the above that there is no direct way of knowing if a DISCONNECT or TRANSACTION ROLLBACK trigger caused an exception.
It also follows that you can't connect to a database if CONNECT trigger causes an exception, and that you can't start a transaction if a TRANSACTION START trigger does so. Both phenomena effectively lock you out of your database while you need to get in there to fix the problem. See the note below for a way around this Catch-22 situation.

In the case of a two-phase commit, TRANSACTION COMMIT triggers fire in the prepare, not the commit phase.

Note: Some Firebird command-line tools have been supplied with new switches to suppress the automatic firing of database triggers:

 gbak -nodbtriggers
 isql -nodbtriggers
 nbackup -T

These switches can only be used by the database owner and SYSDBA.

Domains instead of datatypes

Changed in: 2.1

Description

Firebird 2.1 and up allow the use of domains instead of SQL datatypes when declaring local trigger variables. See PSQL::DECLARE for the exact syntax and details.

`COLLATE` in variable declarations

Changed in: 2.1

Description

Firebird 2.1 and up allow COLLATE clauses in local variable declarations. See PSQL::DECLARE for syntax and details.

`NOT NULL` in variable declarations

Changed in: 2.1

Description

Firebird 2.1 and up allow NOT NULL constraints in local variable declarations. See PSQL::DECLARE for syntax and details.

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.

`BEGIN ... END` blocks may be empty

Changed in: 1.5

Description

BEGIN ... END blocks may be empty in Firebird 1.5 and up, allowing you to write stub code without having to resort to dummy statements.

Example

 create trigger bi_atable for atable
 active before insert position 0
 as
 begin
 end

`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.