原来是SQL7下有这个触发器的,我只是在2000下改了一下,改完后将代码考过来的。 CREATE trigger bzwk_in on dbo.cpxs_bzwk_rkd_main for update as declare @flag_ggkc char(1) declare @flag_ggkc1 char(1) declare @lsh integer declare @rcklb varchar(10) declare @zzrklb varchar(10) declare @zdrq char(10) declare @rq_max char(10) declare @rq_ls char(10) declare @spbh varchar(10) declare @sl integer declare @flag integer if update(flag_tyj) or update(flag_ggkc) begin select @flag_ggkc=a.flag_ggkc,@lsh=a.lsh,@rcklb=a.rcklb,@zzrklb=a.zzrklb,@flag=a.flag_tyj,@zdrq=convert(char(10),a.rq,111),@flag_ggkc1=b.flag_ggkc from deleted b,inserted a where a.lsh=b.lsh and a.flag_ggkc<>b.flag_ggkc if update(flag_tyj) and @flag='1'--退押金还出借 if @rcklb='0117' --押金 begin update cpxs_bzwk_kc_day set yj = yj - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0118' --出借 begin update cpxs_bzwk_kc_day set cj = cj - @sl where spbh = @spbh and rq = @zdrq end else if update(flag_ggkc)--更新库存 begin --当前的结存截至日期 select @rq_max = convert(char(10),max(rq),111) from cpxs_bzwk_kc_day --汇总当前出库单的物品及数量保存到游标中 DECLARE cursl CURSOR FOR select spbh,sum(sl) as sl,rcklb from cpxs_bzwk_rck_mx where lsh_main=@lsh and rcklb=@rcklb group by spbh,lsh_main,rcklb --汇总结束 Open cursl FETCH NEXT FROM cursl INTO @spbh,@sl,@rcklb --根据当前的数据更新结存量 WHILE @@FETCH_STATUS = 0 BEGIN --如果存在根据@rcklb更新数据 if exists(select spbh from cpxs_bzwk_kc_day where spbh=@spbh and rq=@zdrq) begin if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc ='T' and @flag_ggkc1 ='F'--瓶箱班增加 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq end else if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc ='F' and @flag_ggkc1 ='T'--瓶箱班减少,箱票作废 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0101' and @zzrklb='0002'--司机返还 begin update cpxs_bzwk_kc_day set sjcj = sjcj - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0117' --押金 begin update cpxs_bzwk_kc_day set yj = yj + @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0118' --出借 begin update cpxs_bzwk_kc_day set cj = cj + @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0401' --瓶箱班增加,车间减少 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0402' --成品库增加,车间减少 begin update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0403' --成品库增加,车间减少 begin update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0404' --车间结存 begin update cpxs_bzwk_kc_day set cjsl = @sl where spbh = @spbh and rq = @zdrq end else if left(@rcklb,2)='06' and @rcklb <> '0601' and @rcklb<>'0603' --成品库减少 begin update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0601'--瓶箱班增加,成品库减少 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb<>'0603' --司机出借 begin update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set sjcj = sjcj + @sl where spbh = @spbh and rq = @zdrq end end --否则根据@rcklb插入新的数据 else begin --如果当前的结存截至日期小于当前日期,复制数据. while @rq_max < @zdrq begin select @rq_ls = @rq_max select @rq_max = convert(char(10),dateadd(dd,1,convert(datetime,@rq_max,111)),111) insert into cpxs_bzwk_kc_day(spbh,pxbsl,cjsl,cpksl,yj,cj,sjcj,rq) select spbh,pxbsl,cjsl,cpksl,yj,cj,sjcj,@rq_max from cpxs_bzwk_kc_day where rq=@rq_ls end --插入当前新的数据 if not exists(select spbh from cpxs_bzwk_kc_day where spbh=@spbh and rq=@zdrq) insert into cpxs_bzwk_kc_day(spbh,pxbsl,cjsl,cpksl,yj,cj,sjcj,rq) values(@spbh,0,0,0,0,0,0,@zdrq) if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc = 'T' and @flag_ggkc1 = 'F'--瓶箱班增加 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq end else if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc = 'F' and @flag_ggkc1 = 'T'--瓶箱班减少,箱票作废 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0101' and @zzrklb='0002'--司机返还 begin update cpxs_bzwk_kc_day set sjcj = sjcj - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0117' --押金 begin update cpxs_bzwk_kc_day set yj = yj + @sl where spbh = @spbh and rq = @zdrq end else if @rcklb='0118' --出借 begin update cpxs_bzwk_kc_day set cj = cj + @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0401' --瓶箱班增加,车间减少 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0402' --成品库增加,车间减少 begin update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0403' --成品库增加,车间减少 begin update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0404' --车间结存 begin update cpxs_bzwk_kc_day set cjsl = @sl where spbh = @spbh and rq = @zdrq end else if left(@rcklb,2)='06' and @rcklb <> '0601' and @rcklb<>'0603' --成品库减少 begin update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb = '0601'--瓶箱班增加,成品库减少 begin update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq end else if @rcklb<>'0603' --司机出借 begin update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq update cpxs_bzwk_kc_day set sjcj = sjcj + @sl where spbh = @spbh and rq = @zdrq end end
CREATE trigger bzwk_in
on dbo.cpxs_bzwk_rkd_main for update
as
declare @flag_ggkc char(1)
declare @flag_ggkc1 char(1)
declare @lsh integer
declare @rcklb varchar(10)
declare @zzrklb varchar(10)
declare @zdrq char(10)
declare @rq_max char(10)
declare @rq_ls char(10)
declare @spbh varchar(10)
declare @sl integer
declare @flag integer
if update(flag_tyj) or update(flag_ggkc)
begin
select @flag_ggkc=a.flag_ggkc,@lsh=a.lsh,@rcklb=a.rcklb,@zzrklb=a.zzrklb,@flag=a.flag_tyj,@zdrq=convert(char(10),a.rq,111),@flag_ggkc1=b.flag_ggkc
from deleted b,inserted a
where a.lsh=b.lsh and a.flag_ggkc<>b.flag_ggkc
if update(flag_tyj) and @flag='1'--退押金还出借
if @rcklb='0117' --押金
begin
update cpxs_bzwk_kc_day set yj = yj - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0118' --出借
begin
update cpxs_bzwk_kc_day set cj = cj - @sl where spbh = @spbh and rq = @zdrq
end
else if update(flag_ggkc)--更新库存
begin
--当前的结存截至日期
select @rq_max = convert(char(10),max(rq),111) from cpxs_bzwk_kc_day
--汇总当前出库单的物品及数量保存到游标中
DECLARE cursl CURSOR FOR
select spbh,sum(sl) as sl,rcklb from cpxs_bzwk_rck_mx
where lsh_main=@lsh and rcklb=@rcklb
group by spbh,lsh_main,rcklb
--汇总结束
Open cursl
FETCH NEXT FROM cursl INTO @spbh,@sl,@rcklb
--根据当前的数据更新结存量
WHILE @@FETCH_STATUS = 0
BEGIN
--如果存在根据@rcklb更新数据
if exists(select spbh from cpxs_bzwk_kc_day where spbh=@spbh and rq=@zdrq)
begin
if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc ='T' and @flag_ggkc1 ='F'--瓶箱班增加
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq
end
else if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc ='F' and @flag_ggkc1 ='T'--瓶箱班减少,箱票作废
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0101' and @zzrklb='0002'--司机返还
begin
update cpxs_bzwk_kc_day set sjcj = sjcj - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0117' --押金
begin
update cpxs_bzwk_kc_day set yj = yj + @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0118' --出借
begin
update cpxs_bzwk_kc_day set cj = cj + @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0401' --瓶箱班增加,车间减少
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0402' --成品库增加,车间减少
begin
update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0403' --成品库增加,车间减少
begin
update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0404' --车间结存
begin
update cpxs_bzwk_kc_day set cjsl = @sl where spbh = @spbh and rq = @zdrq
end
else if left(@rcklb,2)='06' and @rcklb <> '0601' and @rcklb<>'0603' --成品库减少
begin
update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0601'--瓶箱班增加,成品库减少
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb<>'0603' --司机出借
begin
update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set sjcj = sjcj + @sl where spbh = @spbh and rq = @zdrq
end
end
--否则根据@rcklb插入新的数据
else
begin
--如果当前的结存截至日期小于当前日期,复制数据.
while @rq_max < @zdrq
begin
select @rq_ls = @rq_max
select @rq_max = convert(char(10),dateadd(dd,1,convert(datetime,@rq_max,111)),111)
insert into cpxs_bzwk_kc_day(spbh,pxbsl,cjsl,cpksl,yj,cj,sjcj,rq)
select spbh,pxbsl,cjsl,cpksl,yj,cj,sjcj,@rq_max from cpxs_bzwk_kc_day where rq=@rq_ls
end
--插入当前新的数据
if not exists(select spbh from cpxs_bzwk_kc_day where spbh=@spbh and rq=@zdrq)
insert into cpxs_bzwk_kc_day(spbh,pxbsl,cjsl,cpksl,yj,cj,sjcj,rq) values(@spbh,0,0,0,0,0,0,@zdrq)
if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc = 'T' and @flag_ggkc1 = 'F'--瓶箱班增加
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq
end
else if left(@rcklb,2) = '01' and @rcklb<>'0117'and @rcklb<>'0118' and @flag_ggkc = 'F' and @flag_ggkc1 = 'T'--瓶箱班减少,箱票作废
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0101' and @zzrklb='0002'--司机返还
begin
update cpxs_bzwk_kc_day set sjcj = sjcj - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0117' --押金
begin
update cpxs_bzwk_kc_day set yj = yj + @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb='0118' --出借
begin
update cpxs_bzwk_kc_day set cj = cj + @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0401' --瓶箱班增加,车间减少
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0402' --成品库增加,车间减少
begin
update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0403' --成品库增加,车间减少
begin
update cpxs_bzwk_kc_day set cpksl = cpksl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cjsl = cjsl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0404' --车间结存
begin
update cpxs_bzwk_kc_day set cjsl = @sl where spbh = @spbh and rq = @zdrq
end
else if left(@rcklb,2)='06' and @rcklb <> '0601' and @rcklb<>'0603' --成品库减少
begin
update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb = '0601'--瓶箱班增加,成品库减少
begin
update cpxs_bzwk_kc_day set pxbsl = pxbsl + @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq
end
else if @rcklb<>'0603' --司机出借
begin
update cpxs_bzwk_kc_day set cpksl = cpksl - @sl where spbh = @spbh and rq = @zdrq
update cpxs_bzwk_kc_day set sjcj = sjcj + @sl where spbh = @spbh and rq = @zdrq
end
end