2 Replies Latest reply on Oct 25, 2002 3:35 PM by 360589

    Trigger on commit


      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. re:Trigger on commit
          Hello this is Zak from e-spatial. We are facing similar challenges. Check out workspace manager from the Oracle spatial group.
          • 2. re:Trigger on commit

            You could try something like CDM Ruleframe uses.

            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
            be executed.

            5) commit;

            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.

            good luck,