新做了一个触发器,可以同时插入多条数据,但是更新时,只更新了一条。什么问题啊????
新人,没啥分
代码如下:
create or replace trigger unload
before update on pb11_tallysheet_unload
REFERENCING old AS oldRow
new as newRow
for each rowdeclare
STAS_REMARK varchar2(30);
counter number :=1;begin
select um.stas_re into STAS_REMARK from pb11_tallysheet_unloaditem um where um.utsid=:oldRow.id and rownum=1; if STAS_REMARK is not null
then
update pb11_tallysheet_unloaditem utm set utm.stas_date='', utm.stas_re=''
where utm.utsid =:oldRow.id; select count(*) into counter from pb16_tscargoitem ts where ts.ciid in (select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id);
if counter=0
then
--------------------------------------问题语句所在----------------------------------------
insert into pb16_tscargoitem select ts.* from pb16_tscargoitem1 ts
where ts.ciid in (select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id); update pb16_tscargoitem t set t.weightvalue=abs(t.weightvalue)*-1
where t.ciid in (select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id);
commit;
--------------------------------------问题语句所在---------------------------------------- end if;end if ;
end unload;
新人,没啥分
代码如下:
create or replace trigger unload
before update on pb11_tallysheet_unload
REFERENCING old AS oldRow
new as newRow
for each rowdeclare
STAS_REMARK varchar2(30);
counter number :=1;begin
select um.stas_re into STAS_REMARK from pb11_tallysheet_unloaditem um where um.utsid=:oldRow.id and rownum=1; if STAS_REMARK is not null
then
update pb11_tallysheet_unloaditem utm set utm.stas_date='', utm.stas_re=''
where utm.utsid =:oldRow.id; select count(*) into counter from pb16_tscargoitem ts where ts.ciid in (select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id);
if counter=0
then
--------------------------------------问题语句所在----------------------------------------
insert into pb16_tscargoitem select ts.* from pb16_tscargoitem1 ts
where ts.ciid in (select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id); update pb16_tscargoitem t set t.weightvalue=abs(t.weightvalue)*-1
where t.ciid in (select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id);
commit;
--------------------------------------问题语句所在---------------------------------------- end if;end if ;
end unload;
trigger里不允许存在commit,除非你使用自治事务.
select um.ciid from pb16_tallysheet_unloaditem1 um
inner join pb16_tallysheet_unload1 l on l.id=um.utsid where l.id=:oldRow.id