Continue with stored procedure when trigger not finding any errors

user_16727443 0
Viewed 69

I'm really new to Oracle, I want to know how to continue to run my procedure if my trigger not detecting any errors.

I just want to continue to run my procedure if those two exception within trigger is not being triggered but when i run it shows me this error

ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "TEST.TRG_BOOKING_VALIDATION", line 9
ORA-04088: error during execution of trigger 'TEST.TRG_BOOKING_VALIDATION'
ORA-06512: at "TEST.PRC_ADD_BOOKING", line 44
ORA-06512: at line 1

PROCEDURE

create sequence bookingidvalue minvalue 1 start with 3031;


CREATE OR REPLACE PROCEDURE PRC_ADD_BOOKING(CUST_ID IN NUMBER,PAYMENT_ID IN NUMBER,FRM_DEST IN VARCHAR2,TO_DEST IN VARCHAR2)AS


v_journeystatus VARCHAR2(15) := 'Pending';
v_bookeddate Booking.bookingdate%TYPE;

no_null_on_custID EXCEPTION;
no_null_on_payID EXCEPTION;
no_null_on_FROM_DEST EXCEPTION;
no_null_on_TO_DEST EXCEPTION;
duplicate_error EXCEPTION;


BEGIN

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 FRM_DEST IS NULL
THEN
RAISE no_null_on_FROM_DEST;
END IF;

IF TO_DEST IS NULL
THEN
RAISE no_null_on_TO_DEST;
END IF;
  
IF FRM_DEST = TO_DEST
THEN
RAISE duplicate_error;
END IF;



INSERT INTO Booking values(bookingidvalue.nextval,sysdate,v_journeystatus,FRM_DEST,TO_DEST,CUST_ID,PAYMENT_ID);
DBMS_OUTPUT.PUT_LINE('Booking has been added');

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 no_null_on_FROM_DEST then
DBMS_OUTPUT.PUT_LINE('From Destination must be filled');

WHEN no_null_on_TO_DEST then
DBMS_OUTPUT.PUT_LINE('To Destination must be filled');

WHEN duplicate_error then
DBMS_OUTPUT.PUT_LINE('From destination cannot same with To destination');


END;
/

TRIGGER

CREATE OR REPLACE trigger trg_booking_validation
 before insert on booking
 for each row

 declare

 v_journeystatus booking.journeystatus%type;
 v_customerid booking.customerid%type;
 v_paymentid booking.paymentid%type;

BEGIN 

select bo.journeystatus into v_journeystatus
 from booking bo
 where bo.customerid = :new.customerid;

if SQL%FOUND then
 case
  when inserting then
   if(v_journeystatus = 'In Journey')then
    RAISE_APPLICATION_ERROR(-20950,'YOU ARE CURRENTLY IN JOURNEY, PLEASE BOOK AGAIN WHEN YOU REACHED');
   end if;
   if(v_journeystatus = 'Pending')then
    RAISE_APPLICATION_ERROR(-20950,'YOUR PREVIOUS BOOKING IS ALREADY ON PENDING! DRIVER WILL SOON PICK YOU UP, BE PATIENCE!');
   end if;
   if(v_journeystatus = NULL)then
    DBMS_OUTPUT.PUT_LINE('N0 errors');//i want to continue to my procedure here
   end if;
end case;
end if;


END;
/
triggers plsql stored-procedures oracle sql
1 Answers

Catch the NO_DATA_FOUND exception in the trigger:

CREATE OR REPLACE trigger trg_booking_validation
before insert on booking
  for each row
declare
  v_journeystatus booking.journeystatus%type;
BEGIN 
  select bo.journeystatus
  into   v_journeystatus
  from   booking bo
  where  bo.customerid = :new.customerid;

  if v_journeystatus = 'In Journey' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOU ARE CURRENTLY IN JOURNEY, PLEASE BOOK AGAIN WHEN YOU REACHED'
    );
  elsif v_journeystatus = 'Pending' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOUR PREVIOUS BOOKING IS ALREADY ON PENDING! DRIVER WILL SOON PICK YOU UP, BE PATIENCE!'
    );
  end if;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

Note: you do not need to check if you are INSERTING as the trigger will only trigger during an INSERT.

However, I'm not sure that you should be using a SELECT statement and should not just be checking the :NEW.journeystatus bind variable if you are referring to the row you are INSERTing:

CREATE OR REPLACE trigger trg_booking_validation
before insert on booking
  for each row
BEGIN 
  if :NEW.journeystatus = 'In Journey' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOU ARE CURRENTLY IN JOURNEY, PLEASE BOOK AGAIN WHEN YOU REACHED'
    );
  elsif :NEW.journeystatus = 'Pending' then
    RAISE_APPLICATION_ERROR(
      -20950,
      'YOUR PREVIOUS BOOKING IS ALREADY ON PENDING! DRIVER WILL SOON PICK YOU UP, BE PATIENCE!'
    );
  end if;
END;
/
user_15092640
Related