Insert Trigger Logical Error - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Insert Trigger Logical Error

Author

Topic

aitai
Starting Member

3 Posts

Posted - 2002-04-25 : 12:43:00

The many that I have spoken to all are clueless on this one. Thanks in advance for the right solution!

The insert trigger I created works fine (well, nearly fine), except that AFTER the first insert operation (ie second, third etc), it always produces the correct results BUT FOR THE PREVIOUS INSERTED ROW. It is as if there is a latency of one row in the temp table INSERTED.

I would greatly appreciate a 'why', and more importantly, a 'how to fix it' for this problem.

If you need to look at the code, a NOTEPAD file is attached.

Much appreciated

--start trigger--
create trigger UpdateAffiliateEarnings
on Orders
for insert, update
as

--declare variables
declare @ProductType varchar(15),
@AffID int,
@Earnings money,
@CurrentEarnings money,
@AffTotalEarnings money,
@AffTotalPayments money,
@AffOutstandingBalance money

--check existence of affiliateid, and for product type
select @AffID=AffID, @ProductType=Source
from Orders
where AffID IS NOT NULL

--get relevant information
if @AffID IS NOT NULL
begin
if @ProductType = 'FLOWER'
begin
select @Earnings=CONVERT(money,ChargedAmount*CommissionRate)
from Orders o,FlowerOrder t,Commission c
where o.OrderID = t.OrderID
and t.CommissionID = c.CommissionID
and PaymentConfirmedYN='YES'
end
if @ProductType='PHONE'
begin
select @Earnings=CONVERT(money,ChargedAmount*CommissionRate)
from Orders o,PhoneOrder t,Commission c
where o.OrderID = t.OrderID
and t.CommissionID = c.CommissionID
and PaymentConfirmedYN='YES'
end

--update Affiliate account
--get totals to update
select @CurrentEarnings=AffTotalEarnings, @AffTotalPayments=AffTotalPayments
from Affiliates
where AffID=@AffID

--calculate new totals for affiliate account
set @AffTotalEarnings=@CurrentEarnings+@Earnings
set @AffOutstandingBalance=@AffTotalEarnings-@AffTotalPayments

--update affiliate account to new totals
update Affiliates
set AffTotalEarnings=@AffTotalEarnings, AffOutstandingBalance=@AffOutstandingBalance
where AffID=@AffID

--roll back the transaction if there is an error
if @@ERROR !=0
rollback tran
end
-- end of trigger --

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-25 : 12:54:56

triggers are always tough for an outsider to debug 'cause they are drenched in business rules . . .

however, the lack of any join to the inserted table waves a big red flag for me.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources