--需求
一张自关联的父子表，实现删除子记录时判断父记录下是否还有子记录，如果没有子记录，则更新父记录。
--建表
create table test_sj (id number(8) not null,p_id number(8),remark varchar2(200));
alter table test_sj add constraint PK_test_sj primary key (id);
alter table test_sj add constraint FK_test_sj foreign key (p_id) references test_sj (id);
--插数据
truncate table test_sj;
insert into test_sj values (0,null,'0');
insert into test_sj values (1,0,'0');
insert into test_sj values (2,0,'0');
insert into test_sj values (11,1,'1');
insert into test_sj values (12,1,'1');
insert into test_sj values (21,2,'2');
commit;
--触发器
create or replace trigger tri_test_sj_ad after delete on test_sj for each row
declare
-- 定义变量
pragma autonomous_transaction;
l_cnt number;
begin
select count(id) into l_cnt from test_sj where p_id=:old.p_id and id <> :old.id;
if l_cnt=0 then
update test_sj set remark='你没有儿子了' where id=:old.p_id;
commit;
end if;
raise_application_error(-20001,'你来了吗?'||l_cnt);
end tri_test_sj_ad;
/
show err;
alter trigger tri_test_sj_ad disable;
create or replace trigger tri_test_sj_bd before delete on test_sj for each row
declare
-- 定义变量
pragma autonomous_transaction; --设为自治事务，从而避免ORA-14552（无法在一个查询或DML中执行DDL、COMMIT、ROLLBACK）、ORA-14551（无法在一个查询中执行DML操作）等错误
l_cnt number;
begin
select count(id) into l_cnt from test_sj where p_id=:old.p_id and id <> :old.id;
if l_cnt=0 then
update test_sj set remark='你没有儿子了' where id=:old.p_id;
commit;
end if;
-- raise_application_error(-20001,'你来了吗?'||l_cnt);
end tri_test_sj_bd;
/
show err;
alter trigger tri_test_sj_bd disable;
--触发条件
delete from test_sj where id in (11); -- 一次删除父的部分子时正常
delete from test_sj where id in (11,12); -- 一次删除父的所有子时存在问题，问题在于：行级触发器每次只能传进一行记录，判断时总有一行处于“存在状态”，可以用语句级触发器试试，或者在应用程序里写个循环，为每层循环开启一个事务。
delete from test_sj where id in (21); -- 一次删除父的唯一子时正常
--查询
select * from test_sj;
--语句级触发器
1.用包头来定义数据结构
CREATE OR REPLACE Package Pkg_test_sj_ids
As
type p_ids is table of test_sj.id%type index by binary_integer;
v_ids p_ids;
v_pids p_ids;
v_NumEntries binary_integer := 0;
End Pkg_test_sj_ids;
/
show err;
2.行级触发器
Create Or Replace Trigger Tri_test_sj_ad_r
After Delete On test_sj for each row
Declare
Begin
--[记录数据]--
Pkg_test_sj_ids.v_NumEntries := Pkg_test_sj_ids.v_NumEntries + 1;
Pkg_test_sj_ids.v_ids(Pkg_test_sj_ids.v_NumEntries) := :old.id;
Pkg_test_sj_ids.v_pids(Pkg_test_sj_ids.v_NumEntries) := :old.p_id;
End Tri_test_sj_ad_r;
/
show err;
3.语句级触发器
Create Or Replace Trigger Tri_test_sj_ad_all
After Delete On test_sj
Declare
l_id test_sj.id%type;
l_pid test_sj.p_id%type;
l_cnt number;
Begin
for v_LoopIndex in 1..Pkg_test_sj_ids.v_NumEntries loop
--[获取变量]--
l_id := Pkg_test_sj_ids.v_ids(v_LoopIndex);
l_pid := Pkg_test_sj_ids.v_pids(v_LoopIndex);
select count(id) into l_cnt from test_sj where p_id=l_id and id <> l_pid;
if l_cnt=0 then
update test_sj set remark='你没有儿子了' where id=l_pid;
-- commit;
end if;
end loop;
Pkg_test_sj_ids.v_NumEntries := 0;
End Tri_test_sj_ad_all;
/
show err;
--测试
delete from test_sj where id in (11,12); -- ok
delete from test_sj where id in (21); -- ok
