我正在学习如何制作PL/SQL触发器。这就是我编码的，但我会遇到很多错误

Question

这是我的教练给我的描述

为“工作”表创建一个触发器，该表将启动插入或更新或删除在“工作”表上执行的操作。该触发器将显示旧值和新值之间的薪水差异。编写一个集合PL/SQL以测试触发器的执行，以响应某些数据库操作（DML）语句（删除，插入或更新）。

这是代码：

create or replace trigger salary_diff
before delete or insert or update on Worker
for each row 
when (new.Worker_id > 0 )

declare
sal_diff number;
begin
sal_diff := :new.salary - :old.salary;
dbms_output.put_line('new salary ' ||  :new.salar)
dbms_output.put_line('old salary ' ||  :old.salary)
dbms_output.put_line('difference between salary is ' || sal_diff);
end;

Answer 1

您提到了“吨错误”，但是 - 实际上，只有两个：

• 一个防止触发的编译（dbms_output.put_line呼叫之后丢失的semi -colon）
• 另一个会阻止触发时触发删除行（因为 - 在这种情况下 - 没有：任何列的new值），因此 - 条款

so：示例表：

SQL> CREATE TABLE worker
  2  AS
  3     SELECT 1 worker_id, 100 salary FROM DUAL;

Table created.

触发器：testing：

SQL> CREATE OR REPLACE TRIGGER salary_diff
  2     BEFORE DELETE OR INSERT OR UPDATE
  3     ON Worker
  4     FOR EACH ROW
  5  DECLARE
  6     sal_diff  NUMBER;
  7  BEGIN
  8     sal_diff := :new.salary - :old.salary;
  9     DBMS_OUTPUT.put_line ('new salary ' || :new.salary);
 10     DBMS_OUTPUT.put_line ('old salary ' || :old.salary);
 11     DBMS_OUTPUT.put_line ('difference between salary is ' || sal_diff);
 12  END;
 13  /

Trigger created.

testing：

SQL> SET SERVEROUTPUT ON
SQL> UPDATE worker
  2     SET salary = 50
  3   WHERE worker_id = 1;
new salary 50
old salary 100
difference between salary is -50

1 row updated.

SQL> DELETE FROM worker
  2    WHERE worker_id = 1;
new salary
old salary 50
difference between salary is

1 row deleted.

SQL>

（删除时，没有“差异”，因为工人不再存在。