How to use SQL Trigger and Function to Sum multiple columns

See more:

SQL

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)
)

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.