Trigger if-else statement error not being raised

Question

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!

Procedure

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

Answer 1

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;

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

---

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.

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.