The Hunt for the Oracle Database On Commit trigger
Back in 1998 or 1999 I filed a bug/enhancement request in the Oracle bug database. As I was still working at Oracle back then and I had direct access to the bug database – primarily for the many beta testing activities I was involved in. The request was for an On Commit trigger, to fire immediately before the commit process would irrevokably be under way. The trigger should allow me to do some last minute business rule enforcements, to make sure the transaction was really, really okay.
At the time I was working on Oracle’s CDM RuleFrame framework for the implementation of data oriented business rules. It was somewhat novel at that time, as it focused on transaction level business rule enforcement. Our philosophy was: as long as you do not commit, it does not matter one bit if the data is not in line with all data integrity rules. Who cares? At time you commit – that is when it should be okay. The kind of same idea behind the deferred declarative database integrity constraints. The problem we were facing is that we had no way to invoke this transaction level validation of rules when the application commits. The database will take care of ‘simple’ declarative constraints, and for multi-table constraints or complex single table constraints that had to be programmed in PL/SQL, we could do nothing but enforce applications to perform this validation by deliberately invalidating a deferred check constraint from before statement level triggers for each DML type on all tables protected by our special business rules. Only if our CDM RuleFrame validation code had been called would we rectify that constraint violation and allow the transaction to continue. What I wanted to have was a way for the database to automatically invoke the CDM RuleFrame validation logic when the transaction is committed.
The enhancement request never saw any action. So today, seven years onwards, we still do not have an On Commit trigger in the database. There some ways to emulate one, but none of them is ideal. In this article a brief report on what hooks we have for our make-believe On Commit trigger.
The proceedings in the database surrounding the commit of a transaction include the following steps:
- validate deferred constraints
- delete all data from Temporary Tables with ON COMMIT DELETE ROWS
- synchronize Materialized View with ON COMMIT REFRESH
- operations on Materialized View Logs? Operations on Changed Data Capture Window?
And once the transaction is really found to be okay:
- synchronize redo log buffers to file
- release locks
- ‘submit’ pending jobs
- send alerts
I have looked into these events to see which one could be used as a trigger for my own additional PL/SQL code.
I had some hopes for Validation of Deferred Constraints. Well, that takes place but does not fire any triggers. I once thought that a Deferred Foreign Key Constraint with ON DELETE CASCADE in combination with a delete trigger on the referring (Child) table would work: the parent is deleted somewhere during the transaction, the ON DELETE CASCADE is part of a deferred constraint and therefore takes place after the application has sent the commit command. Well, no: it turns out that even for deferred constraints, the cascade deletion of children is Immediate.
Then I hoped that perhaps in 10gR2 the DML Error Log feature – which allows us to complete statements that violate declarative integrity constraints (see for example: http://orafaq.com/node/76) while writing the offending records to a logging table – would help out: an insert trigger on this logging table in combination with a deferred constraint that always gets violated during my transactions surely would do the trick? No, it does not, as DML Error Log does not work for deferred constraints…
Note that when validation of deferred constraints runs into a violation, the entire transaction gets rolled back. There is an absolute minimum in terms of information on what violation for which record caused this rollback.
One other event at commit time is the emptying of temporary tables with ON COMMIT DELETE ROWS set. That means that when the transaction commits, all records are flushed out of the TEMPORARY table. Well, I understood it would be naive to simply create a DELETE trigger on the temporary table (which I can do) and expect it to be fired if the records are removed at commit time (which it does not). The flushing of records from the temporary table is a low level, no log action – much like TRUNCATE. I still have to check whether perhaps a TRUNCATE system even level trigger would do the trick (though I doubt it).
I though a smart approach would be: foreign key to temporary table with ON DELETE CASCADE and a delete trigger on the child table. However, it turned out that Referential Constraints to temporary tables are not allowed in the database. Another idea bites the dust.
Refreshing Materialized Views
Well, that means I am back to a solution I had found quite some time ago: the Materialized View. See for example the article Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints) for some background. The idea is quite simple:
- create a materialized view log for a table X ( the table should have a primary key)
- create a materialized view on that table – set the refresh to ‘on commit’ : create materialized view mv refresh fast on commit…
- Add an insert and/or update trigger to the materialized view (yes, you can do that!); this trigger in turn can invoke whatever code you want to run at this late stage in your transaction.
- add such logic in before insert, update and delete statement level triggers on all to be protected tables in your schema that perform some sort of dml on the table X that will cause the materialized view to be refreshed (update a record associated with the current session is probably best as to minimize contention for this table)
The disadvantages of this approach include the overhead of extra objects – a dummy table, a Materialized View Log on that table, a Materialized View as well as statement level triggers on all meaningful tables in the schema – and the potential contention between sessions all having to refresh the materialized view and locking each other out while the business rules get validated.
I still want to investigate if the changed data capture somehow gives me any hooks for event triggers. I should also investigate the TRUNCATE trigger on tempoary tables – though I doubt this approach: I am not sure if this event may be only after the commit itself, too late for my purpose. I am also not exactly clear on what Materialized View Logs exactly go through.
If you would have any suggestions for mechanisms in the database that may help me get as close to a commit trigger as I can get, I would be thankful.
- Select Trigger in Oracle Database – introducing Fine Grained Auditing
- 10gR2 New Feature: Asynchronous Commit
- PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving
- Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines)
- Oracle Database 10g Release 2 – SQL and PL/SQL Features