我有一个任务要做,我必须在oracle SQL Developer中使用语句触发器实现错误触发器。我的数据表目前看起来像这样:
annumber | EXTRACT(YEARFROMAPPDATE) | APPCOUNT | 2 | 1999 | 2 | 4
---|---|---|
2000 | 2 |
触发器不会触发两次。我猜错误堆栈中重复的ORA-06512消息是由于编译器内部结构代码的方式。也许有一种方法可以进行深度跟踪并找到它的底部,但我会忽略它。
触发器可以简化为以下内容,以避免循环,从而避免双重6512错误:
create or replace trigger application_count_constraint
before update or insert on applies
declare
l_appcount number;
begin
-- Detect case where more than 3 applications exist
-- (assumes there were none before the current transaction and no other sessions are inserting/updating)
select count(*) into l_appcount
from applies
group by anumber, to_char(appdate,'YYYY') having count(*) > 3
order by count(*) desc
fetch first row only;
if l_appcount > 0 then
raise_application_error(-20001, 'An applicant can not have more than 3 applications');
end if;
exception
when no_data_found then null;
end;
编辑:正如@ThorstenKettner所指出的那样,使用ORA-04091: table正在为多行插入改变时,尝试使用行级触发器会失败,因此下面的版本将不起作用。此外,正如我在下面提到的,在两个会话中并发插入很容易绕过这个问题。
create or replace trigger this_wont_work
before update or insert on applies for each row
declare
appcount number := 0;
begin
select count(*) into appcount
from applies
where anumber = :new.anumber
and trunc(appdate,'YEAR') = trunc(:new.appdate,'YEAR');
if appcount >= 3 then
raise_application_error(-20001, 'Applicant '||:new.anumber||' already has '||appcount||' applications in year '||to_char(:new.appdate,'YYYY'));
end if;
end;
但是,如果每个会话都插入了一行或两行,然后稍后提交,那么两个会话可以很容易地绕过检查,因为检查将只看到提交的行。因此,您可能需要以某种方式查看序列化更新(最简单的方法可能是在触发器开始时使用lock table applies in exclusive mode;
),或者创建一个提交时实体化视图,该视图聚合数据并对total count列具有检查约束(有点练习,我还没有尝试过)。