Firebird Documentation Index → Firebird 2.5 Language Ref. Update → DDL statements → TRIGGER |
Table of Contents
Table of Contents
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 TRIGGERname
{<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>
[POSITIONnumber
]<relation_trigger_sql2003>
::= [ACTIVE | INACTIVE] {BEFORE | AFTER}<mutation_list>
[POSITIONnumber
] ON {tablename
|viewname
}<database_trigger>
::= [ACTIVE | INACTIVE] ONdb_event
[POSITIONnumber
]<mutation_list>
::=mutation
[ORmutation
[ORmutation
]]mutation
::= INSERT | UPDATE | DELETEdb_event
::= CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACKnumber
::= 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.
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 TRIGGERname
[ACTIVE | INACTIVE] ONdb_event
[POSITIONnumber
] AS [<declarations>
] BEGIN [<statements>
] ENDdb_event
::= CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACKnumber
::= 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 canceled.
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 a 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.
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.
Added in: 2.5
Description: Analogous to the “TYPE OF
domain
” syntax supported since version 2.1, it is now
also possible to declare variables as having the type of an existing table or view
column. See PSQL::DECLARE
for syntax and details.
Changed in: 2.1
Description: Firebird 2.1 and up allow the use of domains instead of SQL data types 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: Relation triggers can 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
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.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.
Table of Contents
Available in: DSQL, ESQL
Description: Alters an existing trigger. Relation triggers cannot be changed into database triggers or vice versa. The associated table or view of a relation trigger cannot be changed.
Syntax:
ALTER TRIGGERname
[ACTIVE | INACTIVE] [{BEFORE | AFTER}<mutation_list>
| ONdb_event
] [POSITIONnumber
] [AS [<declarations>
] BEGIN [<statements>
] END ]
See CREATE TRIGGER for the meaning of
<mutation_list>
etc.
Added in: 2.1
Description: The ALTER TRIGGER syntax (see above) has been extended to support database triggers. For a full discussion of this feature, see CREATE TRIGGER :: Database triggers.
Added in: 2.5
Description: Analogous to the “TYPE OF
domain
” syntax supported since version 2.1, it is now
also possible to declare variables as having the type of an existing table or view
column. See PSQL::DECLARE
for syntax and details.
Changed in: 2.1
Description: Firebird 2.1 and up allow the use of domains instead of SQL data types 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: The ALTER TRIGGER syntax (see above) has been extended to support multi-action triggers. For a full discussion of this feature, see CREATE TRIGGER :: Multi-action triggers.
Changed in: 2.0, 2.0.1
Description: In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still, performing these operations on a live database is potentially risky and should only be done with the utmost care.
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.
Changed in: 1.0
Description: Each time you use CREATE, ALTER or DROP TRIGGER, InterBase increments the metadata change counter of the associated table. Once that counter reaches 255, no more metadata changes are possible on the table (you can still work with the data though). A backup-restore cycle is needed to reset the counter and perform metadata operations again.
While this obligatory cleanup after many metadata changes is in itself a useful feature, it also means that users who regularly use ALTER TRIGGER to deactivate triggers during e.g. bulk import operations are forced to backup and restore much more often then needed.
Since changes to triggers don't imply structural changes to the table itself, Firebird no longer increments the table change counter when CREATE, ALTER or DROP TRIGGER is used. One thing has remained though: once the counter is at 255, you can no longer create, alter or drop triggers for that table.
Available in: DSQL
Added in: 1.5
Description: If the trigger does not yet exist, it is created just as if CREATE TRIGGER were used. If it already exists, it is altered and recompiled. Existing permissions and dependencies are preserved.
Syntax: Exactly the same as for CREATE TRIGGER.
Table of Contents
Available in: DSQL, ESQL
Changed in: 2.0, 2.0.1
Description: In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still, performing these operations on a live database is potentially risky and should only be done with the utmost care.
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.
Table of Contents
Available in: DSQL
Added in: 2.0
Description: Creates or recreates a trigger. If a trigger with the same name already exists, RECREATE TRIGGER will try to drop it and create a new trigger. RECREATE TRIGGER will fail if the existing trigger is in use.
Syntax: Exactly the same as CREATE TRIGGER.
Changed in: 2.0, 2.0.1
Description: In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still, performing these operations on a live database is potentially risky and should only be done with the utmost care.
Firebird Documentation Index → Firebird 2.5 Language Ref. Update → DDL statements → TRIGGER |