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.