目的是在表tbl_class删除一条记录前,递归删除该记录的所有子记录,子记录特征为的其class_up_id等于父记录的class_id
由于触发器不能删除触发它的表,所以我写了一个变量和两个触发器来做这件事。每删除一条记录前,先记录下其class_id(触发器trigger_tbl_class_cas_delete_1),然后再根据class_id删除其所有子记录(触发器trigger_tbl_class_cas_delete_2)。现在运行到触发器trigger_tbl_class_cas_delete_2 ,delete语句式报错:超过递归值的最大级,怎么解决呢?
create or replace package pak_class_up_id is
my_class_up_id VARCHAR2(50);
end pak_class_up_id;
create or replace trigger trigger_tbl_class_cas_delete_1
before delete on tbl_class
for each row
declare
begin
pak_class_up_id.my_class_up_id := :old.class_id ;
end trigger_tbl_class_cas_delete_1;
create or replace trigger trigger_tbl_class_cas_delete_2
before delete on tbl_class
declare begin
if pak_class_up_id.my_class_up_id is not null then
delete from tbl_class
where
tbl_class.class_up_id = pak_class_up_id.my_class_up_id ; pak_class_up_id.my_class_up_id :=null;
end if;end trigger_tbl_class_cas_delete_2;
由于触发器不能删除触发它的表,所以我写了一个变量和两个触发器来做这件事。每删除一条记录前,先记录下其class_id(触发器trigger_tbl_class_cas_delete_1),然后再根据class_id删除其所有子记录(触发器trigger_tbl_class_cas_delete_2)。现在运行到触发器trigger_tbl_class_cas_delete_2 ,delete语句式报错:超过递归值的最大级,怎么解决呢?
create or replace package pak_class_up_id is
my_class_up_id VARCHAR2(50);
end pak_class_up_id;
create or replace trigger trigger_tbl_class_cas_delete_1
before delete on tbl_class
for each row
declare
begin
pak_class_up_id.my_class_up_id := :old.class_id ;
end trigger_tbl_class_cas_delete_1;
create or replace trigger trigger_tbl_class_cas_delete_2
before delete on tbl_class
declare begin
if pak_class_up_id.my_class_up_id is not null then
delete from tbl_class
where
tbl_class.class_up_id = pak_class_up_id.my_class_up_id ; pak_class_up_id.my_class_up_id :=null;
end if;end trigger_tbl_class_cas_delete_2;
你在trigger里有执行删除基表的动作,就像递归自调用一样的,所以报了这个错。
你可以用一个表级触发加个行级触发来实现,在行级trigger把delete的id记下来,在表级trigger里去删除