Plsql:如何使用语句级触发器编写此触发器？

Question

从昨天开始，我一直在尝试解决这个问题，但是我不知道如何编写一个可以工作的语句级触发器来完成这个任务。

下面是问题：

实现并全面测试验证以下一致性约束的语句触发器。

“一个职位需要的技能不能超过4项”。

准备好后，在脚本解决方案3.sql中保存CREATE TRIGGER语句和所有全面测试触发器的SQL语句。综合测试意味着触发器必须拒绝违反一致性约束的SQL语句，接受不违反一致性约束的SQL语句。查找应该测试哪些SQL语句是您任务的一部分。每当SQL语句违反一致性约束时，触发器必须返回ORA-…。错误消息。使用过程RAISE_APPLICATION_ERROR返回ORA-…错误消息。如果SQL语句没有违反一致性约束，则触发器不能返回任何消息。

如果你想尝试一下这个问题，下面是数据库文件：https://www.dropbox.com/sh/4dyemye2bpq5hv4/AAAEAmTDELHcgFFhpfRrqHtDa?dl=0

我写的代码给了我一个错误，新的和旧的不能在表级触发器上使用，我认为如果我把它设为行级触发器，这个函数会起作用，但我需要它作为语句触发器。你能告诉我我哪里做错了吗。

create or replace trigger checkskillnum
before insert or update of pnumber on sneeded
declare
skillnum number;
begin
select count(*) into skillnum from sneeded where sneeded.pnumber = :new.pnumber;
if skillnum > 3 then
RAISE_APPLICATION_ERROR(-20030,'A position does not need more than 4 skills');
end if;
end;
/

结果应该只允许对skillnum小于4的语句执行insert或update操作。但是现在它符合错误: new和old不能用于表级触发器。

新的解决方案：

表SNEEDED: select语句的第一个结果是4，即使pnumber不同，触发器也只检查该结果。

SQL> select pnumber, count(*) as SKILL_COUNT
  2                   from sneeded
  3                   group by pnumber
  4                   order by COUNT(*) DESC
  5  ;

   PNUMBER SKILL_COUNT
---------- -----------
         1           4
         2           3
         3           3
         7           3
         5           3
         6           1
         4           1

7 rows selected.

新解决方案中的错误：

SQL> insert into sneeded values(1,'fishing',7);
insert into sneeded values(1,'fishing',7)
            *
ERROR at line 1:
ORA-20030: A position does not need more than 4 skills
ORA-06512: at "SYSTEM.CHECKSKILLNUM", line 8
ORA-04088: error during execution of trigger 'SYSTEM.CHECKSKILLNUM'


SQL> insert into sneeded values(2,'fishing',7);
insert into sneeded values(2,'fishing',7)
            *
ERROR at line 1:
ORA-20030: A position does not need more than 4 skills
ORA-06512: at "SYSTEM.CHECKSKILLNUM", line 8
ORA-04088: error during execution of trigger 'SYSTEM.CHECKSKILLNUM'


SQL> insert into sneeded values(4,'fishing',7);
insert into sneeded values(4,'fishing',7)
            *
ERROR at line 1:
ORA-20030: A position does not need more than 4 skills
ORA-06512: at "SYSTEM.CHECKSKILLNUM", line 8
ORA-04088: error during execution of trigger 'SYSTEM.CHECKSKILLNUM'

Answer 1

语句触发器(没有FOR EACH ROW的触发器)不能使用:OLD和:NEW，因为它没有访问行值的权限。但在这种情况下，你并不真的需要它们--你只需要换个方式思考。

您关心的是-在语句执行后，是否有人拥有三种以上的技能？所以你需要弄清楚如何找到这些信息。

下面的代码将为您完成此操作：

create or replace trigger checkskillnum
  before insert or update on sneeded
begin
  FOR aRow IN (select pnumber, count(*) as SKILL_COUNT
                 from sneeded 
                 group by pnumber
                 order by COUNT(*) DESC)
  LOOP
    IF aRow.SKILL_COUNT > 3 THEN
      RAISE_APPLICATION_ERROR(-20030, 'A position does not need more than 4 skills');
    END IF;

    EXIT;    -- The first row will have the highest SKILL_COUNT, so only need to  
  END LOOP;  -- check the first row.
end CHECKSKILLNUM;

祝你好运。