Trigger Gurus Only Please!

[aitai]

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, see below.

--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 rderID = 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 rderID = 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 --

 

[thelke]

I know I am no guru by far, but wouldn't you want to JOIN the Inserted table?

So this:

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

Would be more like:

--check existence of affiliateid, and for product type
SELECT @AffID =
AffID @ProductType=Source
FROM Orders
JOIN Inserted --Joining to inserted becuase RECORD has not been created, thus NO RECORD IN the Orders table yet.
ON Inserted.AffID=Orders.AffID
WHERE AffID IS NOT NULL --Not sure this is needed.

or something like that?

If I am way off base, I am sorry...

 

[arrowhouse]

I already suggested this:

You need to join to the inserted table

select @AffID=AffID, @ProductType=Source
from Orders INNER JOIN insterted
ON Orders.AffID = inserted.AffID

Please can people not start new threads for the same problem. First you get people suggesting things that have already been suggested & second there are enough threads to go through already!