--需求
? 一張自關(guān)聯(lián)的父子表,實現(xiàn)刪除子記錄時判斷父記錄下是否還有子記錄,如果沒有子記錄,則更新父記錄。
--建表
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);
--插數(shù)據(jù)
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;
--觸發(fā)器
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;??????? --設(shè)為自治事務(wù),從而避免ORA-14552(無法在一個查詢或DML中執(zhí)行DDL、COMMIT、ROLLBACK)、ORA-14551(無法在一個查詢中執(zhí)行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;
--觸發(fā)條件
delete from test_sj where id in (11);???? -- 一次刪除父的部分子時正常
delete from test_sj where id in (11,12);? -- 一次刪除父的所有子時存在問題,問題在于:行級觸發(fā)器每次只能傳進(jìn)一行記錄,判斷時總有一行處于“存在狀態(tài)”,可以用語句級觸發(fā)器試試,或者在應(yīng)用程序里寫個循環(huán),為每層循環(huán)開啟一個事務(wù)。
delete from test_sj where id in (21);???? -- 一次刪除父的唯一子時正常
--查詢
select * from test_sj;
--語句級觸發(fā)器
1.用包頭來定義數(shù)據(jù)結(jié)構(gòu)
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.行級觸發(fā)器
Create Or Replace Trigger Tri_test_sj_ad_r
?After Delete On test_sj for each row
Declare
Begin
?--[記錄數(shù)據(jù)]--
? 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.語句級觸發(fā)器
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
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
