Help on trigger - work for update but not inserted

[PaulvanZyl]

Good afternoon

i have created a trigger to update the sum of a field to another table. this works for updates but not for inserted. please help


here is the script
create trigger edp1
on opportunities_contacts
for insert, update, delete as
declare @x money
select @x = sum(a.amount)

from opportunities a, inserted b, opportunities_contacts c
where b.contact_id = c.contact_id
and a.id = c.opportunity_id
update contacts_cstm
set contacts_cstm.brr_actual_cury_c = @x
from contacts_cstm, inserted b

where contacts_cstm.id_c = b.contact_id

tables
Contacts
contacts_cstm
opportunity
opportunities_contacts c
contacts has a rec id = contact.id
contacts_cstm = contacts_cstm_id_c which links to contacts
opportunity = opportunity.id
opportunities_contacts = id
opportunities_contacts.contact_id =contact_cstm.id_c
opportunities_contacts.opportunity_id=opportunitie s.id

i need a sum of all the oppertunities.amount per contact to update the contacts_cstm.brr_actual_cury_c with the sum amount of all the related oppertunities.

thank you

[bwestover]

Use CODE tags around your code so its easier to read in the forums.

 
Code looks like this when CODE tags are used
 
Select Foo 
From Bar
Where Condition = TRUE
 
 

What is the error you get when you try an insert? Are you doing a single row insert, or multiline?

[PaulvanZyl]

sorry

Here is the script

[HTML]create trigger edp1
on opportunities_contacts
for insert, update, delete as
declare @x money
select @x = sum(a.amount)
from opportunities a, inserted b, opportunities_contacts c
where b.contact_id = c.contact_id
and a.id = c.opportunity_id
update contacts_cstm
set contacts_cstm.brr_actual_cury_c = @x
from contacts_cstm, inserted b
where contacts_cstm.id_c = b.contact_id[/HTML]

---Tables---
[HTML]Contacts
contacts_cstm
opportunity
opportunities_contacts c
contacts has a rec id = contact.id
contacts_cstm = contacts_cstm_id_c which links to contacts
opportunity = opportunity.id
opportunities_contacts = id
opportunities_contacts.contact_id =contact_cstm.id_c
opportunities_contacts.opportunity_id=opportunitie s.id[/HTML]

i need a sum of all the oppertunities.amount per contact to update the contacts_cstm.brr_actual_cury_c with the sum amount of all the related oppertunities.

thank you

[bwestover]

Im still lost. Maybe someone else can help?

Im having a hard time decoding your table relationships.

If Im understanding your correctly your trying to generate a sum from one table based on the inserted ID and insert that sum into another table.

Does the record your trying for in the destination table alreay exist or do you need to create it?

You might need to create separate triggers for Insert and Update since you want to create a record in one instance, and Update an existing record in the other.