ORA-04088: error during execution of trigger 'OES2.T_UPDATE_ORDERS_GROSS'

I created the trigger: -- drop trigger t_update_orders_gross

create or replace trigger t_update_orders_gross
before insert on orders_update
for each row
declare
v_gross int;
v_subtotal int;
begin
select subtotal into v_subtotal from orders;
if v_subtotal is null then
    :new.subtotal:=testing(:new.order_no); 
--  testing is a function that will calculate the subtotal for the given 
-- order_no
    update orders set subtotal=:new.subtotal
    where order_no=:new.order_no;
end if;
end;

-- Procedure to call the insert statement in the orders_update table that should fire the triger :

create or replace procedure p_update_orders_grossSales
as
v_order_no orders.order_no%type;   
v_order_date orders.order_date%type;
v_ship_date orders.ship_date%TYPE;
v_shipping_method orders.shipping_method%type;
v_tax_status orders.tax_status%type;
v_subtotal orders.subtotal%type;
v_tax_amt orders.tax_amt%type;
v_shipping_charge orders.shipping_charge%type;
v_total_amt orders.total_amt%type;
v_customer_no orders.customer_no%type;
v_employee_no orders.employee_no%type;
v_branch_no orders.branch_no%type;
cursor c1 is select order_no,order_date,ship_date,shipping_method,tax_status, subtotal,tax_amt,shipping_charge,total_amt,customer_no,employee_no,branch_no from orders;

begin
open c1;
loop
    fetch c1 into v_order_no,v_order_date,v_ship_date,v_shipping_method,v_tax_status,v_subtotal,v_tax_amt,v_shipping_charge,v_total_amt,v_customer_no,v_employee_no,v_branch_no;
    exit when c1%notfound;
    insert into orders_update (
            order_no,order_date,ship_date,shipping_method,tax_status, subtotal,tax_amt,shipping_charge,total_amt,customer_no,employee_no,branch_no)
        values (v_order_no,v_order_date,v_ship_date,v_shipping_method,v_tax_status,v_subtotal,v_tax_amt,v_shipping_charge,v_total_amt,v_customer_no,v_employee_no,v_branch_no);
    end loop;
close c1;
end;
/

i get the below error when executing the procedure p_update_orders_grossSales:

Error starting at line : 62 in command - BEGIN p_update_orders_grossSales; END; Error report - ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "OES2.T_UPDATE_ORDERS_GROSS", line 5 ORA-04088: error during execution of trigger 'OES2.T_UPDATE_ORDERS_GROSS' ORA-06512: at "OES2.P_UPDATE_ORDERS_GROSSSALES", line 22 ORA-06512: at line 1 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested

Why is the error beign generated, if i insert one row at a time the trigger is working but i want to insert many records with one procedure and trigger should be fired to calculate the subtotal for the row being inserted in order table before inserting row in orders_update table.

1 answer

  • answered 2018-07-11 05:09 Belayer

    Oracle raises error ORA-01422 when a "select into" returns more that 1 row. In this case the select from orders table will return every row in the orders table since there is no WHERE. You need to add the same where clause that's on the update orders statement. You probably need to prepare for a "no data found exception".

    Belayer