April 25, 2002 at 11:24 am
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.
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 --
April 25, 2002 at 11:44 am
A trigger fires once for the transaction, not for each row. So if you insert multiple rows you will have problems.
This trigger also appears that you will always get the ID of the same row. What you want is to get the AFFID from the inserted table, not the existing table.
I'd rewrite this to perform a direct update of the table based on the inserted table, which will also allow you to handle multiple rows. So for the first update:
update Affiliate
set
AffTotalEarnings = currentearnings +
case when producttype = 'flower'
then CONVERT(money,ChargedAmount*CommissionRate)
end
from Orders o,FlowerOrder t,Commission c
inner join inserted i
on i.affid = affliiates.adffid
where o.OrderID = t.OrderID
and t.CommissionID = c.CommissionID
and PaymentConfirmedYN='YES'
Steve Jones
April 25, 2002 at 7:53 pm
I am just expanding on what Steve put out, sorry did not have a chance to check my syntax is right but should be close. You have to look at the inserted table to see the data going in.
--start trigger--
create trigger UpdateAffiliateEarnings
on Orders
for insert, update
as
BEGIN
--Flower
UPDATE
Affiliates
SET
AffTotalEarnings = AffTotalEarnings + (CONVERT(money,ChargedAmount*CommissionRate)),
AffOutstandingBalance = (AffTotalEarnings + (CONVERT(money,ChargedAmount*CommissionRate))) - AffTotalPayments
FROM
Affiliates a
INNER JOIN
inserted o
ON
o.Affid = a.Affid
INNER JOIN
FlowerOrder t
ON
o.OrderID = t.OrderID
INNER JOIN
Commission c
ON
t.CommissionID = c.CommissionID
WHERE
PaymentConfirmedYN = 'YES'AND
o.Source = 'FLOWER' AND
o.AffID IS NOT NULL
--roll back the transaction if there is an error
if @@ERROR !=0
rollback tran
END
-- end of flower --
BEGIN
--Phone
UPDATE
Affiliates
SET
AffTotalEarnings = AffTotalEarnings + (CONVERT(money,ChargedAmount*CommissionRate)),
AffOutstandingBalance = (AffTotalEarnings + (CONVERT(money,ChargedAmount*CommissionRate))) - AffTotalPayments
FROM
Affiliates a
INNER JOIN
inserted o
ON
o.Affid = a.Affid
INNER JOIN
PhoneOrder t
ON
o.OrderID = t.OrderID
INNER JOIN
Commission c
ON
t.CommissionID = c.CommissionID
WHERE
PaymentConfirmedYN = 'YES'AND
o.Source = 'PHONE' AND
o.AffID IS NOT NULL
--roll back the transaction if there is an error
if @@ERROR !=0
rollback tran
end
-- end of Phone --
-- end of trigger --
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply