fireboy0526 fireboy0526 - 2 months ago 11
SQL Question

Oracle Trigger with If Statement AND OR conditions

I'm trying to get an oracle trigger to work.

Here's the code I have so far:

CREATE OR REPLACE TRIGGER CHK_SALES_JOB
BEFORE INSERT OR UPDATE OF JOB_ID ON EMPLOYEES
FOR EACH ROW
BEGIN
IF :old.department_id = 80 AND (:new.job_id != 'SA_REP' OR :new.job_id != 'SA_MAN') THEN
RAISE_APPLICATION_ERROR(-20001, 'Sales Department can only have SA_MAN or SA_REP');
END IF;
END;


Here's the update statement:

UPDATE employees
SET job_id = 'SA_REP'
WHERE employee_id = 179;


The problem that I'm facing at the moment is that with the if statement, it will only work like this:

IF :old.department_id = 80 AND :new.job_id != 'SA_REP' THEN
RAISE_APPLICATION_ERROR(-20001, 'Sales Department can only have SA_MAN or SA_REP');
END IF;


If I have the OR condition within the If statement, the code would never work. It will compile the trigger with no problem, but when ever I try to update the employees table, it will continuously display the RAISE_APPLICATION_ERROR.

Is there anyway I can fix this?

Thanks in advance

Answer

Replace OR with NOT IN:

CREATE OR REPLACE TRIGGER CHK_SALES_JOB 
BEFORE INSERT OR UPDATE OF JOB_ID ON EMPLOYEES 
FOR EACH ROW
BEGIN
  IF :old.department_id = 80 AND :new.job_id NOT IN ('SA_REP', 'SA_MAN') THEN 
      RAISE_APPLICATION_ERROR(-20001, 'Sales Department can only have SA_MAN or SA_REP');
  END IF;
END;