我創建了一個觸發器來檢查是否有人使用檢查程序更新了薪水或在員工表中插入了新員工，下面是我的程序和觸發器的代碼：
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;
uj5u.com熱心網友回復：
祝賀有據可查的問題。
問題在于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 產生錯誤。請參閱此答案底部的資訊。這是證據。讓我們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。但是，在評估 DECODE 函式時，Oracle 認為兩個空值相等。在asktom上這個20 年前的答案中檢查檔案或閱讀它
uj5u.com熱心網友回復：
@KoenLostrie 關于為什么您的觸發器不會在 Insert 上觸發是絕對正確的。但這只是問題的一半。但是，另一個問題源于同樣的誤解： NULL 值呼叫check_salary通過:old.job_id但它仍然為空，導致游標 ( 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例外塊，但使用約束更好地處理它們。
uj5u.com熱心網友回復：
感謝您的精彩回答！感謝他們，我學到了很多。
在檢查我的代碼特別是我的觸發器時，我在將 傳遞: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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/333123.html