谢谢你的参与。我用的就是触发器啊,可是在Insert触发器里好象不能对新增的记录进行修改吧?比如:我是给t1建立的触发器,我想在新增时根据条件将t1.iflag列改的值由0改成3,:new.iflag=3,不能执行啊!
解决方案 »
- 小弟初学oracle,问问关于数据库字符串截取的问题。
- shutdown immediate20分钟没响应重启服务器导致的【问题】
- 数据库优化
- 著名的 ksvcreate: Process(m000) creation failed 问题
- 通用数据库格式转换工具最新版,支持oracle,sqlsever,access等数据库之间数据格式转换
- 忘记了system密码该怎么办?
- 字符集问题啊!救命!!!!!!!!!!!!
- 请问VB中如何存取ORACLE数据库的CLOB类型的字段?
- 向大侠指教Oracle8i的问题
- 欢迎大家来讨论索引的使用,优秀者得分
- spotlight for sqlserver和SQL Expert for SQL server的序列号谁有?
- 我的头都打了!!!!
create or replace trigger AoPlanCarSet
instead of insert on rgorder
for each row
declare
-- local variables here
rec_Limit Biparameter%rowtype;
isManu number(2,0);
cursor LimitCur is
select * from Biparameter;
begin
if :new.cTransNo<>'001' then
return;
end if;
select bAuto into isManu from biset;
if isManu=0 then
begin
update rgOrder set iFlag=2 where vcOrderNo=:new.vcOrderNo;
return;
end;
end if;
open LimitCur;
loop
fetch LimitCur into rec_Limit;
exit when LimitCur%NOTFOUND;
if rec_Limit.Itype=1 then
begin
if trim(rec_Limit.Vccontent)=trim(:new.vcOrderNo) then
begin
update rgOrder set iFlag=3 where vcOrderNo=:new.vcOrderNo;
return;
end;
end if;
end;
elsif rec_Limit.Itype=2 then
begin
if rec_Limit.Bflag = 1 then
begin
if to_char(:new.dtOrderDate,'yyyy-mm-dd')>=to_char(rec_Limit.Dtbegindate,'yyyy-mm-dd')
and to_char(:new.dtOrderDate,'yyyy-mm-dd')<=to_char(rec_Limit.Dtenddate,'yyyy-mm-dd') then
begin
update rgOrder set iFlag=3 where vcOrderNo=:new.vcOrderNo;
Ao_Package.changevalue := 3;
return;
end;
end if;
end;
end if;
end;
elsif rec_Limit.Itype=3 then
begin
if trim(rec_Limit.Vccontent)=trim(:new.vcStyleNo) then
begin
update rgOrder set iFlag=3 where vcOrderNo=:new.vcOrderNo;
return;
end;
end if;
end;
end if;
end loop;
update rgOrder set iFlag=5 where vcOrderNo=:new.vcOrderNo;
commit;
end AoPlanCarSet;
create or replace trigger AoPlanCarSet
before insert on rgorder
for each row
begin
...
if vcContent='aa' then
:new.vcOrderNo=3;
end if;
...
end;
before update on rgorder
for each row
declare
-- local variables here
rec_Limit Biparameter%rowtype;
cursor LimitCur is
select * from Biparameter;
begin
if (:old.iFlag=2) and (:new.iFlag=1) then
begin
--由2转变成1,这种情况出现在人工订单转自动订单时,也要进行过滤后再进行排车(人工或是自动排)
open LimitCur;
loop
fetch LimitCur into rec_Limit;
exit when LimitCur%NOTFOUND;
if rec_Limit.Itype=1 then
begin
--过滤出订单号
if trim(rec_Limit.Vccontent)=trim(:new.vcOrderNo) then
begin
:new.iFlag := 3;
return;
end;
end if;
end;
elsif rec_Limit.Itype=2 then
begin
--过滤出货物发往地
if rec_Limit.Bflag = 1 then
begin
if to_char(:new.dtOrderDate,'yyyy-mm-dd')>=to_char(rec_Limit.Dtbegindate,'yyyy-mm-dd')
and to_char(:new.dtOrderDate,'yyyy-mm-dd')<=to_char(rec_Limit.Dtenddate,'yyyy-mm-dd') then
begin
:new.iFlag := 3;
return;
end;
end if;
end;
end if;
end;
elsif rec_Limit.Itype=3 then
begin
--过滤出车型
if trim(rec_Limit.Vccontent)=trim(:new.vcStyleNo) then
begin
:new.iFlag := 3;
return;
end;
end if;
end;
end if;
end loop;
:new.iFlag := 5;
AoPlanCar(:new.vcOrderNo);
end;
end if;
end AoAuPlanCar;
CarNum numeric(2, 0);
Driver_No varchar2(30);
IsBalance numeric(2, 0);
BalanceMile numeric(9, 2);
DriverMile numeric(9, 2);
FleetNo varchar2(30);
cursor Driver_Cur is
select vcDriverNo from TMDriverQueue where bCome = 1 order by dtDate;
begin
select dcQty into CarNum from rgOrder where vcOrderNo = orderID;
if (CarNum is null) or (CarNum=0) then
return;
end if;
while CarNum > 0 loop
open Driver_Cur;
loop
fetch Driver_Cur
into Driver_No;
exit when Driver_Cur%notfound;
select bBalance, dcBalance into IsBalance, BalanceMile from BISet;
if IsBalance = 1 then
begin
select dcBalance, cFleetNo into DriverMile, FleetNo from BIDriver
where vcDriverNo = Driver_No;
if BalanceMile > DriverMile then
begin
insert into rgOrderDetail
(Vcorderno, Cfleetno, Vcdriverno)
values
(orderID, FleetNo, Driver_No);
delete from TMDriverQueue where vcDriverNo = Driver_No;
update BIDriver
set vcOrderList = orderID
where vcDriverNo = Driver_No;
exit;
end;
end if;
end;
else
begin
insert into rgOrderDetail
(Vcorderno, Cfleetno, Vcdriverno)
values
(orderID, FleetNo, Driver_No);
delete from TMDriverQueue where vcDriverNo = Driver_No; update BIDriver set vcOrderList = orderID
where vcDriverNo = Driver_No;
exit;
end;
end if;
end loop;
close Driver_Cur;
CarNum := CarNum - 1;
end loop;
--此订单排完,写订单信息
update rgOrder
set bAssign = 1, dtAssign = sysdate, iAssignType = 1,iFlag=6
where vcOrderNo = orderID;
commit;
exception
when others then
rollback;
end AoPlanCar;
create or replace trigger AoPlanCarSet
before insert on rgorder
for each row
declare
-- local variables here
CarNum numeric(2, 0);
Driver_No varchar2(30);
IsBalance numeric(2, 0);
BalanceMile numeric(9, 2);
DriverMile numeric(9, 2);
FleetNo varchar2(30); --司机车队编号
cursor Driver_Cur is
select vcDriverNo from TMDriverQueue where bCome = 1 order by dtDate;
rec_Limit Biparameter%rowtype;
isManu number(2,0);
cursor LimitCur is
select * from Biparameter;
begin
if :new.cTransNo<>'001' then
return;
end if;
select bAuto into isManu from biset;
if isManu=0 then
begin
:new.iFlag := 2;
-- update rgOrder set iFlag=2 where vcOrderNo = :new.vcOrderNo;
return;
end;
end if;
open LimitCur;
loop
fetch LimitCur into rec_Limit;
exit when LimitCur%NOTFOUND;
if rec_Limit.Itype=1 then
begin
--过滤出订单号
if trim(rec_Limit.Vccontent)=trim(:new.vcOrderNo) then
begin
:new.iFlag := 3;
return;
end;
end if;
end;
elsif rec_Limit.Itype=2 then
begin
if rec_Limit.Bflag = 1 then
begin
if to_char(:new.dtOrderDate,'yyyy-mm-dd')>=to_char(rec_Limit.Dtbegindate,'yyyy-mm-dd')
and to_char(:new.dtOrderDate,'yyyy-mm-dd')<=to_char(rec_Limit.Dtenddate,'yyyy-mm-dd') then
begin
:new.iFlag := 3;
return;
end;
end if;
end;
end if;
end;
elsif rec_Limit.Itype=3 then
begin
if trim(rec_Limit.Vccontent)=trim(:new.vcStyleNo) then
begin
:new.iFlag := 3;
return;
end;
end if;
end;
end if;
end loop;
:new.iFlag := 5;
select :new.dcQty into CarNum from dual;
if (CarNum is null) or (CarNum=0) then
return;
elsif CarNum<:new.dcQty then
begin
--如果司机数量不够,则将订单转入人工处理
:new.iFlag := 3;
return;
end;
end if;
while CarNum > 0 loop
--读取司机排队信息
open Driver_Cur;
loop
fetch Driver_Cur
into Driver_No;
exit when Driver_Cur%notfound;
select bBalance, dcBalance into IsBalance, BalanceMile from BISet;
if IsBalance = 1 then
begin
select dcBalance, cFleetNo into DriverMile, FleetNo from BIDriver
where vcDriverNo = Driver_No;
if BalanceMile > DriverMile then
begin
insert into rgOrderDetail
(Vcorderno, Cfleetno, Vcdriverno)
values
(:new.vcOrderNo, FleetNo, Driver_No);
delete from TMDriverQueue where vcDriverNo = Driver_No; --将司机排队信息删除
--写司机当前订单号
update BIDriver
set vcOrderList = :New.vcOrderNo
where vcDriverNo = Driver_No;
exit;
end;
end if;
end;
else
--不平衡公里
begin
--继续排车,新增一条
insert into rgOrderDetail
(Vcorderno, Cfleetno, Vcdriverno)
values
(:new.vcOrderNo, FleetNo, Driver_No);
delete from TMDriverQueue where vcDriverNo = Driver_No; --将司机排队信息删除
--写司机当前订单号
update BIDriver set vcOrderList = :new.vcOrderNo
where vcDriverNo = Driver_No;
exit;
end;
end if;
end loop;
close Driver_Cur;
CarNum := CarNum - 1;
end loop;
--此订单排完,写订单信息
:new.bAssign := 1;:new.dtAssign := sysdate; :new.iAssignType := 1;:new.iFlag :=6;
end AoPlanCarSet;