未引发触发 if-else 语句错误 [英] Trigger if-else statement error not being raised

问题描述

我创建了一个程序和一个触发器，我的目的是支付票价，输入的票价金额必须大于或等于支付表中的确切金额

I have created a procedure and a trigger and my intention is to pay the fares that the amount of fares entered must be more than or equals to the exact amount within the Payment Table

从表中我的意思是，用户需要输入足够的数量才能进行存储过程，如果输入的数量低于该数量，则会引发触发错误！

As from the table what I meant was, user need to enter sufficient amount for the stored procedure to be proceed and if the amount entered is lower than that, trigger errors will be raised!

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
BEGIN 
    if :NEW.paymentamount < :OLD.paymentamount then >>> this error could not be raised even after
                                                        the amount entered is lower than the
                                                        actual amount
        RAISE_APPLICATION_ERROR(
          -20950,
          'Insufficient amount entered, pls pay the exact amount'
        );
    elsif :OLD.paymentstatus = 'Paid' then
        RAISE_APPLICATION_ERROR(
          -20950,
          'You cannot pay the fares as you already paid before this, have a nice day'
        );
    end if;
END;
/

推荐答案

当您手动更新表时，触发器按预期工作.

The trigger works as you expect when you update the table manually.

当从过程中调用时，触发器不会报告无效金额，并且即使未支付也将全额显示为已支付金额 - 支付金额未更改，但其他列不变.

When called from the procedure the trigger doesn't report an invalid amount, and shows the full amount as paid even if it wasn't - the payment amount isn't changed bu the other columns are.

那是因为你的程序的更新语句是:

That's because your procedure's update statement is:

UPDATE Payment
SET paymenttype = PAYMENT_TYPE,
  paymentdate = TO_CHAR(sysdate,'DD/MON/YYYY'),
  paymentstatus = v_paymentstatus
where paymentid = PAYMENT_ID;

您没有告诉它更新数量，因此触发器没有修改过的 :new 值 - 旧的和新的相同.您需要在更新中包含该列:

You are not telling it to update the amount, so the trigger doesn't have a modified :new value - the old and new are the same. You need to include that column in the update:

UPDATE Payment
SET paymenttype = PAYMENT_TYPE,
  paymentdate = TO_CHAR(sysdate,'DD/MON/YYYY'),
  paymentamount = amt_pay,
  paymentstatus = v_paymentstatus
where paymentid = PAYMENT_ID;

db<>fiddle

TO_CHAR(sysdate,'DD/MON/YYYY') 看起来很奇怪 - 表列应该是日期而不是字符串，因此您不应该将该值转换为字符串；如果该列是日期，则您依赖客户端的 NLS 设置将其转换回来.如果您试图忽略当前时间，则可以改为执行 TRUNC(sysdate).

The TO_CHAR(sysdate,'DD/MON/YYYY') looks odd - the table column should be a date not a string, so you shouldn't be converting that value to a string; if the column is a date then you are relying on the client's NLS settings to convert it back. If you're trying to ignore the current time then you can do TRUNC(sysdate) instead.

您也不应该依赖过程主体中的 dbms_output - 您无法控制调用它的人是否启用了输出，因此他们可能永远不会发现问题.当您在触发器中引发异常时，您可以在其他错误的过程中执行相同的操作.

You also shouldn't rely on dbms_output in the procedure body - you can't control whether someone calling this has output enabled so they may never see a problem. As you're raising an exception in the trigger, you could do the same in the procedure for the other errors.

这篇关于未引发触发 if-else 语句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！