Firebird Documentation Index → Firebird 1.5 Language Ref. Update → DDL statements → CREATE TRIGGER |
Available in: DSQL, ESQL
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.
Syntax:
CREATE TRIGGERtrigger-name
fortable-name
[ACTIVE | INACTIVE] {BEFORE | AFTER}<actions>
[POSITIONnumber
] AS<trigger_body>
<actions>
::=<single_action>
[OR<single_action>
[OR<single_action>
]]<single_action>
::= INSERT | UPDATE | DELETE
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.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.
Firebird Documentation Index → Firebird 1.5 Language Ref. Update → DDL statements → CREATE TRIGGER |