当触发器没有发现任何错误时，继续执行存储过程

Question

我是Oracle的新手，我想知道如果我的触发器没有检测到任何错误，如何继续运行我的过程。

我只是想继续运行我的过程，如果这两个异常在触发器没有被触发，但当我运行它显示我这个错误

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

<标题>程序

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

<标题>触发

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

Answer 1

捕捉触发器中的NO_DATA_FOUND异常:

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

注意:您不需要检查您是否为INSERTING，因为触发器只会在INSERT期间触发

---

然而，我不确定你应该使用SELECT语句，不应该只是检查:NEW.journeystatus绑定变量，如果你指的是INSERT行:

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