Need total to reset inside a trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need total to reset inside a trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-02 : 14:49:10

Have the following trigger:

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
after INSERT
AS
begin
DECLARE @Sum Float
SELECT @Sum = SUM(bdr_hfl)
FROM inserted
WHERE freefield3 = 'Rebate'

SET @Sum = COALESCE(@Sum, 0)
UPDATE gbkmut
SET bdr_hfl = bdr_hfl - @Sum
WHERE reknr = ' 1040'
end

I need @Sum to reset to zero if the ord_no changes. I'm inserting discount records that need to subtract from another account's amount.
As records are inserted for one ord_no and Inv_no I need to sum bdr_hfl field. When the ord_no and Inv_no change, I need to subtract that from the amount in the bdr_hfl where the account number is 1040 and the inserted records ord_no and Inv_no match the ord_no and Inv_no where the account is 1040. Then I need the @sum to reset to zero and start summing again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 14:59:05

may be this:-

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT] 
after INSERT
AS
begin
DECLARE @TotalTable table
(
ord_no int,
inv_no int,
Sum Float
)
INSERT INTO @TotalTable
SELECT ord_no,inv_no,COLAESCE(SUM(bdr_hfl),0)
FROM inserted
WHERE freefield3 = 'Rebate'
GROUP BY ord_no,inv_no


UPDATE g
SET g.bdr_hfl = g.bdr_hfl - t.Sum
FROM gbkmut g
INNER JOIN @TotalTable t
ON t.ord_no=g.ord_no
AND t.inv_no=g.inv_no
WHERE g.reknr = ' 1040'
end

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-03 : 09:12:39

That is not subtracting from bdr_hfl where reknr = '1040'

The first statement above was subtracting but the sum wasn't resetting when the inv_no changed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 09:20:17

[code]CREATE TRIGGER UPDATEEGBKMUTREBATERECORDS ON dbo.GBKMUT
after INSERT
AS

UPDATE e
SET e.bdr_hfl = e.bdr_hfl - d.x
FROM gbkmut AS e
INNER JOIN (
SELECT Ord_No,
SUM(bdr_hfl) AS x
FROM inserted
WHERE freefield3 = 'Rebate'
GROUP BY Ord_No
) AS d ON d.Ord_No = e.Ord_No
WHERE e.reknr = '1040'[/code]

E 12°55'05.25"
N 56°04'39.16"

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-03 : 09:28:19

That did the trick.

Really appreciate all the help, I've been learning a lot.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 09:28:22

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources