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.
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
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:
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.
Changed in: 2.1
Description
Firebird 2.1 and up allow COLLATE clauses in local variable declarations. See PSQL::DECLARE for syntax and details.
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.
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:
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
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.