--需求

  一张自关联的父子表，实现删除子记录时判断父记录下是否还有子记录，如果没有子记录，则更新父记录。

--建表

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