can anyone help me solve this error

CREATE OR REPLACE TRIGGER INVALID_COURSE 
BEFORE INSERT OR UPDATE ON MARKS 
FOR EACH ROW BEGIN   
IF :NEW.C_ID NOT IN (SELECT C_ID FROM COURSE WHERE B_ID =(SELECT B_ID FROM EMPLOYEE WHERE EMP_ID= :NEW.EMP_ID)) THEN      RAISE_APPLICATION_ERROR(-20001, 'INVALID COURSE FOR THE BATCH');   
END IF; 
END;

CodePudding user response：

Check it first, raise if necessary. Something like this:

CREATE OR REPLACE TRIGGER invalid_course
   BEFORE INSERT OR UPDATE
   ON marks
   FOR EACH ROW
DECLARE
   l_cnt  NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO l_cnt
     FROM course c JOIN employee e ON e.b_id = c.b_id
    WHERE     e.emp_id = :new.emp_id
          AND c.c_id = :new.c_id;

   IF l_cnt = 0
   THEN
      raise_application_error (-20001, 'INVALID COURSE FOR THE BATCH');
   END IF;
END;

CodePudding user response：

You are mixing SQL and PL/SQL. They are different engines with different syntax and vocabulary, though there are similarities and overlaps.

IF is a PL/SQL command, and it does not support an embedded SQL statement as you are trying to do here. You need to separate your SQL from your PL/SQL. Fetch the SQL result into a variable and then use PL/SQL constructs to make decisions based on the contents of that variable, as Littlefoot's answer demonstrates.