Subject Re: [Firebird-Architect] Database triggers
Author Jim Starkey
Adriano dos Santos Fernandes wrote:
> Triggers fired in database events are useful for many things:
> On connect/disconnect for logging or logon control.
> On begin transaction for implementation of a "writable transaction
> id" (incrementing a generator and storing in a context variable).
> On commit triggers for business rules validations.
> Proposed syntax:
> CREATE TRIGGER <name> <type> AS ...
> Types:
> AFTER CONNECT (or LOGON as in Oracle?)
> They will be stored in RDB$TRIGGERS with RDB$RELATION_NAME = NULL.
> RDB$TRIGGER_TYPE will store incompatible values with the current ones,
> but RDB$RELATION_NAME = NULL make they work in different ways.
> Or another field (RDB$TRIGGER_KIND?) could be created to store in what
> event the trigger are fired (DML, DATABASE, DDL in the future), so the
> real trigger type become (RDB$TRIGGER_KIND, RDB$TRIGGER_TYPE).
> These triggers may make the database unusable (for example, an exception
> in a CONNECT trigger), so it could be disabled (only for the attachment)
> through a DPB, but only by sysdba/db-owner.
> Attachments from GBAK/GFIX/GSEC automatically make this flag ON.
> To run CONNECT/DISCONNECT, the engine will start and finish a snapshot
> transaction.
> Exceptions in CONNECT, BEGIN TRANSACTION and COMMIT cancels the
> operation. But not cancels (the detach) in DISCONNECT.
> Comments?
This would be a very starting point for virtual tables. Rather than
having a NULL table table, create suitable virtual tables for connection
and transaction data. This will allow you to use the standard trigger
mechanisms without change. It also gives a good place to implement
system monitoring functions.

What account do the trigger run under?

Stepping back, you proposal is written as a solution looking for a
problem. It would be easier to evaluate if you started by saying what
problem you are trying to solve. Then we could evaluate the proposal in


Jim Starkey, Senior Software Architect
978 526-1376