ORA-04088:执行触发器“OES2.t_UPDATE_ORDERS_GROSS”时出错

Question

我创建了触发器:--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;

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;
/

Answer 1

当“select into”返回超过1行时，Oracle引发错误ORA-01422。在这种情况下，select from orders表将返回orders表中的每一行，因为没有Where。您需要添加与update orders语句相同的where子句。您可能需要为“未找到数据异常”做准备。

护卫者