function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
klocworkklocwork 

Trigger Help: Too many code statements

Greetings!

 

I've been backwards and forwards through the forums here looking at the examples of solutions to "System.LimitException: Too many code statements: 200001" errors and have made a number of the suggested modifications (that I can figure out) to my code below.  I am easily able to update a handful of records, but as soon as I try to push 50 or more through the trigger I get the error.

 

I am swimming in the deep end and beyond my current comfort.  I would sincerely appreciate some input and guidance on how I can best address the issue I'm seeing!

 

The intent of this code is to align the OwnerId of all related Leads and Contacts when an Account either has a new Owner assigned, or when a check box is selected.  Stipulations - the Account and the Lead records must have North American Country values.

 

 

Thanks

 

 

 

trigger Account_ChangeOwner on Account (before update) {
//This trigger updates Lead and Contact Owners based on either a new Account Owner or a selected check box

	integer counter=0;
	Set<Id> AccountIds = new Set<Id>();
	For (Account Obj : trigger.new){

//Only applies to North American Accounts
		if (Obj.BillingCountry!=null && (
       			Obj.BillingCountry=='CA' || 
       			Obj.BillingCountry=='US' ||
       			Obj.BillingCountry=='Canada' ||
       			Obj.BillingCountry=='United States')  
       			)
       				{
       					if (Obj.OwnerId!=trigger.old[counter].OwnerId)
       					{
       						AccountIds.add(Obj.Id);
       					}
       					else if (Obj.Reset_Contact_Owners__c==true)
       					{
		       				AccountIds.add(Obj.Id);
       					}
       				}

       	counter++;
    }//end of loop 1
    
//Gather all relevant Leads and Conacts and add them to Lists
	list<Lead> Lead_list = [Select 		l.Id,
										l.Account_Record__c,
										l.OwnerId
									From Lead l
										where l.IsDeleted=false 
										and l.IsConverted=false 
										//Only applies to North American Leads
										and (l.Country!=null and (
							       			l.Country='CA' or 
							       			l.Country='US' or
							       			l.Country='Canada' or
							       			l.Country='United States'))
										and Account_Record__c IN :AccountIds]; 

	list<Contact> Contact_list = [Select 	c.Id,
											c.AccountId,
											c.OwnerId
									From Contact c
										where c.IsDeleted=false 
										and AccountId IN :AccountIds]; 

//Make the changes
	counter=0;
    For (Account Obj : trigger.new){
		if (Obj.OwnerId!=trigger.old[counter].OwnerId || Obj.Reset_Contact_Owners__c==true){

					//Review the Leads and Contacts for the Acounts and change the Owner;
		       		For (Lead MyLead : Lead_list){
		       			if (MyLead.Account_Record__c==Obj.Id){
		       				if (Obj.Named_Account__c==true){
				       				MyLead.OwnerId=Obj.OwnerId;								
		       				}
		       			}
       				}       			
       				{
		       		For (Contact MyContact : Contact_list){
		       			if (MyContact.AccountId==Obj.Id){
		       				MyContact.OwnerId=Obj.OwnerId;
		       			}
		       		}
       				}
       	}

		//Clear the Reset Contact Owners field
      	if (Obj.Reset_Contact_Owners__c==true){
			   Obj.Reset_Contact_Owners__c=false;
       	}
       	      	
       	counter++;
	}//end of loop 2 

//Process Leads		
	Lead[] LeadsToUpdate=new Lead[]{};
	For (Lead MyLead : Lead_list){
		LeadsToUpdate.add(MyLead);
		if (LeadsToUpdate.size()==5000){
			update LeadsToUpdate; 
			LeadsToUpdate.clear();
		}
	}
	if (LeadsToUpdate.size()>0){
		update LeadsToUpdate;
	}

//Process Contacts	
	Contact[] ContactsToUpdate=new Contact[]{};
	For (Contact MyContact : Contact_list){
		ContactsToUpdate.add(MyContact);
		if (ContactsToUpdate.size()==5000){
			update ContactsToUpdate; 
			ContactsToUpdate.clear();
		}
	}
	if (ContactsToUpdate.size()>0){
		update ContactsToUpdate;
	}
	
    
}//end of trigger

 

Best Answer chosen by Admin (Salesforce Developers) 
Sean TanSean Tan

You're doing a lot of excessive looping to re-map the values... you can use the Trigger.newMap to get the Account relative to it's Account Id.

 

Try something like this to reduce the number of script statements:

 

