DML Operations and State-Changing Events - Firebird

Firebird incorporates some features that can be implemented in database design to respond to DML operations that change the state of data, namely the posting of INSERT, UPDATE, and DELETE statements.

Referential Integrity Action Clauses

Referential integrity triggers are modules of compiled code created by the engine when you define referential integrity constraints for your tables. By including ON UPDATE and ON DELETE action clauses in FOREIGN KEY constraint declarations, you can specify one of a selection of pre-defined response actions that will occur when these DML events execute.

Custom Triggers

With custom triggers (those you write yourself using the PSQL language), you have the capability to specify exactly what is to happen when the server receives a request to insert, change, or delete rows in tables. Custom triggers can be applied not just to the update and delete events, but also to inserts. Triggers can include exception handling, feedback, and (in Firebird 1.5) custom query plans.

DML Event Phases

Trigger syntax splits the custom DML actions into two phases: the first phase occurs before the event, and the second occurs after the event.

  • The BEFORE phase makes it possible to manipulate and transform the values that are input by the DML statement and to define defaults with much more flexibility than is permitted by the standard SQL DEFAULT constraint. The BEFORE phase completes before any column, table, or integrity constraints are tested.
  • In the AFTER phase, response actions can be performed on other tables. Usually, such actions involve inserting to, updating, or deleting from these other tables, using the NEW and OLD variables to provide the context of the current row and operation. The AFTER phase begins after all of the owning table’s constraints have been applied. AFTER triggers cannot change values in the current row of the owning table.

Table describes the six phases/events of custom triggers.

The Six Phases/Events of Custom Triggers

NEW and OLD Context Variables

The server makes two sets of context variables available to triggers. One consists of all the field values of the current row, as they were in the current row just before the row was last posted. The identifiers for this set consist of the word “OLD.” prefixed to each column identifier. In the same manner, all of the new values have “NEW.” prefixed to each column identifier. Of course, “OLD.” is meaningless in an insert trigger and “NEW.” is meaningless in a delete trigger.

Multi-action Triggers

From Firebird 1.5 onward, you can optionally write triggers with conditioned logic to roll all of the events (insert, update, and delete) for one phase—BEFORE or AFTER— into one trigger module. This is a long-awaited enhancement that can save up to two-thirds of the coding of triggers.

Multiple Triggers per Event

Another useful option is the ability to have multiple triggers for each phase/event combination. CREATE TRIGGER syntax includes the keyword POSITION, taking an integer argument that can be used to set the zero-based firing order of multiple triggers within a phase.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics