Vlad Horsun wrote:

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

Yes. Forget to mention because I don't see anything useful for it. :-)

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

Yes.

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

What about pass the exception to client (instead of log) but
invalidating the attachment handle?

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

Yes.

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

They are executed too.

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

Seems nice. And maybe ON TRANSACTION START (instead of BEGIN TRANSACTION).

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

Seems not logical to auto-run things when doing backup or fixing a database.

>
>> To run CONNECT/DISCONNECT, the engine will start and finish a snapshot
>> transaction.
>>
>
> Agree. What about other parameters ? concurrency, write, wait ?
>

I think we should define a constant TPB.
Seems difficult to make TPB customizations.

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

While they are SQL standard, they're not so useful as db triggers.
They could be added later.


Adriano