trigger Account_ChangeOwner on Account (before update) {
    //This trigger updates Lead and Contact Owners based on either a new Account Owner or a selected check box
    Set<String> billingCountries = new Set<String>
    {
        'CA',
        'US',
        'Canada',
        'United States'
    };
    Set<Id> AccountIds = new Set<Id>();
    For (Account Obj : trigger.new)
    {    
        //Only applies to North American Accounts        
        if (Obj.BillingCountry!=null && billingCountry.contains(Obj.BillingCountry) &&
           (Obj.Reset_Contact_Owners__c == true || Trigger.oldMap.get(Obj.Id).OwnerId != Obj.OwnerId))
        {
            AccountIds.add(Obj.Id);
            Obj.Reset_Contact_Owners__c = false;
        }                    
    }//end of loop 1
    
    if (!AccountIds.isEmpty())
    {
        //Gather all relevant Leads and Conacts and add them to Lists
        Lead[] leadsToUpdate = new Lead[]{};
        for (Lead item : [Select l.Id,
                                l.Account_Record__c,
                                l.OwnerId
                                From Lead l
                                where
                                and l.IsConverted=false     
                                and l.Country IN :billingCountries
                                and Account_Record__c IN : AccountIds])
        {
            Account a = Trigger.newMap.get(a.Account_Record__c);
            
            if (a.Named_Account__c)
            {
                item.OwnerId = a.OwnerId;
                leadsToUpdate.add(item);
                
                if (leadsToUpdate.size() == 5000)
                {
                    update leadsToUpdate;
                    leadsToUpdate.clear();
                }
            }
        }
        
        if (!leadsToUpdate.isEmpty())
        {
            update leadsToUpdate;
        }
        
        Contact[] contactsToUpdate = new Contact[]{};
        
        for (Contact item : [Select c.Id,
                                c.AccountId,
                                c.OwnerId
                                From Contact c
                                where
                                and AccountId IN :AccountIds])
        {
            Account a = Trigger.newMap.get(item.AccountId);
            item.OwnerId = a.OwnerId;        
            contactsToUpdate.add(item);
            
            if (contactsToUpdate.size() == 5000)
            {
                update contactsToUpdate;
                contactsToUpdate.clear();
            }
        }
        
        if (!contactsToUpdate.isEmpty())
        {
            update contactsToUpdate;
        }
    }
}//end of trigger

 

All Answers

Sean TanSean Tan

You're doing a lot of excessive looping to re-map the values... you can use the Trigger.newMap to get the Account relative to it's Account Id.

 

Try something like this to reduce the number of script statements:

 

trigger Account_ChangeOwner on Account (before update) {
    //This trigger updates Lead and Contact Owners based on either a new Account Owner or a selected check box
    Set<String> billingCountries = new Set<String>
    {
        'CA',
        'US',
        'Canada',
        'United States'
    };
    Set<Id> AccountIds = new Set<Id>();
    For (Account Obj : trigger.new)
    {    
        //Only applies to North American Accounts        
        if (Obj.BillingCountry!=null && billingCountry.contains(Obj.BillingCountry) &&
           (Obj.Reset_Contact_Owners__c == true || Trigger.oldMap.get(Obj.Id).OwnerId != Obj.OwnerId))
        {
            AccountIds.add(Obj.Id);
            Obj.Reset_Contact_Owners__c = false;
        }                    
    }//end of loop 1
    
    if (!AccountIds.isEmpty())
    {
        //Gather all relevant Leads and Conacts and add them to Lists
        Lead[] leadsToUpdate = new Lead[]{};
        for (Lead item : [Select l.Id,
                                l.Account_Record__c,
                                l.OwnerId
                                From Lead l
                                where
                                and l.IsConverted=false     
                                and l.Country IN :billingCountries
                                and Account_Record__c IN : AccountIds])
        {
            Account a = Trigger.newMap.get(a.Account_Record__c);
            
            if (a.Named_Account__c)
            {
                item.OwnerId = a.OwnerId;
                leadsToUpdate.add(item);
                
                if (leadsToUpdate.size() == 5000)
                {
                    update leadsToUpdate;
                    leadsToUpdate.clear();
                }
            }
        }
        
        if (!leadsToUpdate.isEmpty())
        {
            update leadsToUpdate;
        }
        
        Contact[] contactsToUpdate = new Contact[]{};
        
        for (Contact item : [Select c.Id,
                                c.AccountId,
                                c.OwnerId
                                From Contact c
                                where
                                and AccountId IN :AccountIds])
        {
            Account a = Trigger.newMap.get(item.AccountId);
            item.OwnerId = a.OwnerId;        
            contactsToUpdate.add(item);
            
            if (contactsToUpdate.size() == 5000)
            {
                update contactsToUpdate;
                contactsToUpdate.clear();
            }
        }
        
        if (!contactsToUpdate.isEmpty())
        {
            update contactsToUpdate;
        }
    }
}//end of trigger

 

This was selected as the best answer
Bhawani SharmaBhawani Sharma

Definitely would go with the Sean's suggestion. Just wanted to add one more thing is, you are updating contacts and leads here. If you have triggers on these 2 objects and you really do not need to execute them, please some some static flag to control the execution.

klocworkklocwork

Thanks so much Sean!

 

Works like a charm.  Great solution - I appreciate the help!