SQL 带有 If 语句 AND OR 条件的 Oracle 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21214414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Trigger with If Statement AND OR conditions
提问by fireboy0526
I'm trying to get an oracle trigger to work.
我正在尝试让 oracle 触发器工作。
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 语句，它只能像这样工作：
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.
如果我在 If 语句中有 OR 条件，则代码将永远无法工作。它会毫无问题地编译触发器，但是当我尝试更新员工表时，它会不断显示 RAISE_APPLICATION_ERROR。
Is there anyway I can fix this?
无论如何我可以解决这个问题吗？
Thanks in advance
提前致谢
回答by gromi08
Replace OR with NOT IN:
将 OR 替换为 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;
