469,927 Members | 1,832 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Help on trigger - work for update but not inserted

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
Jul 26 '07 #1
3 1457
Use CODE tags around your code so its easier to read in the forums.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Code looks like this when CODE tags are used
  3.  
  4. Select Foo 
  5. From Bar
  6. Where Condition = TRUE
  7.  
  8.  
What is the error you get when you try an insert? Are you doing a single row insert, or multiline?
Jul 27 '07 #2
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
Jul 27 '07 #3
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.
Aug 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Thierry Marneffe | last post: by
3 posts views Thread by Curtis Gilchrist | last post: by
9 posts views Thread by Martin | last post: by
7 posts views Thread by rkrueger | last post: by
15 posts views Thread by Jay | last post: by
4 posts views Thread by rcamarda | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.