由于表发生变化,触发器无法执行 [英] Trigger could not be executed due to table mutating

查看:49
本文介绍了由于表发生变化,触发器无法执行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想知道这个问题的问题是什么,当我执行我的存储过程时它一直向我显示这个错误.我的目标是在使用我的存储过程将数据更新到我的表中之前,我想检查用户输入的触发器中的错误

I want to know what is the problem for this issue whereby it kept on showing me this error when I execute my stored procedure. My goal is to before updating data into my table using my stored procedure, I want to check errors from trigger that the user input

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

我去搜索问题,解决方案之一就是在触发器内将之后"更改为之前"

I went to search the problem and one of the solution was simply change 'after' to 'before' within the trigger

付款更新之前"-->像这样

"before update on payment" --> like this

但是还是不行

存储过程

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

触发

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

推荐答案

如果您在更新的同一行中检查数据,则不要使用 SELECT 语句,而是检查 :NEW:OLD 绑定变量:

If you are checking data in the same row as you are updating then don't use a SELECT statement, instead check the :NEW and :OLD bind variables:

CREATE OR REPLACE trigger trg_payment_validation
  before update on payment
  for each row
BEGIN 
  if :OLD.paymentstatus = 'Paid' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'You already paid for your trip, have a nice day'
    );
  elsif :NEW.paymentamount < :old.paymentamount then
    RAISE_APPLICATION_ERROR(
      -20950,
      'Insufficient amount entered, pls pay the exact amount'
    );
  end if;
END;
/

这篇关于由于表发生变化,触发器无法执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