17,086
社区成员
发帖
与我相关
我的任务
分享
参考下-也是刚刚学oracle
create or replace trigger TRIGGER_PSANDCARSTATUS
after insert
on scm_psorder_isdn
for each row
declare
INPSCODE VARCHAR2(100);--出库单号
spcode varchar2(200);--配送单号
outstorecode varchar2(200);--配送单上所有的出库单号
carplante varchar2(2000);--配送单上所有的车牌号
count_all_out integer;--配送单上的出库单数量
count_in_isdn integer;--已经'回执'的出库单数量
begin
INPSCODE := :NEW.PSCODE;--把扫描编号赋值给变量
select pscode into spcode from scm_psorder where instr(outcode,INPSCODE,1)>0;--配送单号
select carid into carplante from scm_psorder where pscode=spcode;--配送单上的所有车辆
select outcode into outstorecode from scm_psorder where pscode=spcode;--配送单上的所有出库单号
update scm_outstore set outtype=2 where outcode=INPSCODE;--更新出库单状态为 "已回执"
select count(*) into count_all_out from scm_outstore s where instr(outstorecode,s.outcode,1)>0;--配送单上的出库单数量
select count(*) into count_in_isdn from scm_outstore s1 where instr(outstorecode,s1.outcode,1)>0 and s1.outtype=2;--已经'回执'的出库单数量
if count_all_out=count_in_isdn then
update scm_car c set c.car_free_status=0 where instr(carplante,c.number_plate,1)>0;--改变车辆的状态 '空闲'
else
update scm_psorder set status=2 where pscode=spcode and status<>2;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
update scm_psorder set status=2 where pscode=spcode and status<>2;
end;