Oracle Trigger with If Statement AND OR conditions

Question

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

gromi08 · Accepted Answer · 2016-09-29 14:19:03Z

10

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;

edited Sep 29, 2016 at 14:19

answered Jan 19, 2014 at 7:58

gromi08

5153 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

It might have been easier just to replace the or with and.

Camoen Over a year ago

(:new.job_id != 'SA_REP' OR :new.job_id != 'SA_MAN') will evaluate to true as long as one of the conditions is true. Boolean logic indicates that true OR false = true, and true AND false = false. Compare NOT IN ('SA_REP', 'SA_MAN') to the initial OR condition. The initial condition will evaluate to true when :new.job_id = 'SA_REP', since :new.job_id != 'SA_MAN' evaluates to true.

Collectives™ on Stack Overflow

Oracle Trigger with If Statement AND OR conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related