我想知道这个问题是什么问题,当我执行存储过程时,它不断向我显示此错误。我的目标是在使用存储过程将数据更新到表中之前,我想检查用户输入的触发器中的错误
SQL> exec PRC_PAY_TRIP(1031,2031,'Cash',200)
BEGIN PRC_PAY_TRIP(1031,2031,'Cash',200); END;
*
ERROR at line 1:
ORA-04091: table TEST.PAYMENT is mutating, trigger/function may not see it
ORA-06512: at "TEST.TRG_PAYMENT_VALIDATION", line 10
ORA-04088: error during execution of trigger 'TEST.TRG_PAYMENT_VALIDATION'
ORA-06512: at "TEST.PRC_PAY_TRIP", line 45
ORA-06512: at line 1
我去搜索问题,解决方案之一就是简单地将触发器内的“之后”更改为“之前”
“付款更新之前”-->像这样
但还是不行
存储过程
CREATE OR REPLACE PROCEDURE PRC_PAY_TRIP(CUST_ID IN NUMBER,PAYMENT_ID IN NUMBER,PAYMENT_TYPE IN VARCHAR2,AMT_PAY IN NUMBER)AS
v_paymentstatus VARCHAR2(15) := 'Paid';
v_temppaymentid NUMBER(4) := PAYMENT_ID;
v_truenumber NUMBER(10);
no_null_on_custID EXCEPTION;
no_null_on_payID EXCEPTION;
invalid_paymentid EXCEPTION;
invalid_paymenttype EXCEPTION;
invalid_paymentamt EXCEPTION;
BEGIN
v_truenumber := v_temppaymentid-1000;
IF CUST_ID < 0
THEN
RAISE no_null_on_custID;
END IF;
IF PAYMENT_ID < 0
THEN
RAISE no_null_on_payID;
END IF;
IF CUST_ID ^= v_truenumber THEN
RAISE invalid_paymentid;
END IF;
IF PAYMENT_TYPE ^= 'Cash' AND PAYMENT_TYPE ^= 'E-Wallet' THEN
RAISE invalid_paymenttype;
END IF;
IF AMT_PAY < 0 THEN
RAISE invalid_paymentamt;
END IF;
UPDATE Payment
SET paymenttype = PAYMENT_TYPE,paymentdate = TO_CHAR(sysdate,'DD/MON/YYYY'), paymentstatus = v_paymentstatus
where paymentid = PAYMENT_ID;
EXCEPTION
WHEN no_null_on_custID then
DBMS_OUTPUT.PUT_LINE('Invalid Customer ID');
WHEN no_null_on_payID then
DBMS_OUTPUT.PUT_LINE('Invalid Payment ID');
WHEN invalid_paymenttype then
DBMS_OUTPUT.PUT_LINE('You can either choose Cash or E-Wallet only!');
WHEN invalid_paymentid then
DBMS_OUTPUT.PUT_LINE('Payment id is not yours, just add 1000 from your Customer ID and that will be your Payment ID');
WHEN invalid_paymentamt then
DBMS_OUTPUT.PUT_LINE('Amount pay cannot be negative value!');
END;
/
TRIGGER
CREATE OR REPLACE trigger trg_payment_validation
before update on payment
for each row
DECLARE
v_paymentstatus payment.paymentstatus%type;
v_customerid booking.customerid%type;
v_paymentid payment.paymentid%type;
v_paymentamt payment.paymentamount%type;
BEGIN
select po.paymentstatus into v_paymentstatus
from payment po
where po.paymentid = :new.paymentid;
select po.paymentamount into v_paymentamt
from payment po
where po.paymentid = :new.paymentid;
if SQL%FOUND then
if v_paymentstatus = 'Paid' then
RAISE_APPLICATION_ERROR(
-20950,
'You already paid for your trip, have a nice day'
);
elsif v_paymentamt < :old.paymentamount then
RAISE_APPLICATION_ERROR(
-20950,
'Insufficient amount entered, pls pay the exact amount'
);
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/