|Subject||Re: [firebird-support] FB 2.1 - new DB trigger feature with MON$ tables|
> In the application I support, SQL is dynamically created by theThe ON TRANSACTION ROLLBACK trigger feature is ready for prime time when
> 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
> INSERT INTO BADSQL(SQLTIME, SQLCMD)
> SELECT CURRENT_TIMESTAMP, M.MON$SQL_TEXT
> FROM MON$STATEMENTS M
> WHERE M.MON$TRANSACTION_ID = CURRENT_TRANSACTION;
> 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?
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.
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and