Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    18

    Unanswered: How to catch no_data_found exception with trigger

    Hia,

    The following trigger works fine but the line:

    SELECT ENDDATE INTO T_EDATE FROM TOUR WHERE TOURID=:NEW.TOURID;

    may result in no data being found. How can I capture this event within the following trigger and raise an error message ?

    Thanks in adv !

    Greg

    CREATE OR REPLACE TRIGGER DESTINATION_TG
    BEFORE INSERT ON DESTINATION
    FOR EACH ROW
    DECLARE T_SDATE DATE; T_EDATE DATE;
    BEGIN
    SELECT STARTDATE INTO T_SDATE FROM TOUR WHERE TOURID=:new.tourid;
    SELECT ENDDATE INTO T_EDATE FROM TOUR WHERE TOURID=:NEW.TOURID;
    IF TRUNC(:NEW.ARRIVEDATE)<TRUNC(T_SDATE) THEN
    RAISE_APPLICATION_ERROR(-20100,'Destination arrivedate cannot be before tour start date');
    ELSIF TRUNC(:NEW.ARRIVEDATE)>TRUNC(T_EDATE) THEN
    RAISE_APPLICATION_ERROR(-20100,'Destination arrivedate cannot be after tour end date');
    ELSIF TRUNC(:NEW.DEPARTDATE)<(:NEW.arrivedate) THEN
    RAISE_APPLICATION_ERROR(-20100,'Destination departdate cannot be before destination startdate');
    ELSIF TRUNC(:NEW.DEPARTDATE)>TRUNC(T_EDATE) THEN
    RAISE_APPLICATION_ERROR(-20100,'Destination departdate cannot be after tour enddate');
    ELSIF :NEW.NONIGHTS>(TRUNC(:NEW.DEPARTDATE)-TRUNC(:NEW.ARRIVEDATE)) THEN
    RAISE_APPLICATION_ERROR(-20100,'NoNights cannot be greater than difference between arrivedate and departdate');
    ELSIF (TRUNC(:NEW.DEPARTDATE)-TRUNC(:NEW.ARRIVEDATE))-(:NEW.NONIGHTS)>1 THEN
    RAISE_APPLICATION_ERROR(-20100,'Please revise number of nights entry');
    END IF;
    END;

  2. #2
    Join Date
    Oct 2005
    Posts
    18
    Hey I managed to find the answer. Using

    EXCEPTION
    when no_data_found then
    raise_application_error(-20001, 'A tour has not yet been created with given TourID');

    Thanks anyway

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Regarding your message, it seems that you trapped only first SELECT statement (STARTDATE); I guess that tour is created when it has STARTDATE, but has not finished until ENDDATE exists. So, if the second SELECT fails, this message will be confusing.

    If it doesn't matter (which one failed), just change the message to suit both needs. If not, encapsulate every SELECT statement into its own BEGIN-END PL/SQL block and use distinct EXCEPTION handler for each of them.

    And if you just don't want to bother with NO-DATA-FOUND, it is possible (question is: is it correct way to handle this?) to avoid writing EXCEPTIONs by, for example,

    SELECT MAX(startdate) INTO ...

    as it will NOT raise NO-DATA-FOUND error.

  4. #4
    Join Date
    Oct 2005
    Posts
    18
    An excellent reply that has helped me sort out both posts ! Thanks

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also, why are you doing two selects from the same table. Instead of

    SELECT STARTDATE INTO T_SDATE FROM TOUR WHERE TOURID=:new.tourid;
    SELECT ENDDATE INTO T_EDATE FROM TOUR WHERE TOURID=:NEW.TOURID;

    Write your trigger as follows


    Code:
    CREATE OR REPLACE TRIGGER DESTINATION_TG
    BEFORE INSERT ON DESTINATION
    FOR EACH ROW
    DECLARE T_SDATE DATE; T_EDATE DATE;
    BEGIN
      begin
      SELECT STARTDATE,ENDDATE INTO T_SDATE,T_EDATE FROM TOUR WHERE TOURID=:new.tourid;
      exception
         when others then
         t_sdate := null;
         t_edate := null;
      end;
      IF TRUNC(:NEW.ARRIVEDATE)<TRUNC(T_SDATE) THEN
        RAISE_APPLICATION_ERROR(-20100,'Destination arrivedate cannot be before tour start date');
      ELSIF TRUNC(:NEW.ARRIVEDATE)>TRUNC(T_EDATE) THEN
        RAISE_APPLICATION_ERROR(-20100,'Destination arrivedate cannot be after tour end date');
      ELSIF TRUNC(:NEW.DEPARTDATE)<(:NEW.arrivedate) THEN
        RAISE_APPLICATION_ERROR(-20100,'Destination departdate cannot be before destination startdate');
      ELSIF TRUNC(:NEW.DEPARTDATE)>TRUNC(T_EDATE) THEN
        RAISE_APPLICATION_ERROR(-20100,'Destination departdate cannot be after tour enddate');
      ELSIF  :NEW.NONIGHTS>(TRUNC(:NEW.DEPARTDATE)-TRUNC(:NEW.ARRIVEDATE)) THEN
      RAISE_APPLICATION_ERROR(-20100,'NoNights cannot be greater than difference between arrivedate and departdate');
      ELSIF (TRUNC(:NEW.DEPARTDATE)-TRUNC(:NEW.ARRIVEDATE))-(:NEW.NONIGHTS)>1 THEN
      RAISE_APPLICATION_ERROR(-20100,'Please revise number of nights entry');
    END IF;
    END;
    Never do multiple selects when one will do. It slows down your application.
    Last edited by beilstwh; 11-14-05 at 09:49.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •