Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table payment (paymentid number, paymenttype varchar2(10), paymentdate date, paymentamount number, paymentstatus varchar2(6));
insert into payment values (2031, '-', null, 89.99, 'Unpaid');
1 rows affected

commit;
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;
/
update payment set paymentamount = 50 where paymentid = 2031
ORA-20950: Insufficient amount entered, pls pay the exact amount
ORA-06512: at "FIDDLE_PBJCANHJUGSSEMQEYILJ.TRG_PAYMENT_VALIDATION", line 5
ORA-04088: error during execution of trigger 'FIDDLE_PBJCANHJUGSSEMQEYILJ.TRG_PAYMENT_VALIDATION'

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;
/
begin
  dbms_output.enable;
  PRC_PAY_TRIP(1031, 2031, 'Cash', 50);
end;
/
1 rows affected

select * from payment


PAYMENTID
PAYMENTTYPE
PAYMENTDATE
PAYMENTAMOUNT
PAYMENTSTATUS


2031
Cash
23-AUG-21
89.99
Paid


rollback;
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'),
  paymentamount = amt_pay, ---- added this
  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;
/
begin
  dbms_output.enable;
  PRC_PAY_TRIP(1031, 2031, 'Cash', 50);
end;
/
ORA-20950: Insufficient amount entered, pls pay the exact amount
ORA-06512: at "FIDDLE_PBJCANHJUGSSEMQEYILJ.TRG_PAYMENT_VALIDATION", line 5
ORA-04088: error during execution of trigger 'FIDDLE_PBJCANHJUGSSEMQEYILJ.TRG_PAYMENT_VALIDATION'
ORA-06512: at "FIDDLE_PBJCANHJUGSSEMQEYILJ.PRC_PAY_TRIP", line 45
ORA-06512: at line 3

select * from payment


PAYMENTID
PAYMENTTYPE
PAYMENTDATE
PAYMENTAMOUNT
PAYMENTSTATUS


2031
-
null
89.99
Unpaid