Re: Triggers and commits

Hi.

 You are NOT supposed to have a COMMIT in trigger.  Think what happens when following scenario is executed :

1. Update row in table-a
2. Trigger "fired"
3. First stored proc is executed and COMMITTED. The update for the original row is committed as well.
4. Second stored proc is executed and FAILS ( for any reason application/system ).
5. Oracle is supposed to ROLLBACK everything , but it can't. Data integrity may be lost.

  Anyway, if you are lucky and working with 8i - you may   use autonomous transactions to do that kind of stuff.

  Good luck. Michael.

In article <7sajl7$669$1_at_nnrp1.deja.com>,   lucas.m.anderson_at_ac.com wrote:
> I have a trigger that calls several stored procedures. Within the
> procedures are commits. When I performed the action that kicks off
the
> trigger, Oracle tells me that it cannot perform a commit within a
> trigger.
>
> How can I get around this? The commits are necessary because we need
> to be able to restart the procedures at controlled points if an
> exception occurred. On the other hand, we need to be able to kick off
> the procedures in an automatic fashion.
>
> Thanks,
> Luke
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 22 1999 - 15:41:59 CDT