Re: trigger with ora-04091

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Tue, 21 Dec 2004 21:04:49 +0100
Message-ID: <b80hs010cl71o843toh5d2tgeholurmunt@4ax.com>

On 21 Dec 2004 07:36:59 -0800, zana_f_at_hotmail.com wrote:

>i tried instead of row trigger a statement trigger with same trigger
>code but then the trigger not fired when i updated table
>(z_wedstrijden). so does you know what is the cause of that ? and how
>can i resolve it ? i appreciate your help.

You are selecting (in your procedure bereken totaal) from the table on which the trigger fires. As the table is mutating, the select can't provide any read-consistent results, and consequently you are getting ora-4091.

The classical solution is by means of 3 triggers

1 before statement trigger
initialize a PL/SQL table holding the primary key of the affected record
2 before row trigger
do whatever calculation you need to do, and store the result in a PL/SQL table
3 after row trigger
Loop through your PL/SQL table and perform updates to the dependent tables.

Examples on http://asktom.oracle.com

--
Sybrand Bakker, Senior Oracle DBA

Received on Tue Dec 21 2004 - 14:04:49 CST