编写Worker表PL/SQL薪资差触发器大量报错 求排查修正方案

原代码存在的错误 #

• 拼写错误：:new.salar 字段名拼写缺失末尾的y，正确字段名应为:new.salary
• 语法错误：第一行dbms_output.put_line语句末尾缺少分号，PL/SQL要求每条可执行语句必须以分号结尾
• 伪记录逻辑错误：
  • INSERT操作触发时不存在:old伪记录，对应字段值全为NULL
  • DELETE操作触发时不存在:new伪记录，对应字段值全为NULL
  • 直接执行:new.salary - :old.salary在插入、删除场景下会得到空值，无法输出正确差值
• WHEN子句逻辑缺陷：DELETE操作时:new.Worker_id为NULL，new.Worker_id > 0判断结果为假，会导致DELETE操作完全无法触发触发器，不符合作业覆盖三类DML操作的要求
• 输出逻辑未区分DML类型：插入场景下无旧薪资、删除场景下无新薪资，原代码无分支判断会输出无意义的空值内容

修正后完整触发器代码 #

create or replace trigger salary_diff
before delete or insert or update on Worker
for each row 
declare
sal_diff number;
begin
  -- 针对不同DML类型做分支处理
  if INSERTING then
    sal_diff := :new.salary;
    dbms_output.put_line('本次为新增操作，无旧薪资值');
    dbms_output.put_line('新薪资：' || :new.salary);
  elsif DELETING then
    sal_diff := -:old.salary;
    dbms_output.put_line('本次为删除操作，无新薪资值');
    dbms_output.put_line('旧薪资：' || :old.salary);
  elsif UPDATING then
    sal_diff := :new.salary - :old.salary;
    dbms_output.put_line('本次为更新操作');
    dbms_output.put_line('新薪资：' || :new.salary);
    dbms_output.put_line('旧薪资：' || :old.salary);
  end if;
  dbms_output.put_line('薪资变动差值为：' || sal_diff);
  dbms_output.put_line('------------------------');
end;
/

测试操作指导 #

1. 首先打开PL/SQL的输出开关，否则无法看到dbms_output打印的内容，执行命令：

SET SERVEROUTPUT ON;

2. 编写PL/SQL测试逻辑，依次执行三类DML操作验证效果：

-- 测试插入操作
INSERT INTO Worker(Worker_id, salary) VALUES (1, 8000);
-- 测试更新操作
UPDATE Worker SET salary = 10000 WHERE Worker_id = 1;
-- 测试删除操作
DELETE FROM Worker WHERE Worker_id = 1;
-- 测试完成后如果不需要保留测试数据，执行ROLLBACK即可，需要保留则执行COMMIT
ROLLBACK;

3. 执行后即可在输出窗口看到三类操作对应的薪资差值输出，完全符合作业要求。

内容的提问来源于stack exchange，提问作者Darkin