我正在練習我在 PL/SQL 中學到的知識我有一個表“客戶端”，其中包含：
客戶（身份證、姓名、姓氏、電子郵件、城市、電話、傭金、工資）
該委員會應始終低于工資
我被要求在插入和更新之前創建一個 oracle 觸發器以確保傭金 < 工資， 所以我所做的是以下
Create Trigger verifySalary
Before insert, update
ON Client
for each row
begin
if :new.salary < :new.comm then
raise_application_error(-20555, "commission should be lower than salary");
end if
end
我不確定這是否正確，因為如果用戶沒有更新工資和傭金或只更新這兩列之一，那么 :new.salary 和 :new.commission 的值會是多少？
我應該如何進行？先感謝您
uj5u.com熱心網友回復：
您發布的觸發代碼無效。固定后（并使用應用NVL功能），它看起來像這樣：
SQL> create table client (name varchar2(10), commision number, salary number);
Table created.
SQL> create or replace trigger verifysalary
2 before insert or update on client
3 for each row
4 begin
5 if nvl(:new.salary, 0) < nvl(:new.commision, 0) then
6 raise_application_error(-20555, 'commision should be lower than salary');
7 end if;
8 end;
9 /
Trigger created.
測驗：
SQL> insert into client (name, commision, salary) values ('Little', 10, null);
insert into client (name, commision, salary) values ('Little', 10, null)
*
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'
SQL> insert into client (name, commision, salary) values ('Little', 10, 100);
1 row created.
SQL> update client set commision = 50;
1 row updated.
SQL> update client set commision = 500;
update client set commision = 500
*
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'
SQL> select * from client;
NAME COMMISION SALARY
---------- ---------- ----------
Little 50 100
SQL> update client set salary = null;
update client set salary = null
*
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'
SQL> update client set salary = 10;
update client set salary = 10
*
ERROR at line 1:
ORA-20555: commision should be lower than salary
ORA-06512: at "SCOTT.VERIFYSALARY", line 3
ORA-04088: error during execution of trigger 'SCOTT.VERIFYSALARY'
SQL>
對我來說看起來不錯。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/398202.html
上一篇：OracleXMLTABLE函式。如何合并具有相同名稱的節點？
下一篇：從同一行的不同列中選擇n個最高值