Proposal for db level triggers

Background:

Firebird 2.1 allows:

CREATE TRIGGER name ON
(
CONNECT
| DISCONNECT
| TRANSACTION START
| TRANSACTION COMMIT
| TRANSACTION ROLLBACK
)

I want to allow syntax:

CREATE TRIGGER <name> event

event (3 variations, application can specify any number of them):
AFTER CONNECT
AFTER TRANSACTION START
BEFORE TRANSACTION COMMIT

In usage:

AFTER START clears counters and flags.
UPDATE triggers on data set counters and flags.
BEFORE COMMIT examines the counters and flags and performs any final
validation or
adjustments (or external events such as sending a MoM message)

Some of this can be done with the event mechanism but:
- it can be handy to have a place to check the consistency of all
changes as a whole
(assuming they've been recorded by other triggers)
- the event mechanism is a bit sucky at the moment anyway, and with
this I can go straight
to my MoM of choice instead of using a gateway

Storage:

In pg_trigger with:
tgrelid = (oid)0
(and constraint data set to 'f',0,0)
use additional bits in the tgtype column (smallint)

Check: enough bits remain in the tgtype column. Currently it stores
(B|A)(I|U|D) but we may
be able to reuse the I,U,D bits for CONNECT, START, COMMIT respectively.

Caching:

Backends should cache the set of database-level triggers and refresh it
at the start of a transaction.
The transaction triggers should be stored in 2 lists for ease of access.

A shared memory location should store the last time (or a sequence
value) reflecting changes to the
set of database-level triggers. The timestamp/sequencer should be
updated atomically as we changes
to pg_trigger (either for all such changes, or only for database
triggers) : such changes are quite
rare.

Execution:

Triggers in the same class run in alphabetic order.

Database triggers are NOT run in the account that is dbo. This ensures
that dbo
is never prevented from connecting to a database or from listing or dropping
such triggers. (Consider allowing them to be turned on using a 'set'
command: the
connect will happen immediately that completes. The default will be 'off'.)

The AFTER CONNECT triggers run immediately after authentication in an
implicit transaction. If the
transaction is rolled back by any trigger then we disconnect immediately.

The AFTER START triggers are run when we enter a new transaction
context, before any changes are
made. If there is a rollback then we stop processing triggers.

The BEFORE COMMIT triggers are run just before normal commit processing
starts. They may elect to
roll back. If there is a rollback then we stop processing triggers.

Implementation:

(I made this up. I don't know what I'm doing really. This is from a
very cursory
look at the code. Help!)

AFTER CONNECT needs to be inserted at the end of the authorization
processing and we need to
detect a rollback there so we can disconnect.

AFTER START can be handled by a new state inserted in front of
TRANS_INPROGRESS (or handled
inline in xact.c:StartTransaction?)

BEFORE COMMIT is handled in xact.c:CommitTransaction. We already allow
triggers to be deferred
to the commit and run in AfterTriggerFireDeferred. We need to allow
those triggers to run,
then we run the BEFORE COMMIT triggers, and then perform a second round
of pending deferred
triggers. So, insert code before AfterTriggerEndXact(true). Needs to
be in PrepareTransaction
too.

Unknown: how do autocommit statements work? Need to operate 'as if' we
have start and commit.

The usage is:

AFTER CONNECT provides a way to enhance security and permissioning. It
allows us to
initialise state.

AFTER START is used to re-initialise state. We enter START immediately
we try to do anything, so we
defer recovery from ROLLBACK to this point. We can tell we have rolled
back logically since we
have state left around.

BEFORE COMMIT is run just before we perform the actual commit: it can
still make
changes to data, and can still rollback. It needs to do side effects
and clear out state.

James