Hi Adriano,

Sounds very good!

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?)
> BEFORE DISCONNECT (or LOGOFF)
> AFTER BEGIN TRANSACTION
> BEFORE COMMIT
>
> 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.
>

What if a user other than SYSDBA/DB owner wants to use GBAK/GFIX/GSEC?
If the flag is automatically set ON, how will it react if the user does
not have the right to disable system triggers?

Also, what parameters will be available within the triggers? For
example, in a before connect trigger, I imagine the current_role and
current_user variables would be accessible; however, as far as I know,
the password would not be accessible, which would severely limit the
usefulness of a before connect trigger (I imagine it being used for
performing custom user/password validation at the database level).
Perhaps, since we're talking about triggers, we could make available a
sort of virtual "table" against which the trigger would be performed. In
other words, could we write triggers like this (assuming "users" to be a
custom table of users):

create trigger tr_login before connect as
declare variable user_rights varchar(50);
begin
select user_rights from users where login = new.user_name and password
= new.password into :user_rights;
if (user_rights is not null) then
set_context('user_rights', :user_rights);
else
raise error; (I can't remember the syntax for that...)
end

--
Best regards,
Jonathan Neve
_______________
CopyTiger - advanced database replicator for Interbase/Firebird!
Web : http://www.microtec.fr/copycat/ct
_______________________________________
CopyCat - database replication components for Delphi/C++Builder!
Web : http://www.microtec.fr/copycat/cc