我写的触发器为何有时好使有时不好使 |
|
尚未結案
|
|
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 |
發表時間：2004-09-07 10:24:34
IP:61.178.xxx.xxx 未訂閱
系統時間：2024-06-28 22:35:28
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |