select b.dj into cnt from a_rkdt a,a_rkdd b where wzlsh=:new.wzlsh and a.jssj=(select max(jssj) from a_rkdt) ;
应该是这个返回多条.
不是触发器问题,你应该确保一条返回,这样可以吗?
select distinct b.dj into cnt from a_rkdt a,a_rkdd b where wzlsh=:new.wzlsh and a.jssj=(select max(jssj) from a_rkdt) ;
应该是这个返回多条.
不是触发器问题,你应该确保一条返回,这样可以吗?
select distinct b.dj into cnt from a_rkdt a,a_rkdd b where wzlsh=:new.wzlsh and a.jssj=(select max(jssj) from a_rkdt) ;
create or replace trigger a_lldd_Trigger_update
before insert on a_lldd_Trigger
for each row
begin
update a_lldd_Trigger set dj=
(select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh));
end;
(select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=16268
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=16268))
是可以的
可以使用自治事务
create or replace trigger a_lldd_Trigger_update
before insert on a_lldd_Trigger
for each row
declare
pragma autonomous_transaction;
begin
update a_lldd_Trigger set dj=
(select distinct b.dj from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh));
commit;
end;
create or replace trigger a_lldd_Trigger_update1
before insert on a_lldd_Trigger1
for each row
declare
pragma autonomous_transaction;
begin
update a_lldd_Trigger set dj=:new.dj where wzlsh=:new.wzlsh;
commit;
end;create or replace trigger a_lldd_Trigger_update
before insert on a_lldd_Trigger
for each row
declare
pragma autonomous_transaction;
cnt number;
begin
cnt:=0;
select distinct b.dj into cnt from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh);
insert into a_lldd_Trigger1 (wzlsh,dj) values (:new.wzlsh,cnt);
commit;
end;
a_lldd_Trigger和a_lldd_Trigger是两个相同结构的表
可是trigger a_lldd_Trigger_update1就是不能修改a_lldd_Trigger表中的dj值。这是为什么?
before insert on a_lldd_Trigger1
for each row
declare
pragma autonomous_transaction;
begin
update a_lldd_Trigger set dj=:new.dj where wzlsh=:new.wzlsh;
update a_lldd_Trigger(***);
commit;
end;
我修改的代码如下:
create or replace trigger a_lldd_Trigger_update1
after insert on a_lldd_Trigger
for each row
begin
--直接写修改a_lldd_Trigger表中的dj的值就可以了
end;
create or replace trigger a_lldd_Trigger
before insert on a_lldd
for each row
declare
pragma autonomous_transaction;
cnt number;
begin
cnt:=0;
select distinct b.dj into cnt from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh
and a.jssj=(select max(jssj) from a_rkdt a,a_rkdd b where a.lsh=b.rkdtlsh and wzlsh=:new.wzlsh);
--insert into a_lldd_Trigger1 (wzlsh,dj) values (:new.wzlsh,cnt);
--commit;
--update a_lldd_Trigger set dj=cnt where wzlsh=:old.wzlsh;
--commit;
:new.dj:=cnt;
end;