时间戳

首页 > 解决方案 > Plsql：如何使用语句级触发器编写此触发器？

oracle - Plsql：如何使用语句级触发器编写此触发器？

问题描述

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

这是问题：

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

“一个职位不能超过4个技能”。

准备好后，将 CREATE TRIGGER 语句和所有全面测试触发器的 SQL 语句保存在脚本 solution3.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 的语句进行插入或更新。但现在它符合错误：新旧不能用于表级触发器。

新解决方案：

表 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'

标签： oracleplsqldatabase-trigger

解决方案

语句触发器（没有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;

祝你好运。

推荐阅读