CREATE TRIGGER [TRIGGER_tbVIPRule] ON [dbo].[tbVIPRule]
FOR UPDATE
AS
update A
set CardType = B.CardType
from tbVIP A inner join inserted B on A.Score >= B.MinScore and A.Score < B.MaxScore
where A.CardType <> B.CardType
if ( @@error='N')
begin
rollback
RaisError('提示错误信息!',16,3)
return
end
FOR UPDATE
AS
update A
set CardType = B.CardType
from tbVIP A inner join inserted B on A.Score >= B.MinScore and A.Score < B.MaxScore
where A.CardType <> B.CardType
if ( @@error='N')
begin
rollback
RaisError('提示错误信息!',16,3)
return
end
解决方案 »
- 请问PHP的hash_hmac("md5",加密对应的C#加密的结果是一样的吗?
- 谁能给我举例一个真实的索引例子咧?
- Gridview表格中多层表头的问题
- 如何用阿斯克码找出标点符号
- 求助!在 System.Runtime.InteropServices.COMException 中第一次偶然出现的“mscorlib.dll”类型的异常
- 如何做一个分组工具栏(形如VS2005右侧的工具箱)?
- 电脑资料管理多面手(已更新至3.80版,子功能估计有近30项,主要归功于C#的强大功能) ,继续散分
- 根据ip实现图片的变化
- 統計兩個時間的相隔小時數(日期格式24小時制)
- C#读取oracle数据问题
- iframe页面使用错误!
- C# WINFORM 程序中调用 access里建的查询也就是视图,报错0x80004005
CREATE TRIGGER [TRIGGER_tbVIPRule] ON [dbo].[tbVIPRule]
FOR UPDATE
AS
update A
set CardType = B.CardType
from tbVIP A inner join inserted B on A.Score >= B.MinScore and A.Score < B.MaxScore
where A.CardType <> B.CardType
if ( @@error>0)
begin
rollback
RaisError('提示错误信息!',16,3)
return
end
ALTER TRIGGER [tr_update_cpzd] ON dbo.cpzd
FOR UPDATE
AS
if update(cpbh)
return
if not (update(cartonqty) or update(GrossWeight) or update(dz) or update(cpfl) or update(bjyl_xc) or update(bjyl_dz) or update(bjyl_qt) or update(bjyl_sk))
return
declare @cpbh varchar(20),@dz numeric(18,4),@dz_new numeric(18,4),@工号 char(6),@title varchar(250),@content varchar(1000)
declare @gw_new numeric(18,4),@gw numeric(18,4),@gw_revno int,@bjylsum numeric(18,4)
declare @cpfl varchar(20),@cpfl_new varchar(20)
declare @dz_revno int ,@dzbl numeric(18,4)
declare @bjylsum_new numeric(18,4),@bomdz numeric(18,4),@dzcb numeric(18,4)set @工号=dbo.查询工号(system_user)
declare cursor_cpzd cursor for
select cpbh,dz,dz_revno,cpfl,grossweight,gw_revno ,bjyl_sum from deleted
open cursor_cpzd
fetch next from cursor_cpzd into @cpbh,@dz,@dz_revno,@cpfl,@gw,@gw_revno,@bjylsum
while @@fetch_status=0
begin
if update(DZ)
begin
update cpzd set dz_dlr=system_user,dz_dlsj=getdate(),dz_revno=dz_revno+1 where cpbh=@cpbh
set @dz_new=(select isnull(dz,0) from inserted where cpbh=@cpbh)
update cpzd set netweight=isnull(@dz_new,0)*isnull(cartonqty,0)/1000 where cpbh=@cpbh
insert into cpdz_update(cpbh,dz,dz_new,update_user,update_date) values(@cpbh,isnull(@dz,0),isnull(@dz_new,0),system_user,getdate())
----如果 (新单重-旧单重)/旧单重 else (旧单重-新单重)/旧单重 //Kyle modifyDate 20070705 10:50
if isnull(@dz,0)=0
begin
return
set @dzbl=1
end
else
begin
if @dz_new>=@dz
begin
set @dzbl=(isnull(@dz_new,0)-isnull(@dz,0))/@dz
end
else
begin
set @dzbl=(isnull(@dz,0)-isnull(@dz_new,0))/@dz
end
end
if @dzbl>0.01 --单重更改范围大于0.01,发BBS
begin
set @title='产品单重变更:'+@cpbh
set @content= '产品编号:'+@cpbh+char(10)+
'更改前单重:'+str(isnull(@dz,0),18,4)+char(10)+
'更改后单重:'+str(isnull(@dz_new,0),18,4)+char(10)+
'更改比重:'+str(@dzbl*100)+'%'+char(10)+
'更改人:'+system_user+char(10)+
'更改时间:'+convert(varchar,getdate(),120)+char(10)+
'更改次数:'+str(isnull(@dz_revno,0)+1)
exec insert_article 116,@工号,@title,@content
end
end
-------包装信息箱毛重变更后发BBS
if update(GrossWeight)
begin
update cpzd set GW_dlr=system_user,GW_dlsj=getdate(),GW_revno=isnull(@gw_revno,0)+1 where cpbh=@cpbh
set @gw_new=(select isnull(grossweight,0) from inserted where cpbh=@cpbh)
set @title='产品箱毛重(KG)变更:'+@cpbh
set @content='产品编号:'+@cpbh+char(10)+'更改前箱毛重(KG):'+str(isnull(@gw,0),18,4)+char(10)+'更改后箱毛重(KG):'+str(isnull(@gw_new,0),18,4)+char(10)+'恢复箱毛重(KG)为:'+str(isnull(@gw,0),18,4)+char(10)+'更改人:'+system_user+char(10)+'更改时间:'+convert(varchar,getdate(),120)+char(10)+'更改次数:'+str(isnull(@GW_revno,0)+1)
exec insert_article 120,@工号,@title,@content
end if update(cpfl)
begin
----update cpzd set dz_dlr=system_user,dz_dlsj=getdate(),dz_revno=dz_revno+1 where cpbh=@cpbh
set @cpfl_new=(select cpfl from inserted where cpbh=@cpbh)
set @title='产品分类变更:'+@cpbh
set @content='产品编号:'+@cpbh+char(10)+'更改前产品分类:'+@cpfl+char(10)+'更改后产品分类:'+@cpfl_new+char(10)+'更改人:'+system_user+char(10)+'更改时间:'+convert(varchar,getdate(),120)
exec insert_article 118,@工号,@title,@content
end
-------更新产品边角余料
if ( update(bjyl_xc) or update(bjyl_dz) or update(bjyl_qt) or update(bjyl_sk))
begin
update cpzd set xgr=system_user,xgsj=getdate() where cpbh=@cpbh
--如果边角余料4个栏位都已输入完成才做处理
if exists(select * from cpzd where cpbh=@cpbh and xc_isaffirm=1 and dz_isaffirm=1 and sk_isaffirm=1 and qt_isaffirm=1)
begin
if ((select dbo.get_cpzd_dzcb(@cpbh))<0.97 or (select dbo.get_cpzd_dzcb(@cpbh))>1.03)
begin
set @bjylsum_new=(select bjyl_sum from cpzd where cpbh=@cpbh)
set @bomdz=isnull((select dbo.para_ncpbh(@cpbh,'bomdz')),0)
set @dzcb=isnull((select dbo.get_cpzd_dzcb(@cpbh)*100),0)
set @title='产品边角余料变更异常:'+@cpbh
set @content= '原因:单重比 A 不在范围内(0.97大于A小于1.03)'+char(10)+
'产品编号 :'+@cpbh+char(10)+
'更改前余料 :'+str(isnull(@bjylsum,0),18,4)+char(10)+
'更改后余料 :'+str(isnull(@bjylsum_new,0),18,4)+char(10)+
'产品单重 :'+str(isnull(@dz,0),18,4)+char(10)+
'产品BOM重量 :'+str(isnull(@bomdz,0),18,4)+char(10)+
'产品单重差比:'+str(isnull(@dzcb,0),18,4)+char(10)+
'请检查此产品的BOM重量和余料重量'+char(10)+
'更改人:'+system_user+char(10)+
'更改时间:'+convert(varchar,getdate(),120)
exec insert_article 120,@工号,@title,@content
end
end
end-------每箱数量cartonqty变更后修改净重
if update(cartonqty)
begin
update cpzd set netweight=isnull(dz,0)*isnull(cartonqty,0)/1000 where cpbh=@cpbh
end fetch next from cursor_cpzd into @cpbh,@dz,@dz_revno,@cpfl,@gw,@gw_revno,@bjylsum
end
close cursor_cpzd
deallocate cursor_cpzd
FOR UPDATE
AS
declare @current int, @CurrCardType varchar(10), @NewCardType varchar(10), @VIPID varchar(20), @VIPWord varchar(20)
select @current=Score, @CurrCardType=CardType, @VIPID=VIPID, @VIPWord=VIPWord from inserted
set @NewCardType=(Select Top 1 CardType from tbVIPRule where @current>MinScore order by MinScore)
IF @CurrCardType<>@NewCardType
Begin
Update tbVIP
set CardType=@NewCardType
where VIPID=@VIPID, VIPWord=@VIPWord
End
FOR UPDATE
AS
declare @current int, @CurrCardType varchar(10), @NewCardType varchar(10), @VIPID varchar(20), @VIPWord varchar(20)
select @current=Score, @CurrCardType=CardType, @VIPID=VIPID, @VIPWord=VIPWord from inserted
set @NewCardType=(Select Top 1 CardType from tbVIPRule where @current>MinScore order by MinScore)
IF @CurrCardType<>@NewCardType
Begin
Update tbVIP
set CardType=@NewCardType
where VIPID=@VIPID, VIPWord=@VIPWord
End
这个是根据Rules更新来更新tbVIP的CREATE TRIGGER [TRIGGER_tbVIPRule] ON [dbo].[tbVIPRule]
FOR UPDATE
AS
declare @CardType varchar(10), @MinScore int, @MaxScore int
select @CardType=CardType, @MinScore=MinScore, @MaxScore=MaxScore from inserted
update tbVIP
set CardType=@CardType
where Score between @MinScore and @MaxScore
for update
as
update A
set CardType = B.CardType
from tbVIP A inner join inserted B on A.Score >= B.MinScore and A.Score < B.MaxScore
where A.CardType <> B.CardType
if ( @@error>0)
begin
rollback
RaisError('提示错误信息!',16,3)
return
end
感谢大家了