我写的触发器为何有时好使有时不好使 |
尚未結案
|
Michael_f
一般會員 發表:24 回覆:19 積分:8 註冊:2004-08-27 發送簡訊給我 |
create or replace trigger da_gzbase
after insert or update or delete on rlzy.rs_da_ryda
for each row
declare
sGzsj varchar2(60);
nGlsj number(8);
n_id number(16);
begin
if inserting then
begin
sGzsj := to_char(:new.gzsj,'yyyymm');
nGlsj := to_number(to_char(:new.glsj,'yyyy'));
if nGlsj is null then
nGlsj := 0;
end if;
select id into n_id from t_um_department where id_base = :new.jgid;
insert into gz_t_base (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (:new.id, n_id, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
end;
elsif updating then
begin
sGzsj := to_char(:new.gzsj,'yyyymm');
nGlsj := to_number(to_char(:new.glsj,'yyyy'));
if nGlsj is null then
nGlsj := 0;
end if;
select id into n_id from t_um_department where id_base = :new.jgid;
update gz_t_base set xm =:new.xm, yxm = :new.yxm,
xb = to_char(:new.xbid), bxh = :new.bxh, c05 = sGzsj, n07 = nGlsj, n02 = :new.xzjbid,
n05 = :new.ryflid, n01 = :new.jszwid, c03 = :new.sfzh, c09 = :new.sfzh
where id = :old.id;
end;
elsif deleting then
delete from gz_t_base where id = :old.id;
end if;
end;
/ 编译通过。为何有时好使有时不好使,提示错误:不能增加、修改因为trigger da_gzbase Try my best!
------
Try my best! |
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
Hi Michael_f, 1.建議在 Select xxx into xxx from xxx where .... 語法中加入例外處理避免錯誤(Too Many Rows,No Data Found ...)
2.Insrte 指令也有可能出錯(Key Violation ...)
create or replace trigger da_gzbase after insert or update or delete on rlzy.rs_da_ryda for each row declare sGzsj varchar2(60); nGlsj number(8); n_id number(16); begin if inserting then begin sGzsj := to_char(:new.gzsj,'yyyymm'); nGlsj := to_number(to_char(:new.glsj,'yyyy')); if nGlsj is null then nGlsj := 0; end if; begin select id into n_id from t_um_department where id_base = :new.jgid; exception when others then id := 0; end; begin insert into gz_t_base (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09) values (:new.id, n_id, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj, :new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh); exception when others then null; end; end; elsif updating then begin sGzsj := to_char(:new.gzsj,'yyyymm'); nGlsj := to_number(to_char(:new.glsj,'yyyy')); if nGlsj is null then nGlsj := 0; end if; begin select id into n_id from t_um_department where id_base = :new.jgid; exception when others then id := 0; end; update gz_t_base set xm =:new.xm, yxm = :new.yxm, xb = to_char(:new.xbid), bxh = :new.bxh, c05 = sGzsj, n07 = nGlsj, n02 = :new.xzjbid, n05 = :new.ryflid, n01 = :new.jszwid, c03 = :new.sfzh, c09 = :new.sfzh where id = :old.id; end; elsif deleting then delete from gz_t_base where id = :old.id; end if; end;---------------------------------- 小弟才疏學淺,若有謬誤尚請不吝指教 ----------------------------------
------
Fishman |
Michael_f
一般會員 發表:24 回覆:19 積分:8 註冊:2004-08-27 發送簡訊給我 |
|
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
|
Michael_f
一般會員 發表:24 回覆:19 積分:8 註冊:2004-08-27 發送簡訊給我 |
再帮我看看这个触发器,提示的错误和上面的一样,有时好用有时不好用,但没提示(Too Many Rows,No Data Found ...) create or replace trigger jg_department
after insert or update or delete on rlzy.rs_xx_jg
for each row
declare
nid number(16);
maxid number(16);
njb number(16);
sbs VARCHAR2(2);
begin
if inserting then
begin
njb := (:new.jb);
if njb = 2 then
begin
select id into nid from t_um_department where id_base = (:new.sjid) and pt_id = 0;
select max(t_um_department.id) 1 into maxid from t_um_department;
insert into t_um_department (id, da_dm, name, p_order, note, dep_dm, da_sjid, f_id, id_base, sign, pt_id)
values (maxid, :new.bm, :new.mc, :new.yxm, :new.jb,
substr(:new.bm,1,2),:new.sjid, nid, :new.id, :new.bs, 0);
end;
elsif njb = 1 then
begin
select max(t_um_department.id) 1 into maxid from t_um_department;
insert into t_um_department (id, da_dm, name, p_order, note, dep_dm, da_sjid, f_id, id_base, sign, pt_id)
values (maxid, :new.bm, :new.mc, :new.yxm, :new.jb,
substr(:new.bm,1,2),:new.sjid, 1, :new.id, :new.bs, 0);
end;
end if;
end;
elsif updating then
njb := (:new.jb);
if njb = 2 then
begin
select id into nid from t_um_department where id_base = (:new.sjid) and pt_id = 0;
update t_um_department set da_dm = :new.bm, name =:new.mc, p_order = :new.yxm,
note = :new.jb, f_id = nid, dep_dm = substr(:new.bm,1,2), da_sjid = :new.sjid, sign = :new.bs,
pt_id = 0
where id_base = :old.id;
end;
elsif njb = 1 then
begin
update t_um_department set da_dm = :new.bm, name =:new.mc, p_order = :new.yxm,
note = :new.jb, f_id = 1, dep_dm = substr(:new.bm,1,2), da_sjid = :new.sjid, sign = :new.bs,
pt_id = 0
where id_base = :old.id;
end;
end if;
sbs := (:new.bs);
if sbs = '2' then
delete from t_um_department where id_base = :old.id and pt_id = 0;
end if;
elsif deleting then
delete from t_um_department where id_base = :old.id and pt_id = 0;
end if;
end;
/ Try my best!
------
Try my best! |
Fishman
尊榮會員 發表:120 回覆:1949 積分:2163 註冊:2006-10-28 發送簡訊給我 |
Hi Michael_f, 1.除非你可以確定 id_base + pt_id 為 t_um_department 之 Unique Key,一定有值且僅有一筆,否則
select id into nid from t_um_department where id_base = (:new.sjid) and pt_id = 0;
可能即會產生 TOO_MANY_ROWS 或 NO_DATA_FOUND 的 ERROR,建議更改為以下寫法,作例外處理
BEGIN SELECT ID INTO NID FROM T_UM_DEPARTMENT WHERE ID_BASE = (:NEW.SJID) AND PT_ID = 0; EXCEPTION WHEN TOO_MANY_ROWS THEN TOO_ MANY_ ROWS PROCESS WHEN NO_DATA_FOUND THEN NO_DATA_FOUND PROCESS WHEN OTHERS THEN OTHERS PROCESS; END;2.select max(t_um_department.id) 1 into maxid from t_um_department;建議更改為 SELECT NVL(MAX(T_UM_DEPARTMENT.ID),0) 1 INTO MAXID FROM T_UM_DEPARTMENT;以避免當 T_UM_DEPARTMENT 內無任何資料時無 MAXID 的狀況 3.確認你的 INSERT 與 UPDATE 指令,不會出現例如型別錯誤、長度過長、鍵值重複‧‧‧等問題 ---------------------------------- 小弟才疏學淺,若有謬誤尚請不吝指教 ----------------------------------
------
Fishman |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |