By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,653 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

Help on trigger - work for update but not inserted

P: 2
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
Share this Question
Share on Google+
3 Replies


P: 39
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

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

P: 39
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.