0

me again, the newbie...i have a trigger, which should update the address from Account to contact, if it is changed. But i receive the error "Too many SOQL queries: 101" a lot of times. How can i solve that issue? Can i change the trigger in a specific way to get that handled?

Thanks a lot in advance for any help here.

trigger updateContactAddressfromAccount on Account (after update, before update) {
for (Account a : trigger.new){
    if(a.Country_Responsible_for_Account__c == 'France'){
        List<Contact> cons = [SELECT AccountId,MailingCity,mailingpostalcode, mailingstreet,
                              mailingcountry FROM Contact WHERE AccountId = :a.Id];
    for (Contact c: cons)
    if(c.Mailingcity != a.Billingcity || c.Mailingstreet != a.Billingstreet ||  c.Mailingpostalcode != a.Billingpostalcode  ||  c.Mailingcountry != a.Billingcountry ) {

        c.mailingstreet = a.billingstreet;
    c.mailingpostalcode = a.billingpostalcode;
    c.mailingcity = a.billingcity;
    c.mailingcountry = a.billingcountry;

update c;
    }}}}
3

In addition to performing one query per record in the trigger context, it also performs one DML Operation per child Contact record. Really awful from a performance perspective. The main idea behind "bulkification" of any trigger is to move to a collection based approach. In this case, you need to query the Contact before your loop, and update those that need it afterward.

If you are not a developer, you can indeed move this logic to Process Builder (as mentioned in the comments) and avoid the need to write unit tests, which are almost certainly lacking in this case. If you want to fix this code, you need to learn about many topics in Apex development, but most critically: trigger bulkification, proper unit testing, and exception handling. It would also be really beneficial to learn about trigger handler patterns and how to set up one trigger per object.

The main risk is that you will suffer a performance hit, and may experience CPU timeouts by switching to Process Builder. Another big problem with the approach is that it does not handle errors gracefully. So if you try to update a child Contact and it fails, it can fail the entire transaction and provide really ugly and unhelpful error messages.

The basic idea on how to perform your updates without hitting governors:

List<Contact> contactsToUpdate = new List<Contact>();
for (Contact contact : [
    SELECT ... FROM Contact
    WHERE AccountId IN :trigger.newMap.keySet()
]){
    if (contact.MailingCity != contact.Account.BillingCity && ...)
    {
        contactsToUpdate.add(new Contact(
            Id = contact.Id,
            MailingCity = contact.Account.BillingCity,
            ...
        ));
    }
}

In the above code, I omitted many fields for brevity, but you should get the gist. In order to properly perform the updates, you must handle any DmlException that may occur as a result of your cross-object update.

try
{
    update contactsToUpdate;
}
catch (DmlException d)
{
    for (Integer i = 0; i < d.getNumDml(); i++)
    {
        trigger.newMap.get(contactsToUpdate[d.getDmlIndex(i)].AccountId).addError(d);
    }
}
| improve this answer | |
  • 1
    Thanks a lot Adrian for that detailed description and your time, i will take mine to dive deeper into it...and yes, you are right, i have to learn a lot though...but which admin has the time to learn coding? ;) – Ralf Wittenberger Nov 11 '16 at 0:23
  • you like this pattern better than using database.saveResult[] sr = database.update(contactsToUpdate,false); yada...yada? – Eric Nov 11 '16 at 1:01
  • It's much easier to map the errors back, so I like the simplicity of that. I have written my own library to handle partial saves and all-or-none saves equally, so I do tend to prefer partial saves when using it. – Adrian Larson Nov 11 '16 at 3:09
1

You can use this code and find the comments in it.

trigger updateContactAddressfromAccount on Account (after update, before update) 
{
    //prepare the set of account
    Set<Id> accIds = new Set<Id>();
    for (Account a : trigger.new){
        if(a.Country_Responsible_for_Account__c == 'France'){
            accIds.add(a.id);
        }       
    }   

    //based on list of account, retrieve the list of contacts
    List<Contact> cons = [SELECT AccountId,MailingCity,mailingpostalcode, mailingstreet,
                              mailingcountry, 
                              Account.Billingcity, Account.Billingstreet, 
                              Account.Billingpostalcode, Account.Billingcountry
                              FROM Contact 
                              WHERE AccountId IN:accIds];

    //prepare a list of contacts to be updated  
    List<Contact> lstContact = new List<Contact>();

    for (Contact c: cons)
        if(c.Mailingcity != c.Account.Billingcity 
        || c.Mailingstreet != c.Account.Billingstreet 
        ||  c.Mailingpostalcode != c.Account.Billingpostalcode  
        ||  c.Mailingcountry != c.Account.Billingcountry ) 
        {
            c.mailingstreet = c.Account.billingstreet;
            c.mailingpostalcode = c.Account.billingpostalcode;
            c.mailingcity = c.Account.billingcity;
            c.mailingcountry = c.Account.billingcountry;

            lstContact.add(c);
        }
    //finally perform single DML operation
    if(lstContact.size()>0) update lstContact;
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.