U8触发器

最新推荐文章于 2024-08-31 18:20:51 发布

-_-哈-_-哈-_-哈-_-

最新推荐文章于 2024-08-31 18:20:51 发布

阅读量3.5k

点赞数

本文链接：https://blog.csdn.net/fzgjf08/article/details/62887835

版权

这是一个关于U8系统的SQL触发器，用于在SO_SOMain_extradefine表中数据更新时，根据特定条件（部门代码以'106%'开头）执行一系列计算和更新操作。触发器涉及了多个表，包括SO_SOMain, SO_SODetails及自定义字段表，主要计算和更新了折扣率、无税单价、价税合计等相关字段。" 11571795,1550787,C++野指针问题深度解析,"['C++编程', '内存管理', '程序错误', '指针操作']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [UFDATA_002_2016]
GO
/****** Object: Trigger [dbo].[[tri_update_SO_SOMain_extradefine]]    Script Date: 01/25/2017 11:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tri_update_SO_SOMain_extradefine]
on [dbo].[SO_SOMain_extradefine]
for update
as
--if UPDATE (iQuotedPrice)

SELECT * FROM SO_SOMain_extradefine
declare @tpcDepCode varchar(20)
DECLARE @CODE varchar(20)
SELECT @CODE = cSOCode FROM inserted a inner join SO_SOMain b
on a.ID = B.ID

select @tpcDepCode = cDepCode from SO_SOMain WHERE cSOCode = @CODE
if (@tpcDepCode like '106%')

BEGIN

SET NOCOUNT ON;

declare @iNatSum numeric(15,2) --本币价税合计
declare @iMoney numeric(15,2) --原币无税金额
declare @iSum numeric(15,2)    --原币价税合计
declare @iNatMoney numeric(15,2) --本币无税金额
declare @iUnitPrice numeric(15,2) --原币无税单价
declare @iTaxUnitPrice numeric(15,2) --原币含税单价
declare @iNatUnitPrice numeric(15,2) --本币无税单价
declare @iDisCount numeric(15,2) --原币折扣额
declare @iNatDisCount numeric(15,2) --本币折扣额
declare @KL numeric(15,2) --扣率
declare @CBDEFINE6 numeric(15,2)   --自定义6 折后价格
declare @CBDEFINE7 numeric(15,2)   --累计折扣额
declare @CBDEFINE8 numeric(15,2) --折扣率
declare @CBDEFINE9 numeric(15,5) --折后单价
declare @isoid varchar(20)   --子表与自定义表关联ID
declare @cSOCode varchar(20) --销售订单号
declare @e varchar(20)

-- select @isoid = A.iSOsID , @CBDEFINE6 = CBDEFINE6,@CBDEFINE7 = CBDEFINE7 , @CBDEFINE8 = CBDEFINE8 ,@CBDEFINE9 = CBDEFINE9 from [SO_SODetails_extradefine] a inner join SO_SODetails b
--on a.iSOsID = b.ID where B.cSOCode = @CODE

   --select @isoid = iSOsID from inserted b

-- select @e = ippartid from SO_SODetails
-- if (@e is not null)
--PRINT @E
-- ---销售订单主表 SO_SOMain
-----销售订单子表SO_SODetails
----自定义字段表 SO_SODetails_ExtraDefine
---- select * from SO_SOMain
----select * from SO_SODetails
--ELSE
--BEGIN
--select @isoid = iSOsID , @CBDEFINE6 = CBDEFINE6 from inserted
--update SO_SODetails set iNatSum = @CBDEFINE6 ,iMoney = @CBDEFINE6,iSum = @CBDEFINE6,iNatMoney = @CBDEFINE6 where iSOsID = @isoid
-- select @CBDEFINE7 = CBDEFINE7 from inserted
-- update SO_SODetails set iDisCount = @CBDEFINE7,iNatDisCount = @CBDEFINE7 where iSOsID = @isoid
-- select @CBDEFINE8 = CBDEFINE8 from inserted
-- update SO_SODetails set KL = @CBDEFINE8 where iSOsID = @isoid
-- select @CBDEFINE9 = CBDEFINE9 from inserted
-- update SO_SODetails set iUnitPrice = @CBDEFINE9 , iTaxUnitPrice = @CBDEFINE9 , iNatUnitPrice = @CBDEFINE9 where iSOsID = @isoid
--END

DECLARE @socode varchar(20)
DECLARE @actID varchar(10)
select * from SO_SOMain_extradefine

SET @socode = @CODE

--SELECT A.ID,a.cSOCode , * FROM SO_SOMain A INNER JOIN SO_SODetails B
--ON A.ID = B.ID
--WHERE A.cSOCode LIKE ' 234141231231'

--if( @actID = ' 000002' and @socode is not null)
-- begin

--IF EXISTS(SELECT * FROM tpjjtable)
if object_id('tpjjtable','U') is not null
drop table tpjjtable
--create table #tpjjtable
--( socode varchar(20) ,
-- quantity int ,
-- price numeric(10,2),
-- total numeric(10,2)

-- )

declare @id varchar(10)
declare @total_price numeric(10,2)
declare @allprice numeric(10,0)
declare @d2 int
declare @d3 numeric(6,2)
declare @d4 numeric(6,2)
declare @d5 numeric(6,2)
declare @d6 numeric(6,2)
declare @c numeric(6,6)

SELECT @id = A.ID FROM SO_SOMain A INNER JOIN SO_SODetails B
ON A.ID = B.ID
WHERE A.cSOCode LIKE @socode

SELECT A.ID,A .cSOCode ,b.iQuantity ,b.iQuotedPrice , (b.iQuantity *b.iQuotedPrice) as total into tpjjtable FROM SO_SOMain A INNER JOIN SO_SODetails B
ON A.ID = B.ID
WHERE A.cSOCode LIKE @socode and cInvCode not like '99%'--' 234141231231'
select * from tpjjtable

select @d2 = chdefine2 , @d3 = chdefine3, @d4 = chdefine4,@d5 = chdefine5, @d6 = chdefine6 from SO_SOMain_extradefine where ID = @id -- 1000000226
select @total_price = SUM(total) from tpjjtable
set @allprice = ( @total_price * @d3 - @d2 ) * @d4 * @d5 --- @d6
print @allprice

set @c = @allprice / @total_price
print @c

update SO_SODetails set KL = @c where cSOCode = @socode
--select * from #tpjjtable

update SO_SODetails set iUnitPrice = iQuotedPrice *@c ,iTaxUnitPrice = iQuotedPrice *@c ,iNatUnitPrice = iQuotedPrice *@c where cSOCode = @CODE

update SO_SODetails set iSum = iUnitPrice *iQuantity , iMoney = iUnitPrice *iQuantity ,
iNatMoney = iUnitPrice *iQuantity , iUnitPrice = iQuotedPrice *iQuantity
where cSOCode = @CODE

--数量*报价-数量*含税单价
update SO_SODetails set iNatDisCount = ((iQuantity * iQuotedPrice) - (iQuantity*iTaxUnitPrice )),
iDisCount = ((iQuantity * iQuotedPrice) - (iQuantity*iTaxUnitPrice ))
where cSOCode = @CODE

COMMIT

--update SO_SODetails set iNatSum = (KL * total) from SO_SODetails a
--inner join tpjjtable b
--on a.AutoID = b.ID

IF EXISTS(SELECT * FROM tpjjtable)
drop table tpjjtable

END