登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

触发器示例二  

2012-10-26 10:31:13|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
create trigger tecRS_FromPWtoAS 
 on RdRecord 
 for update
 as
 if update(cHandler)
 
 declare @ccDefine3 char(20)
 declare @dnverifytime datetime
 declare @iID int
 declare @ccDefine5 int
 declare @cVouchType char(3)
 declare @cPersonCode char(10)
 declare @cMaker char(10)
 select @dnverifytime=dnverifytime,@ccDefine3=cCode,@iID=ID,@ccDefine5=cDefine5,@cVouchType=cVouchType,@cPersonCode=cPersonCode,@cMaker=cMaker from inserted
 if @cVouchType = '01'
 begin
  if @dnverifytime is not NULL
  begin
  if @ccDefine5 = 1
  begin
  declare @ccAVCode char(20)
  declare @ccAccPCode char(20)
  declare @ccInvCode char(20)
  declare @ccInvCodeFa char(20)
  declare @iQuantity int
  declare @ccBatch char(10)
  declare @ccDefine34 int
  declare @iIDASV int
  declare @iAutoID1 int
  declare @iAutoID2 int
  declare @ccAVbatch char(15)
  declare @iAVQuantity int
  declare @unitquantity int
  /*定义表体记录的游标*/
  declare Rs_RdRecords_cur cursor
  global static
  for select cInvCode,cBatch,cDefine34 from RdRecords where ID = @iID
  for read only
 
  declare @iRows int
 
  open Rs_RdRecords_cur
  select @iRows=@@CURSOR_ROWS
/*遍历所有相关记录,并根据每条记录建对应单独的表头和表体*/
  while @iRows > 0
  begin
  set @iRows=@iRows-1
  fetch next from Rs_RdRecords_cur into @ccInvCode,@ccBatch,@ccDefine34
 
  if @ccDefine34 = 0
  continue
  /*取存货档案中对应母件的编码*/
  select @ccInvCodeFa=cInvCode from inventory 
  where cInvAddCode = (select cInvDefine5 from inventory where cInvCode = @ccInvCode)
  /*计算最大流水号*/
  select @iIDASV = (max(ID)+1) from AssemVouch
  select @ccAVCode = (max(cAVCode)+1) from AssemVouch
  /*插入表头*/
  insert into AssemVouch
  (
  cAVCode,cVouchType,cAccPCode,dAVDate,cDepCode,cPersonCode,
  cIRdCode,cORdCode,cMaker,iNetLock,ID,bTransFlag,VT_ID,
  dnMaketime,cDefine3
 
  values
  (
  @ccAVCode,'13',@ccInvCodeFa,getdate(),'102',@cPersonCode,
  '17','27',@cMaker,'0',@iIDASV,'false','31',
  getdate(),@ccDefine3
  )
 
  /*计算流水号和批号*/
  select @iAutoID1 = (max(autoID)+1) from AssemVouchs
  set @iAutoID2 = @iAutoID1+1
  set @ccAVbatch = ('99'+@ccBatch)
  /*插入表体第一行*/
  insert into AssemVouchs
  (
  ID,cAVCode,bAVType,cWhCode,cInvCode,iAVQuantity,cAVbatch,
  autoID,bfvqty,compscrp,baseqtyn,baseqtyd,bCosting,
  iExpiratDateCalcu,unitquantity,irowno,cDefine24
  )
  values
  (
  @iIDASV,@ccAVCode,'套件','00001',@ccInvCodeFa,@ccDefine34,@ccAVbatch,
  @iAutoID1,'false',NULL,NULL,NULL,'true',
  '0',1,1,@ccDefine3
  )
 
  /*计算对应散件的数量*/
  select @unitquantity = cInvDefine12 from inventory where cInvCode = @ccInvCode
  set @iAVQuantity=@unitquantity*@ccDefine34
  /*插入表体第二行*/
  insert into AssemVouchs
  (
  ID,cAVCode,bAVType,cWhCode,cInvCode,iAVQuantity,cAVbatch,
  autoID,bfvqty,compscrp,baseqtyn,baseqtyd,bCosting,
  iExpiratDateCalcu,unitquantity,irowno,cDefine24
  )
  values
  (
  @iIDASV,@ccAVCode,'散件','00002',@ccInvCode,@iAVQuantity,@ccBatch,
  @iAutoID2,'true',0,@unitquantity,1,'true',
  '0',@unitquantity,2,@ccDefine3
  )
 
  end
  close Rs_RdRecords_cur
  deallocate Rs_RdRecords_cur
  end
  end
 
  if @dnverifytime is NULL and @ccDefine5 = 1
  begin
  if exists (select cDefine3 from AssemVouch where cDefine3 = @ccDefine3)
  begin
  delete from AssemVouchs where cDefine24 = @ccDefine3 and (select dnverifytime from AssemVouch where cDefine3 = @ccDefine3) is NULL
  delete from AssemVouch where cDefine3 = @ccDefine3 and dnverifytime is NULL
  end
  end
 end
  评论这张
 
阅读(616)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018