"Adriano dos Santos Fernandes" ...

> 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.

And on rollback of course

> Proposed syntax:
> CREATE TRIGGER <name> <type> AS ...
>
> Types:
> AFTER CONNECT (or LOGON as in Oracle?)

ON CONNECT
fired after user validation and attachment creation but before
return from jrd8_attach_database. Worked in own auto-started
and auto-stoped transaction. Any unhandled exception prevent
CONNECT to be succsesfull and error code returns to client as
result

> BEFORE DISCONNECT (or LOGOFF)

ON DISCONNECT
fired before erase of any attachment resources. Worked in own
auto-started and auto-stoped transaction. Any unhandled exception
don't prevent DISCONNECT but logged in firebird.log

> AFTER BEGIN TRANSACTION

ON BEGIN TRANSACTION
fired just after transaction starts in context of that transaction.
Any unhandled exception breaks this transaction. Error code returns
to client

> BEFORE COMMIT

ON END TRANSACTION
fired before transaction end (and therefore before any DFW works).
Unhandled exception leave transaction open. Error code returns to client
What to do with commit\rollback_retaining ? Needs read-only context variable
with kind of transaction end (commit\rollback).

Or use common syntax

ON <TRANSACTION | COMMIT | ROLLBACK>
where ON TRANSACTION fired after transaction start, ON COMMIT - before
commit, ON ROLLBACK - before rollback

> 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.

Agree

> 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).

For now i don't see needs in such field

> 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.

Maybe yes

> Attachments from GBAK/GFIX/GSEC automatically make this flag ON.

Why ?

> To run CONNECT/DISCONNECT, the engine will start and finish a snapshot
> transaction.

Agree. What about other parameters ? concurrency, write, wait ?

> Exceptions in CONNECT, BEGIN TRANSACTION and COMMIT cancels the
> operation. But not cancels (the detach) in DISCONNECT.

Agree


Regards,
Vlad

PS What about triggers on DDL : CREATE\ALTER\DROP TABLE\PROCEDURE\etc ?