i'd like to perform some data integrity checking which includes several records from a table. The data must obey the rules on every commit like in the case of a deferred constraint. where can i place my plsql block... a good idea would help me much. thanks in adv.
1) create a table CHECK_COMMIT with say a column STATE
and a deferred check constraint check (STATE = 'OK')
2) in the before statement trigger(s) set STATE to anything but 'OK'
3) do your thing here
4) call your procedure, say CLOSE_TRANSACTION, to validate your business rules.
if all business rules are satisfied, set the STATE column to OK. In CDM RuleFrame
this is called close_transaction, which will call all business rules that has to
if you didn't close the transaction (step 4), an error will occur because
of the deferred check constraint.
So, still not what we really want, an ON COMMIT trigger, but it works. Draw-back is that
you have to build before statement triggers that open a transaction and at the end
call the close_transaction. However, this prevents DML that has not been validated.