我已经创建了一个触发器来检查是否有人更新了工资或使用检查过程在employees表上插入了新员工，下面是我的过程和触发器的代码:
CREATE OR REPLACE PROCEDURE check_salary (pjobid employees.job_id%type, psal employees.salary%type)
IS
BEGIN
FOR i in (SELECT min_salary, max_salary
FROM jobs
WHERE job_id = pjobid)
LOOP
IF psal < i.min_salary OR psal > i.max_salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid salary ' || psal || '. Salaries for job ' || pjobid || ' must be between ' || i.min_salary || ' and ' || i.max_salary);
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is okay!');
END IF;
END LOOP;
END check_salary;
CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (new.salary != old.salary OR new.job_id != old.job_id)
BEGIN
check_salary(:old.job_id, :new.salary);
END check_salary_trg;
现在，当我尝试更新表中已经声明的特定员工的工资(结果是注释的那个)时，它正常工作:
UPDATE employees
SET salary = 2800
WHERE employee_id = 115;
--ORA-20001: Invalid salary 2800. Salaries for job HR_REP must be between 4000 and 9000
但是，我想知道为什么当我手动插入数据时，当插入已经在触发器上声明时，它不会工作
INSERT INTO employees(first_name, last_name, email, department_id, job_id, hire_date) VALUES ('Lorem', 'Ipsum', 'loremipsum', 30, 'SAL_REP', TRUNC(sysdate));
或者当我使用包内的add_employee过程时，下面是代码:
PROCEDURE ADD_EMPLOYEE(vfirstname employees.first_name%type,
vlastname employees.last_name%type,
vdepid employees.department_id%type,
vjob employees.job_id%type := 'SA_REP')
IS
vemail employees.email%type;
BEGIN
vemail := upper(substr(vfirstname, 1, 1)) || upper(substr(vlastname,1,7));
IF valid_deptid(vdepid) THEN
INSERT INTO employees(first_name, last_name, email, department_id, job_id, employee_id, hire_date)
VALUES(vfirstname, vlastname, vemail, vdepid, vjob, employees_seq.nextval, TRUNC(SYSDATE));
COMMIT;
ELSE
RAISE_APPLICATION_ERROR(-20003, 'Invalid Department ID');
END IF;
END ADD_EMPLOYEE;
PROCEDURE ADD_EMPLOYEE(vfirstname employees.first_name%type,
vlast_name employees.last_name%type,
vemail employees.email%type,
vjob employees.job_id%type := 'SA_REP',
vmgr employees.manager_id%type := 145,
vsalary employees.salary%type := 1000,
vcomm employees.commission_pct%type := 0,
vdepid employees.department_id%type := 30)
IS
BEGIN
IF valid_deptid(vdepid) THEN
INSERT INTO employees(first_name, last_name, email, department_id, job_id, manager_id, salary, commission_pct, employee_id, hire_date)
VALUES(vfirstname, vlast_name, vemail, vdepid, vjob, vmgr, vsalary, vcomm, employees_seq.nextval, TRUNC(SYSDATE));
COMMIT;
ELSE
RAISE_APPLICATION_ERROR(-20003, 'Invalid Department ID');
END IF;
END ADD_EMPLOYEE;
祝贺你提出了一个文档完备的问题。
问题在于触发器的WHEN子句。在插入时，旧值是NULL，在oracle中，您不能使用"="与NULL进行比较。或"! ="。
检查这个例子:
PDB1--KOEN>create table trigger_test
2 (name VARCHAR2(10));
Table TRIGGER_TEST created.
PDB1--KOEN>CREATE OR REPLACE trigger trigger_test_t1
2 BEFORE INSERT OR UPDATE ON trigger_test
3 FOR EACH ROW
4 WHEN (new.name != old.name)
5 BEGIN
6 RAISE_APPLICATION_ERROR(-20001, 'Some error !');
7 END trigger_test_t1;
8 /
Trigger TRIGGER_TEST_T1 compiled
PDB1--KOEN>INSERT INTO trigger_test (name) values ('koen');
1 row inserted.
PDB1--KOEN>UPDATE trigger_test set name = 'steven';
Error starting at line : 1 in command -
UPDATE trigger_test set name = 'steven'
Error report -
ORA-20001: Some error !
ORA-06512: at "KOEN.TRIGGER_TEST_T1", line 2
ORA-04088: error during execution of trigger 'KOEN.TRIGGER_TEST_T1'
这正是你在代码中看到的行为。插入后，扳机似乎打不着火。嗯…因为在oracle中，'x' != NULL产生false。请参阅答案底部的信息。这就是证据。让我们用包裹在旧值周围的NVL函数重新创建触发器。
PDB1--KOEN>CREATE OR REPLACE trigger trigger_test_t1
2 BEFORE INSERT OR UPDATE ON trigger_test
3 FOR EACH ROW
4 WHEN (new.name != NVL(old.name,'x'))
5 -- above is similar to this
6 --WHEN (new.name <> old.name or
7 -- (new.name is null and old.name is not NULL) or
8 -- (new.name is not null and old.name is NULL) )
9 BEGIN
10 RAISE_APPLICATION_ERROR(-20001, 'Some error !');
11 END trigger_test_t1;
12 /
Trigger TRIGGER_TEST_T1 compiled
PDB1--KOEN>INSERT INTO trigger_test (name) values ('jennifer');
Error starting at line : 1 in command -
INSERT INTO trigger_test (name) values ('jennifer')
Error report -
ORA-20001: Some error !
ORA-06512: at "KOEN.TRIGGER_TEST_T1", line 2
ORA-04088: error during execution of trigger 'KOEN.TRIGGER_TEST_T1'
好了。现在插入时触发。
为什么会这样呢?根据文件:因为null表示缺乏数据，所以null不能等于或不等于任何值或另一个null。然而，Oracle在计算DECODE函数时认为两个null是相等的。查看文档或阅读这个20年前的关于问题的回答
@KoenLostrie是绝对正确的为什么你的触发器不触发插入。但这只是问题的一半。但是，另一个问题源于同样的误解:NULL值对check_salary的调用传递:old.job_id，但它仍然是NULL，导致游标(for i in (Select ...))在尝试'WHERE job_id = NULL '时不返回行。然而，如果游标没有返回任何行，则没有异常，循环根本就没有进入。您需要传递':new.job_id'。您还需要在Update上设置新的作业id。如果一个员工升职了，更新的内容就像这样:
update employee
set job_id = 1011
, salary = 50000.00
where employee = 115;
最后，处理游标是危险的。这样做至少意味着允许在Jobs中为给定的job_id创建多个行。当这些行具有不同的min_salary和max_salary时会发生什么?您可以更新过程，或者只是在触发器中执行所有操作并消除过程。
create or replace trigger check_salary_trg
before insert or update on employees
for each row
declare
e_invalid_salary_range;
l_job jobs%rowtype;
begin
select *
from jobs
into l_job
where job_id = :new.job_id;
if :new.salary < l_job.min_salary
or :new.salary > l_job.max_salary
then
raise e_invalid_salary_range; ;
end if;
exception
when e_invalid_salary_range then
raise_application_error(-20001, 'Invalid salary ' || psal ||
'. Salaries for job ' || pjobid ||
' must be between ' || l_job.min_salary ||
' and ' || l_job.max_salary
);
end check_salary_trg;
你可以在异常块中添加no_data_found和too_many_rows的处理，但是它们最好用约束来处理。
谢谢你的精彩回答!感谢他们，我学到了很多。
在检查我的代码特别是我的触发器时，我犯了一个错误，将:old.job_id传递给我的check_salary过程而不是:new.job_id，这就是为什么我一直想知道为什么我的INSERT正在工作，即使正在传递的工资低于min_salary和max_salary的工作。现在我的代码是:
check_salary过程:
CREATE OR REPLACE PROCEDURE check_salary (pjobid employees.job_id%type, psal employees.salary%type)
IS
BEGIN
FOR i in (SELECT min_salary, max_salary
FROM jobs
WHERE job_id = pjobid)
LOOP
IF psal < i.min_salary OR psal > i.max_salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid salary ' || psal || '. Salaries for job ' || pjobid || ' must be between ' || i.min_salary || ' and ' || i.max_salary);
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is okay!');
END IF;
END LOOP;
END check_salary;
/
check_salary_trg:
CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (new.salary != NVL(old.salary, 0) OR new.job_id != NVL(old.job_id, 'x'))
BEGIN
check_salary(:new.job_id, :new.salary);
END check_salary_trg;
/
和结果每当我通过插入和更新
传递数据时INSERT INTO employees(employee_id, first_name, last_name, email, department_id, job_id, hire_date, salary)
VALUES (employees_seq.nextval, 'Lorem', 'Ipsum', 'loremipsum', 30, 'SA_REP', TRUNC(sysdate), 5000);
ORA-20001: Invalid salary 5000. Salaries for job SA_REP must be between 6000 and 12008
UPDATE employees
SET job_id = 'ST_MAN'
WHERE last_name = 'Beh'
ORA-20001: Invalid salary 9000. Salaries for job ST_MAN must be between 5500 and 8500
UPDATE employees
SET salary = 2800
WHERE employee_id = 115;
--1 row(s) updated.
--Salary is okay!
-- will work since min_salary and max_salary of PU_CLERK is 2500 and 5500