如何使用SQL触发器和函数对多个列求和 [英] How to use SQL Trigger and Function to Sum multiple columns

问题描述

朋友们，我需要你的帮助来解决这个问题。



- 下面是功能代码

Hi friends, I need your help to solve this problem.

--Below is the function code

CREATE FUNCTION fnSumSubTotalPurchasing
(
	@SuppName VarChar(30)
)
	Returns Money
AS
	BEGIN
		DECLARE @SubTo Money
		SELECT @SubTo =SUM(SUB_TOTAL) 
		FROM PURCHASING_GOODS WHERE SUPPLIER_NAME = @SuppName

		SET @SubTo = IsNull(@SubTo,0)
			RETURN @SubTo
	END

- 这是触发代码

--This is the trigger code

CREATE TRIGGER [trgInsertCreditors]
ON [dbo].[ PURCHASING_GOODS]
FOR INSERT
AS
BEGIN
	DECLARE @SuppName VarChar(30),
                @DateReceive DateTime
		@SubTo Money,
		@UserName VarChar(25)

	DECLARE Cur Cursor For
	Select  Inserted.SUPPLIER_NAME,Inserted.DATE_RECEIVED,
        Inserted.SUB_TOTAL,Inserted.RECEIVED_BY
	From Inserted

	Open Cur
	Fetch Next From Cur INTO @SuppName,@SubTo,@UserName
	While @@Fetch_Status = 0
	BEGIN

	Select @SubTo = dbo.fnSumSubTotalPurchasing(@SuppName)	
	INSERT INTO dbo.CREDITORS_REGISTRAR
	Select SupplierName,
			@SubTo,
			ReceivedBy
	From Inserted 
	WHERE SupplierName = @SuppName

	FETCH Next From Cur INTO @SuppName,@SubTo,@UserName
	End
	Close Cur
	Deallocate Cur
END

- 表格也是如此

--Below too are the tables

CREATE  TABLE PURCHASING_GOODS
(
	SUPPLIER_NAME VarChar(30)NOT NULL,
	PRODUCT_NAME VarChar(50)NOT NULL,
	DESCRIPTION VarChar(50)NOT NULL,
	PRODUCT_CATEGORY VarChar(50)NOT NULL,
	QUANTITY_RECEIVED SmallInt NOT NULL,
	UNIT_PRICE Money NOT NULL,
	SUB_TOTAL Money DEFAULT(0),
	DATE_RECEIVED DateTime NOT NULL,
	RECEIVED_BY Char(25)NOT NULL,
	[TRANSACTION_CODE] [Int] IDENTITY(1,1)
)

请在插入PURCHASING_GOODS表后我想要trigg呃和上面的函数代码将PURCHASING_GOODS中的SUB_TOTAL列相加，不插入列数

。正如我在tigger代码中指出的那样where子句使用SUPPLIER_NAME

然后我希望代码在SUB_TOTAL中对值进行求和，并将其插入CREDITORS_REGISTRAR以及SUPPLIER_NAME，DATE_RECEIVED，CREDIT， RECEIVED_BY。

请将总和值插入CREDITORS_REGISTRAR的CREDIT栏。



请总结好但结果我得到不是一个愿望。例如，如果我

插入三行不能将三行相加并将其作为一行插入到

CREDITORS_REGISTRAR中，而是将值相加并插入所有三行。

Please after the PURCHASING_GOODS table is inserted i want the trigger and the function codes above to sum the SUB_TOTAL column in the PURCHASING_GOODS irresptive of number of columns
inserted. As i have indicated in the tigger code the where clause uses the SUPPLIER_NAME
and after that I want the codes to sum values in the SUB_TOTAL and insert it into CREDITORS_REGISTRAR along side with the SUPPLIER_NAME,DATE_RECEIVED,CREDIT,RECEIVED_BY.
Please the summation value is inserted into the CREDIT column in the CREDITORS_REGISTRAR.

Please is able to sum alright but the result I get is not a desire one. For instance, if I
insert three rows is not able to sum the three rows and insert it as one row into the
CREDITORS_REGISTRAR but rather sum the values and insert all the three rows.

CREATE TABLE CREDITORS_REGISTRAR
(
	SUPPLIER_NAME VARCHAR(30)NOT NULL,
	DATE_RECEIVED DATETIME NOT NULL,
	CREDIT MONEY DEFAULT(0),
	DEBIT MONEY DEFAULT(0),
	RECEIVED_BY VARCHAR(25)NOT NULL,
	TRANSACTION_CODE INT IDENTITY(1,1) 
)

提前致谢。

Thanks in advance.

推荐答案

使用合并 [ ^ ]而不是触发器中的Insert可能会得到你想要的结果。



但是当你似乎使用SQLServer时，我必须建议不要为此目的使用触发器。至少阅读 [ ^ ]这个文件让你知道什么样的问题你可能会遇到。

Using a Merge[^] instead of Insert in the trigger will probably get you the result you want.

But as you seem to use SQLServer I must advice against using a trigger for this purpose. At least read[^] this document so you know what kinds of problems you might run into.

这篇关于如何使用SQL触发器和函数对多个列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！