Insert Trigger Logical Error

[aitai]

Hi, All

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. Thanks

Ivan

 

[CasperTFG]

I'd need to see your SQL statement Craig, mailto:sander@cogeco.ca

Remember not to name the Lambs...
It only makes the chops harder to swallow

 

[aitai]

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

 

[cmmrfrds]

I believe the trigger happens before the actual Update of the table so that the data from the inserted record is not getting added the way it is setup. Include the amount from the inserted table.

Select CONVERT(money,ChargedAmount*CommissionRate) from inserted.

and add to the totals

 

[arrowhouse]

I think your problem is this statement:

select @AffID=AffID, @ProductType=Source
from Orders
where AffID IS NOT NULL
First time there is nothing in orders - will work ok.

next time

and AffID exists that is not null (ther record that was inserted previously). You need to join to the inserted table

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



The trigger as it is at the moment will also only work for a single row insert/update. The tables inserted/deleted may contain many rows.

 

[aitai]

Guys, thanks for both of your suggestions. The problem still persists, however. That is, the values from the inseted table still display a latency of 1 row after running the statement more than once.

 

[tlbroadbent]

Change the trigger to get the values from the Inserted table not from the Orders table. I think you need to review the documentaion on Triggers to understand how they work.

Replace your statement...

select @AffID=AffID, @ProductType=Source
from Orders
where AffID IS NOT NULL

With this statement...

select @AffID=AffID, @ProductType=Source
from inserted

Don't join to the Orders table.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.