Getting latest record from trigger

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

Quote:

Originally Posted by mccax

Hello,

I have the following table (UNIQUOTE) with data in it:

Opportunity_IdRx_ComboPlan_Type
B6FG3JX5G$27/$48/$28HMO
B6FG3JX5G$88/$33/$99HMO
B6FG3JX5G$16/$17/$18HMO

There is a trigger on the table that updates another table (opport) on insert. The problem is that if I have an update to make to Rx_Combo it always grabs the first record no matter what. Any ideas on how to get it to update with the current/latest record data? I've looked into SCOPE_IDENTITY() but it can't get it to work. Any ideas? Here's the trigger:

CREATE TRIGGER [update_opportunity_hmo] ON [dbo].[UNIQUOTE]
FOR INSERT
AS
update opport set
opport.HMO_COMP_RX_COPAY=UNIQUOTE.rx_combo,
from opport, UNIQUOTE
where UNIQUOTE.opportunity_id=opport.id
and uniquote.plan_type='HMO'

try getting the value from inserted/deleted tables