> In the application I support, SQL is dynamically created by the
> application. On extremely rare occasions, the SQL generated is bad and
> an exception is thrown. Since our users are never running in a
> programming environment, this can be a hard thing to duplicate and track
> down. I'd like to use the new DB trigger feature in conjunction with
> the new MON$ tables feature to help track these. In the application, a
> good transaction is committed, and an exception is rolled back. So I
> did the following:
>
> CREATE TRIGGER DATALOGGER
> ACTIVE ON TRANSACTION ROLLBACK POSITION 0
> AS
> BEGIN
> INSERT INTO BADSQL(SQLTIME, SQLCMD)
> SELECT CURRENT_TIMESTAMP, M.MON$SQL_TEXT
> FROM MON$STATEMENTS M
> WHERE M.MON$TRANSACTION_ID = CURRENT_TRANSACTION;
> END
>
> I figured this would allow me to check a user's database and find out
> why they reported an error. Unfortunately it never works. The
> MON$TRANSACTION_ID is always null.
>
> Is this possible to do?

The ON TRANSACTION ROLLBACK trigger feature is ready for prime time when
Firebird supports autonomous transactions in PSQL, because if a ROLLBACK
happens, the INSERT INTO statement in the trigger gets rollbacked as well.

At least this is what I have experienced when I first tried a ROLLBACK
trigger. Of course, under the assumption that the BADSQL table is not an
external table. Using an external table should work in this case,
because external tables are outside of transaction control.




--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com