在我的触发器里,首先判断记录数是否已超过了100条,如果超过了的话,我就 rollback 它,程序如下,编译时没有错误,但运行时系统提示:can not rollback in a trigger.请问我怎样写这个触发器才能实现:如果纪录大于100条,系统不让插入新纪录?===============================================
create or replace trigger TBID_LNASSIGN before insert or delete on lnassign
for each row
declare
cnt number;
begin
select count(*) into cnt from lnassign;
if cnt > 100 then
rollback;
end if;
end;
create or replace trigger TBID_LNASSIGN before insert or delete on lnassign
for each row
declare
cnt number;
begin
select count(*) into cnt from lnassign;
if cnt > 100 then
rollback;
end if;
end;
在表b的触发器里,定义一个自定义exception,如果记录数大于100,那么raise this exception,在处理这个exception时,调用raise_application_error(<error_number>,<error_message>) 来通知用户不能再继续插入记录了。
====================================
...
declare
a number;
CAPACITY_OVER_CONTROL exception;
PRAGMA EXCEPTION_INIT(CAPACITY_OVER_CONTROL,-20999);
Begin -- get the record number from table a.
select a.b_recnum into a from a;
if inserting then
if a > 100 then
raise CAPACITY_OVER_CONTROL;
end if;
end if;
...
...
exception
when CAPACITY_OVER_CONTROL then
raise_application_error(-20999,'you can not insert new data, please call your administrator!');end;