DML Triggers
DML triggers are those that fire when a DML operation changes the state of data: updating rows in tables, inserting new rows or deleting rows.They can be defined for both tables and views.
DML triggers are those that fire when a DML operation changes the state of data: updating rows in tables, inserting new rows or deleting rows.They can be defined for both tables and views.
Six base options are available for the event-phase combination for tables and views:
Before a new row is inserted |
|
After a new row is inserted |
|
Before a row is updated |
|
After a row is updated |
|
Before a row is deleted |
|
After a row is deleted |
|
These base forms are for creating single phase/single-event triggers.Firebird also supports forms for creating triggers for one phase and multiple-events, BEFORE INSERT OR UPDATE OR DELETE
, for example, or AFTER UPDATE OR DELETE
: the combinations are your choice.
Note
|
“Multi-phase” triggers, such as |
OLD
and NEW
Context VariablesFor DML triggers, the Firebird engine provides access to sets of OLD
and NEW
context variables (or, “records”).Each is a record of the values of the entire row: one for the values as they are before the data-changing event (the BEFORE
phase) and one for the values as they will be after the event (the AFTER
phase).They are referenced in statements using the form NEW.column_name
and OLD.column_name
, respectively.The column_name can be any column in the table’s definition, not just those that are being updated.
The NEW
and OLD
variables are subject to some rules:
In all triggers, OLD
is read-only
In BEFORE UPDATE
and BEFORE INSERT
code, the NEW
value is read/write, unless it is a COMPUTED BY
column
In INSERT
triggers, references to OLD
are invalid and will throw an exception
In DELETE
triggers, references to NEW
are invalid and will throw an exception
In all AFTER
trigger code, NEW
is read-only
A trigger associated with a database or transaction event can be defined for the following events:
Connecting to a database |
|
Before the trigger is executed, a transaction is automatically started with the default isolation level (snapshot (concurrency), write, wait) |
Disconnecting from a database |
|
Before the trigger is executed, a transaction is automatically started with the default isolation level (snapshot (concurrency), write, wait) |
When a transaction is started |
|
The trigger is executed in the transaction context of the started transaction (immediately after start) |
When a transaction is committed |
|
The trigger is executed in the transaction context of the committing transaction (immediately before commit) |
When a transaction is cancelled |
|
The trigger is executed in the transaction context of the rolling back transaction (immediately before roll back) |