Two methods for processing the variant table (ORA-04091) in Oracle

Oracle often used trigger often appears ora-04091 variant table problem, here bring two kinds of processing methods for your reference.

-- ***** Example ***** Scenario Description:

1. init table create table sales (prod_code varchar2 (4), amount_sold number );
Insert into sales values ('000000', 0); insert into sales values ('000000', 0); insert into sales values ('000000', 0 );
Commit;
Select * from sales order by 1;

2. If a subcategory is inserted or updated, the total sales volume of the parent category must be updated in a cascade.

For example, if the sales volume of category 0111 is updated and the sales volume of Category 1 is increased, the sales volume of category 0100 in parent category must also be added with 1/* Test trigger: create or replace trigger sales_amount_refresh after insert on sales for each row when (new. amount_sold> 0) declare begin update sales set amount_sold = amount_sold +: new. amount_sold where substr (: new. prod_code, 0, length (rtrim (prod_code, '0') = rtrim (prod_code, '0'); end;
*/

Issues with the variant table/* Test SQL:
SQL> insert into sales values ('20140901', 1); insert into sales values ('20140901', 1) ORA-04091: table DEXTER. SALES is mutating, trigger/function may not see it ORA-06512: at "DEXTER. SALES_AMOUNT_REFRESH ", line 4 ORA-04088: error during execution of trigger 'dexter. SALES_AMOUNT_REFRESH '*/

Solution 1: You can use a self-made transaction to solve the problem, but with a bug, you cannot roll back.
/* Create or replace trigger sales_amount_refresh after insert on sales for each row when (new. amount_sold> 0) declare pragma autonomous_transaction; begin update sales set amount_sold = amount_sold +: new. amount_sold where substr (: new. prod_code, 0, length (rtrim (prod_code, '0') = rtrim (prod_code, '0'); commit; end; statements in autonomous transactions will not be rolled back */

Solution 2: Use the collection variable of the package to record the information that needs to be changed, and then use the statement Level trigger to perform the update operation. The variables in the package are only valid in the session-you only need to create a package header.
Create or replace package pkg_vars is type c_t is table of number index by varchar2 (4); refresh_sales_list c_t; end ;/

Modify the row-Level trigger to create or replace trigger sales_amount_refresh after insert on sales for each row when (new. amount_sold> 0) declare begin if length (rtrim (: new. prod_code, '0')> 2 then pkg_vars.refresh_sales_list (: new. prod_code): =: new. amount_sold; end if; end ;/

Create or replace trigger sales_amount_refresh_statement after insert on sales declare ind varchar2 (4); begin ind: = pkg_vars.refresh_sales_list.first; WHILE ind is not null loop update sales set amount_sold = amount_sold + pkg_vars.refresh_sales_list (ind) where substr (ind, 0, length (rtrim (prod_code, '0 '))) = rtrim (prod_code, '0') and length (rtrim (prod_code, '0') = 2; ind: = pkg_vars.refresh_sales_list.NEXT (ind); end loop; pkg_vars.refresh_sales_list.delete; end ;/