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

    Unanswered: no data found problem - trigger

    Hia,

    The two select statements in the trigger below are fine as long as there are related entries in the two tables they use but I want the inserts to be inserted even if the select statements return nothing into the variables as it will mean the insert will have nothing to conflict with. What im asking is can I code the trigger to ignore the rest of the trigger if the select statement return nothing to the variables ?

    Hope the above makes sense and thanks in advance

    Greg

    CREATE OR REPLACE TRIGGER TOUR_TEACHER_TG
    BEFORE INSERT ON TOUR_TEACHER
    FOR EACH ROW
    DECLARE CONFLICT NUMBER; SCHOOLIDCONF NUMBER;
    BEGIN
    SELECT DISTINCT(T.SCHOOLID) INTO CONFLICT FROM TEACHER T JOIN TOUR_TEACHER A ON T.TEACHERID=A.TEACHERID WHERE :NEW.TOURID=A.TOURID;
    SELECT SCHOOLID INTO SCHOOLIDCONF FROM TEACHER T JOIN TOUR_TEACHER A ON T.TEACHERID=A.TEACHERID WHERE :NEW.TEACHERID=T.TEACHERID;
    IF CONFLICT!=SCHOOLIDCONF THEN
    RAISE_APPLICATION_ERROR(-20100,'This Teacher is from a different school than other allocated teachers');
    END IF;
    END;
    Last edited by coxy; 11-12-05 at 08:54.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You opened another thread for the same problem; do as suggested in post #3 here.

Posting Permissions

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