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