请高手看下面的触发器 |
|
尚未結案
|
|
seagal2222
一般會員   發表：24 回覆：48 積分：14 註冊：2003-03-04 發送簡訊給我 |
/*当rlzy.rs_da_rydabak表insert,update,delete时，引起gz_t_basebak表的响应字段变化。
create or replace trigger da_gzbase
after insert or update or delete on rlzy.rs_da_rydabak
for each row
declare
sGzsj varchar2(60);
nGlsj number(8);
begin
if inserting then
begin
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :new.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak
where id = :new.id;
if nGlsj is null then
14 nGlsj := 0;
15 insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09) 16 values (id, :new.jgid, :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
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :old.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak
where id = :old.id;
if nGlsj is null then
nGlsj := 0;
update gz_t_basebak set dp_id = :new.jgid, 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_basebak where id = :old.id;
end if;
end;
/
在sqlplus运行后，提示错误：
15/8 PLS-00103: 出现符号 ";"在需要下列之一时?
if ———————————
抓住快乐！
|
|
Fishman
尊榮會員   發表：120 回覆：1949 積分：2163 註冊：2006-10-28 發送簡訊給我 |
Hi seagal2222,
create or replace trigger da_gzbase
after insert or update or delete on rlzy.rs_da_rydabak
for each row
declare
sGzsj varchar2(60);
nGlsj number(8);
begin
if inserting then
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :new.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak where id = :new.id;
if nGlsj is null then
nGlsj := 0;
insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (id, :new.jgid, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
end if;
elsif updating then
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :old.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak
where id = :old.id;
if nGlsj is null then
nGlsj := 0;
update gz_t_basebak set dp_id = :new.jgid, 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 if;
elsif deleting then
delete from gz_t_basebak where id = :old.id;
end if;
end;
------
Fishman |
|
seagal2222
一般會員 發表：24 回覆：48 積分：14 註冊：2003-03-04 發送簡訊給我 |
非常感谢fishman.但下面这段程序 if nGlsj is null then
nGlsj := 0;
insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (id, :new.jgid, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
end if; 我的意思就是
if nGlsj is null then
nGlsj := 0;
end if;
insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (id, :new.jgid, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
insert语句 不在分支语句之内。 ———————————
抓住快乐！
|
|
Fishman
尊榮會員 發表：120 回覆：1949 積分：2163 註冊：2006-10-28 發送簡訊給我 |
Hi seagal2222,
create or replace trigger da_gzbase
after insert or update or delete on rlzy.rs_da_rydabak
for each row
declare
sGzsj varchar2(60);
nGlsj number(8);
begin
if inserting then
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :new.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak where id = :new.id;
if nGlsj is null then
nGlsj := 0;
end if;
insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (id, :new.jgid, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
elsif updating then
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :old.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak
where id = :old.id;
if nGlsj is null then
nGlsj := 0;
end if;
update gz_t_basebak set dp_id = :new.jgid, 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;
elsif deleting then
delete from gz_t_basebak where id = :old.id;
end if;
end; 記得
if xxxxx then
Action
endif;
------
Fishman |
|
seagal2222
一般會員 發表：24 回覆：48 積分：14 註冊：2003-03-04 發送簡訊給我 |
这样我也试过。
刚才我把你的代码拷过去，提示错误： 6/9 PL/SQL: SQL Statement ignored
6/55 PLS-00201: 必须说明标识符 'RLZY.RS_DA_RYDABAK'
7/9 PL/SQL: SQL Statement ignored
7/64 PLS-00201: 必须说明标识符 'RLZY.RS_DA_RYDABAK'
11/9 PL/SQL: SQL Statement ignored
12/17 PLS-00201: 必须说明标识符 'ID'
15/9 PL/SQL: SQL Statement ignored
15/55 PLS-00201: 必须说明标识符 'RLZY.RS_DA_RYDABAK'
16/9 PL/SQL: SQL Statement ignored
16/64 PLS-00201: 必须说明标识符 'RLZY.RS_DA_RYDABAK' 更加不明白 ———————————
抓住快乐！
|
|
Fishman
尊榮會員 發表：120 回覆：1949 積分：2163 註冊：2006-10-28 發送簡訊給我 |
Hi seagal2222, 根據經驗，Trigger 中不能再去作 Select、Update、Insert、Delete 自己本身 Table 之動作，亦即你的 Trigger 是針對 rlzy.rs_da_rydabak，內容中即不允許你在去 Select rlzy.rs_da_rydabak
create or replace trigger da_gzbase
after insert or update or delete on rlzy.rs_da_rydabak
for each row
declare
sGzsj varchar2(60);
nGlsj number(8);
begin
if inserting then
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :new.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak where id = :new.id;
if nGlsj is null then
nGlsj := 0;
end if;
insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (id, :new.jgid, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
elsif updating then
select to_char(gzsj,'yyyymm') into sGzsj from rlzy.rs_da_rydabak where id = :old.id;
select to_number(to_char(glsj,'yyyy')) into nGlsj from rlzy.rs_da_rydabak
where id = :old.id;
if nGlsj is null then
nGlsj := 0;
end if;
update gz_t_basebak set dp_id = :new.jgid, 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;
elsif deleting then
delete from gz_t_basebak where id = :old.id;
end if;
end;
也許你可以改為
create or replace trigger da_gzbase
after insert or update or delete on rlzy.rs_da_rydabak
for each row
declare
sGzsj varchar2(60);
nGlsj number(8);
begin
if inserting then
sGzsj := to_char(:new.gzsj,'yyyymm');
nGlsj := to_number(to_char(:new.glsj,'yyyy'));
if nGlsj is null then
nGlsj := 0;
insert into gz_t_basebak (id, dp_id, xm, yxm, xb, bxh, c05, n07,n02,n05,n01,c03,c09)
values (id, :new.jgid, :new.xm, :new.yxm, to_char(:new.xbid), :new.bxh, sGzsj, nGlsj,
:new.xzjbid,:new.ryflid,:new.jszwid,:new.sfzh,:new.sfzh);
end if;
elsif updating then
sGzsj := to_char(:old.gzsj,'yyyymm');
nGlsj := to_number(to_char(:old.glsj,'yyyy'));
if nGlsj is null then
nGlsj := 0;
update gz_t_basebak set dp_id = :new.jgid, 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 if;
elsif deleting then
delete from gz_t_basebak where id = :old.id;
end if;
end;
------
Fishman |
|
seagal2222
一般會員 發表：24 回覆：48 積分：14 註冊：2003-03-04 發送簡訊給我 |
發表時間：2004-03-02 16:41:07
IP:202.97.xxx.xxx 未訂閱
系統時間：2024-11-23 9:46:37
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |