将自己以前跟踪分析后写的语句整理分享 --白波九道 2011.12
这个触发器的制作是以前公司想在某一成品入库时就自动生成销售订单然后出库销售,因为是这样的销售模式,每天都有大量的单据做,所以做了这个触发器来自动生成销售订单和销售出库单减少工作量。
这是一个实用的触发器,已经使用了近两年了,完全没有问题。我加了备注方便理解。
RCREATE TRIGGER [IN_DIS] ON [dbo].[RdRecord]
FOR UPDATE
not for replication
AS
------------==========================★触发器-审核一个帐套成品入库时生成销售出库单★=======================---------------------------
-------------------------------------------格式化单号-------------------------------------------------------------
DECLARE @cDLCode char(15) --发货单号
DECLARE @DJHH char(11)
DECLARE @YM char(6)
DECLARE @cmemo char(50)
DECLARE @MNO INT --当天最大的单据号(数值)
--格式化日期
SET @YM = CONVERT(char(6),GETDATE(),12)
--单据字头
SET @DJHH = 'DRWH'+ @YM +'-'
if not exists (select DLID from dispatchlist where substring(cDlcode,5,6) = @YM)
set @mno=0
else
select top 1 @mno=convert(int,right(cDlcode,3)) from dispatchlist where left(cDlcode,4)='DRWH' AND substring(cDlcode,5,6) = @YM order by cDlcode DESC
--格式化序号
set @cDLCode=@DJHH+right('000'+cast(@mno+1 as varchar),3)
set @cmemo='由成品入库单'+(select cCode from inserted)+'生成'
-----------------------------------------------------------------------------------------------------------------------
if Update(cHandler) and (select cHandler from inserted )<>'' and (select cWhCode from inserted)='WXYLC' and (select cBusType from inserted)='成品入库'
and (select cRdCode from inserted)='SCP' and (select cDepcode from inserted)='H202'
and (select cdefine9 from inserted) is null and not exists(select DLid from DispatchList wherecDLCode=@cDLCode)
BEGIN
DECLARE @DLID int --发货单主表标识
DECLARE @AutoID int --发货单子表标识
DECLARE @iDlsID int --发货单子表辅助标识
DECLARE @maxid int
DECLARE @sysid int
DECLARE @i int
DECLARE @count int
DECLARE @DDate varchar(10)
set @ddate=(SELECT CONVERT(VARCHAR(10),GETDATE(),120))
set @maxid=(select max(dlid)+1 from dispatchlist)
set @sysid=(select iFatherId+1 from UFSystem..UA_Identity where cAcc_id='009' and cVouchtype='DISPATCH' )
if @maxid>=@sysid set @DLID=@maxid else set @DLID=@sysid
set @maxid=(select max(iDlsID)+1 from dispatchlists)
set @sysid=(select iChildId+1 from UFSystem..UA_Identity where cAcc_id='009' and cVouchtype='DISPATCH')
if @maxid>=@sysid set @iDlsID=@maxid else set @iDlsID=@sysid
-------------------------------------------插入新记录到销售出库单主表-------------------------------------------------------------
Insert Into dispatchlist
(cbustype,ivtid,cDLCode,cvouchtype,cstcode,ddate,cdepcode,cpersoncode,ccuscode,cexch_name,iexchrate,itaxrate,cmemo,breturnflag,
dlid,cmaker,bfirst,sbvid,isale,ccusname,bcredit,iverifystate)
Values
('普通销售',71,@cDLCode,'05','H0',@ddate,'H0','H005','J0008','人民币',1,17,@cmemo,0,
@DLID,'何伶伶',0,0,0,'绵阳得润',0,0)
update rdrecord set cdefine9='1' where id=(select id from inserted)
update UFSystem..UA_Identity set iFatherId=@DLID where cAcc_id='009' and cVouchtype='DISPATCH'
-------------------------------------------插入新记录到销售出库单子表-------------------------------------------------------------
set @autoid=(select max(autoid) from rdrecords where id=(select id from inserted))
Select identity(int,1,1) tmpid,null iDlsID,@DLID dlid,'WXYLC' cwhcode,a.cInvCode,iQuantity,isnull(iinvscost,0) iquotedprice,isnull(iinvscost,0) iunitprice,isnull(iinvscost,0)*1.17 itaxunitprice,isnull(iinvscost,0)*iQuantity imoney,
isnull(iinvscost,0)*iQuantity*0.17 itax,isnull(iinvscost,0)*iQuantity*1.17 isum,0 idiscount,isnull(iinvscost,0) inatunitprice,isnull(iinvscost,0)*iQuantity inatmoney,
isnull(iinvscost,0)*iQuantity*0.17 inattax,isnull(iinvscost,0)*iQuantity*1.17 inatsum,0 inatdiscount,0 bsettleall,0 itb,100 kl,100 kl2,cinvname,17 itaxrate
into #tmpdisp
from Rdrecords a left join SA_CusUPriceView b on b.ccuscode='j0008' and a.cinvcode=b.cinvcode where id=(select id from inserted)
set @i=1
set @count=(select count(*) from #tmpdisp)
while @i<=@count
begin
update #tmpdisp set iDlsID=@iDlsID where tmpid=@i
set @i=@i+1
set @iDlsID=@iDlsID+1
end
Insert Into dispatchlists
(dlid,iDlsID,cwhcode,cInvCode,iquantity,iquotedprice,iunitprice,itaxunitprice,imoney,
itax,isum,idiscount,inatunitprice,inatmoney,
inattax,inatsum,inatdiscount,bsettleall,itb,kl,kl2,cinvname,itaxrate,
fsalecost,fsaleprice,bIsSTQc,bGsp,cMassUnit,bQANeedCheck,bQAUrgency,bQAChecking,bQAChecked,iQAQuantity,iQANum,bcosting,fcusminprice)
select dlid,iDlsID,cwhcode,cInvCode,iquantity,iquotedprice,iunitprice,itaxunitprice,imoney,
itax,isum,idiscount,inatunitprice,inatmoney,
inattax,inatsum,inatdiscount,bsettleall,itb,kl,kl2,cinvname,itaxrate,
0,0,0,0,0,0,0,0,0,0,0,1,0 from #tmpdisp
update UFSystem..UA_Identity set iChildId=@iDlsID-1 where cAcc_id='009' and cVouchtype='DISPATCH'
END