This discussion is archived
2 Replies Latest reply: Oct 25, 2002 8:35 AM by 360589 RSS

Trigger on commit

70094 Newbie
Currently Being Moderated
HI

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
    279140 Newbie
    Currently Being Moderated
    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
    360589 Newbie
    Currently Being Moderated
    Hi,

    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,
    Marcel