1 create trigger [dbo].[xzz_t] on [dbo].[ICSale] for
2 update as begin
3 if update(FStatus)
4 begin
5 if isnull((select FStatus from inserted ),0)=1
6 and isnull((select FStatus from deleted ),0)=0
7
8 declare @fin int set @fin=(select finterid from inserted)
9 update c set FEndDate=dateadd(dd,-1,GETDATE())
10 from ICSale a inner join ICSaleEntry b on a.FInterID=b.finterid
11 inner join ICPrcPlyEntry c on
12 a.FCustID=c.FRelatedID and b.FItemID=c.FItemID and b.FtaxPrice<>c.FPrice
13 where a.finterid=@fin
14
15 update c set FEndDate='2100-10-1'
16 from ICSale a inner join ICSaleEntry b on a.FInterID=b.finterid
17 inner join ICPrcPlyEntry c on
18 a.FCustID=c.FRelatedID and b.FItemID=c.FItemID and b.FtaxPrice=c.FPrice
19 where a.finterid=@fin
20
21 INSERT INTO [dbo].[ICPrcPlyEntry]
22 ([FInterID],[FItemID],[FRelatedID],[FModel]
23 ,[FAuxPropID],[FUnitID],[FBegQty]
24 ,[FEndQty],[FCuryID],[FPriceType],[FPrice],[FBegDate]
25 ,[FEndDate],[FLeadTime],[FNote],[FChecked]
26 ,[FIndex],[FTime],[FID],[FBase],[FBase1]
27 ,[FBegQty_Base],[FEndQty_Base],[FInteger],[FClassTypeID],[FBCust]
28 ,[FB2CustCls],[FB2Emp],[FB2EmpCls],[FB2VipGrp]
29 ,[FB2Cust],[FB2ItemID],[FB2Item],[FFlagSave]
30 ,[FCreator],[FOperator])
31 select distinct 2,b.fitemid,a.FCustID,0,0,b.FUnitID,0,0,1,0,b.FTaxPrice,a.FDate,'2100-01-01',0,'',
32 1,0,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,0
33 from ICSale a inner join ICSaleEntry b on a.FInterID=b.finterid
34 left join ICPrcPlyEntry c on a.FCustID=c.FRelatedID and b.FItemID=c.FItemID
35 and c.FPrice=b.FtaxPrice
36 where a.FROB=1 and c.FItemID is null and a.finterid=@fin
37
38 update b set FConsignPrice=c.ftaxprice,FConsignAmount=c.FTaxPrice*fqty
39 from icstockbill a inner join icstockbillentry b on a.FInterID=b.finterid
40 inner join (select distinct a.fdate,a.FCustID,b.FItemID,b.ftaxprice
41 from ICSale a inner join ICSaleEntry b on a.FInterID=b.finterid
42 where a.FROB=1 and a.finterid=@fin ) c on c.FCustID=a.FSupplyID and c.FItemID=b.FItemID
43 and YEAR(c.fdate)=YEAR(a.fdate) and MONTH(c.fdate)=MONTH(a.fdate)
44
45 end end
