CODEWITHSUNDEEP
sqloracle-databasetriggers
fireboy0526
fireboy0526

Reputation: 139

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

Upvotes: 4

Views: 62952

Answers (1)

gromi08
gromi08

Reputation: 515

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;

Upvotes: 10

Related Questions