Lot's of good advice. Since the on-commit trigger(s) are called for
every commit, I was planning on making it look more like a switch
statement, and call stored procedures based on a context variable.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of unordained
Sent: Monday, May 04, 2009 4:05 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Table transactional triggers

---------- Original Message -----------
From: "Rick Debay" <rdebay@...> > > You could use ON
TRANSACTION COMMIT trigger with, probably, help of

> RDB$SET_CONTEXT\RDB$GET_CONTEXT functions. But it fire at transaction
> commit time, not after.
>
> I'm assuming the trigger fires within the context of the transaction
> that is committing. Thanks for the tip, I'll be back with questions
> concerning that approach.
>
> > If you need after commit - use events.
>
> That would be the cleanest implementation, except it would require
> something outside the database. This is a data integrity issue, and
> I'd like the database to keep all the relationships straight.

------- End of Original Message -------

If it's a data-integrity issue, use the on-commit triggers. If something
fails horribly in this "clean up" process, the exception will nicely
fail the attempt to commit (but does not automatically rollback the
transaction.)

A few general notes on this approach:

- look up the isql options required to disable on-commit triggers; if
you code it wrong, you'll be scrambling for them anyway.

- be careful not to *always* attempt some sort of DML in on-commit,
remember that the transaction may be read-only, and such attempts will
fail; for example, if you use FlameRobin to connect to your database, it
uses a read-only transaction to fetch info about the database -- if your
trigger blows up because it attempts DML, you'll suddenly find
FlameRobin blows up when logging into the database! another example: at
least with Tomcat/DBCP, the connection pool's "ping" function (such as
"select 1 from rdb$database") will run in its own transaction, commit,
then become available to you -- if you're performing expensive work
every time, you'll discover that just getting a new connection from the
pool is suddenly slow!

- don't forget to watch for SQLException in your client code when you
call (your equivalent of) connection.commit(); this can be thrown by
more than just the obvious DML operations; most people never run into
this, and don't have proper try/catch blocks around commit and are
surprised to discover one new point of failure. if you're
tracing/profiling your code, it's also a new potential bottleneck to
log.

- move the code you would call on-commit into a procedure, and call that
procedure from the trigger: it makes debugging a lot easier, and if
you're doing bulk operations where you need to "clean up" after yourself
in spurts (not wait until you're completely done) that's handy too; in
that case, call
rdb$set_context() from the procedure to indicate "everything is done",
and check it in the trigger, to avoid re-doing work (assuming you have
rdb$set_context in all the right places to indicate that something needs
to be done otherwise.) also, since the on-commit trigger must be owned
by sysdba (I think), this gives you a procedure you can alter from
another username.

- remember that on-commit triggers don't see changes made by other
concurrent transactions (they run within your transaction context, in
your isolation mode) so they're just as limited as CHECK constraints.
you can't have an on-commit trigger truly enforce a custom UNIQUE
constraint, for example; there are still loopholes where transactions
can cooperate to make an end-run around the validation.

-Philip