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
Andrew TelfordAndrew Telford 

Bulkify Code

Hi

I have the following trigger that appears to be ok in the sandbox and in production it all validates. I tried to insert some contacts today using Apex Data Loader and got the error relating to too many SOQL queries.

I thought I had bulkified it but evidently this isn't the case. Can some one assist in pointing me in the right direction as to where I have gone wrong please.

Thanks
 
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------
//
//  Name:
//      CI_Trigger_Contact
//
//  Purpose:
//      This will process changes that need to be made based on fields that may change when 
//      contacts are inserted, edited, deleted and undeleted
//  
//  Created by:
//      Andrew Telford
//
//  Date: 
//      2015-07-31
//
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------

trigger CI_Trigger_Contact on Contact ( after insert, after update, after delete, after undelete) 
{

//	Let us prepare some common variables
//----------------------------------------------------------------------------------------------
	system.debug('Set the variables');

	// Common items
	STRING thisContactID;
	STRING thisContactOwnerID;
	STRING userID;
	STRING userProfileID;
	STRING errMsg = '';


//--	Let us get the details relating to this contact change
//----------------------------------------------------------------------------------------------
	system.debug('Contact[]');

//--	Set the details of the trigger to a variable for use through the trigger
//----------------------------------------------------------------------------------------------
	Contact[] strContact;

	if (Trigger.isDelete) 
	{	strContact = Trigger.old;	}
	else
	{	strContact = Trigger.new;	}

	Set<ID> contactID = new Set<ID>();
	FOR(Contact objCont: strContact)
	{
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------
//
//  Owner Change Approved
//
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------

		system.debug('Set the variable values');

	//--	Define any variables required
	//----------------------------------------------------------------------------------------------
		BOOLEAN approvedOwnerChange;
		STRING newContactID = '';

	//--	Assign Values to Variables
	//----------------------------------------------------------------------------------------------
		contactID.add(objCont.Id);
		thisContactID = objCont.Id;
		thisContactOwnerID = objCont.OwnerId;
		userID = userinfo.getUserId();
		userProfileID = userinfo.getProfileId();
		approvedOwnerChange = objCont.Approved_Owner_Change__c;

		system.debug('Determine if owner is to change');

	//--	Check for change forced by workflow
	//----------------------------------------------------------------------------------------------
		if( !objCont.Approved_Owner_Change__c && ( TRIGGER.newMap.get( objCont.Id ).OwnerId != '00520000000noZtAAI' || TRIGGER.newMap.get( objCont.Id ).OwnerId != '00520000000noZtAAI' ) )
		{

	//--	Check to see if we are updating the owner
	//----------------------------------------------------------------------------------------------
			if( TRIGGER.isupdate && objCont.Approved_Owner_Change__c )
			{
				system.debug('Owner is to change');
				LIST<Contact> contsToUpdate = [SELECT Id, OwnerId, Approved_Owner_Change__c, Approved_Owner_Name__c FROM Contact WHERE Id IN :contactID AND Approved_Owner_Change__c = TRUE];

	//--	Set the update list
	//----------------------------------------------------------------------------------------------
				List<Contact> updateOwner = new List<Contact>();

				FOR (Contact cont: contsToUpdate)
				{
					Boolean alterOwner = FALSE;

					IF ( cont.Approved_Owner_Change__c = TRUE )
					{	alterOwner  = TRUE;	}

					if (alterOwner) 
					{
						cont.OwnerID = cont.Approved_Owner_Name__c;
						cont.Approved_Owner_Change__c = FALSE;
						newContactID = cont.Approved_Owner_Name__c;
						updateOwner.add(cont);
					}
				}


	//--	Execute the update
	//----------------------------------------------------------------------------------------------
				if (!updateOwner.isEmpty()) 
				{
					try{ update updateOwner; }
					catch (Exception ex)
					{ System.debug('Could not update New Contact Owner [' + newContactID + '] cause: ' + ex.getCause() + 'APEX TRIGGER: CI_Trigger_Contact');}
				}
			}
		}	//--	End Check for automated change after insert



//
//  End Approve Owner Change
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------


//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------
//
//  Magic Movers
//
//	This works in conjunction with:
//		Validations
//			Magic Mover Addition (03dc00000009014)
//			Magic Mover REmoval (03dc00000009080)
//
//		Workflow Field Update
//			New Contact (01Q20000000Es9Z)
//			Set Magic Mover to False (04Yc000000090G1)
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------

	//--	Magic Movers 
		BOOLEAN magicMover;
		DECIMAL userMagicCount;

	//--	Select the Owner Information
		LIST<User> thisOwner = [Select Magic_Movers_Count__c FROM User WHERE ID = :objCont.OwnerID];
		LIST<User> updateUser = new LIST<User>();
		system.debug( 'Size of thisUser: ' + thisOwner.size());

		for ( User o1: thisOwner )
		{
			if( o1.Magic_Movers_Count__C == NULL )
			{ userMagicCount = 0; }
			else
			{ userMagicCount = o1.Magic_Movers_Count__C; }
            system.debug('No. of Magic Movers = ' + userMagicCount);

            if( !TRIGGER.isinsert && !TRIGGER.isUnDelete )
            {
                
	//--	Has Magic Mover been altered?
	//------------------------------------------------------------------------------------------------------------------------------
                if( objCont.CCC_Adviser__c != TRIGGER.oldMap.get( objCont.Id ).CCC_Adviser__c 
                    && TRIGGER.isupdate && thisContactOwnerID == userID )
                {
	//--	Check to see if the owner has the magic count limit and the change is an addition
	//------------------------------------------------------------------------------------------------------------------------------
                    if( userMagicCount < 21 && objCont.CCC_Adviser__c )
                    {
                        o1.Magic_Movers_Count__c = userMagicCount + 1;
                        updateUser.add(o1);
                        system.debug('size of: ' + updateuser.size());
                    }
                    else if ( !objCont.CCC_Adviser__c )
                    {
                        o1.Magic_Movers_Count__c = userMagicCount - 1;
                        updateUser.add(o1);
                        system.debug('size of: ' + updateuser.size());
                    }
                }
			//--	End check for change in Magic Mover

	//--	Reduce the count when the owner is changed
	//------------------------------------------------------------------------------------------------------------------------------
                else if ( TRIGGER.isUpdate && TRIGGER.isBefore && objCont.OwnerId != TRIGGER.oldMap.get( objCont.Id ).OwnerId )
                { objCont.CCC_Adviser__c = FALSE; }

	//--	When the contact is deleted, reduce the count
	//------------------------------------------------------------------------------------------------------------------------------
                else if ( TRIGGER.isDelete )
                {
                    o1.Magic_Movers_Count__c = userMagicCount - 1;
                    updateUser.add(o1);
                    system.debug('size of: ' + updateuser.size());
                }
                //--	End check for delete
            }

	//--	Process the updates
	//------------------------------------------------------------------------------------------------------------------------------
            system.debug('size of: ' + updateuser.size());
			if (!updateUser.isEmpty()) 
			{
				try{ update updateUser; }
				catch (Exception ex)
				{ objCont.addError(' Error Updating ' + ex.getCause()); System.debug('Could not update User [' + thisContactOwnerID + '] cause: ' + ex.getCause() + 'APEX TRIGGER: CI_Trigger_Contact'); }
			}
		}	//--	For Owner Loop

//
//  End Magic Movers
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------

	}
//
//  End Contact Loop
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------
}

 
Best Answer chosen by Andrew Telford
Suraj GharatSuraj Gharat
Hi Andrew,

You can have as many loops as you want or required by your logic, just remember, no DML/SOQL/SOSL (Or any operation that gets counted against Force.com Governor Limits) inside loop. To do this you may use collections with SOQL/DML, refer below link.

https://developer.salesforce.com/page/Apex_Code_Best_Practices

In your case, you only need to move your SOQLs before loop start so that they get executed only one time. For instance, place below code before loop (line no. 49 in above snippet) and comment lines 87 & 163.
 
Set<Id> ownerIds=new Set<Id>();
for(Contact objCont: strContact)
	ownerIds.add(objCont.ownerId);

LIST<Contact> contsToUpdate = [SELECT Id, OwnerId, Approved_Owner_Change__c, Approved_Owner_Name__c FROM Contact WHERE Id IN :strContact AND Approved_Owner_Change__c = TRUE];
LIST<User> thisOwner = [Select Magic_Movers_Count__c FROM User WHERE ID IN :ownerIds];

 

All Answers

Suraj GharatSuraj Gharat

Both SOQLs are in the loop which iterates over inserted/updated Contacts. If you insert 50+ Contacts at once, you will hit the SOQL governor limit, since the loop will iterate 50+ times and both SOQL will be fired 100+ times (50+ each). 

Here you need to bring both these SOQL out of that loop.
Andrew TelfordAndrew Telford
Thanks for responding ...

So should I have two loops? One for the Approved Owner Change and one for the Magic Movers like
 
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------
//
//  Name:
//      CI_Trigger_Contact
//
//  Purpose:
//      This will process changes that need to be made based on fields that may change when 
//      contacts are inserted, edited, deleted and undeleted
//  
//  Created by:
//      Andrew Telford
//
//  Date: 
//      2015-07-31
//
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------

trigger CI_Trigger_Contact on Contact ( after insert, after update, after delete, after undelete) 
{

//	Let us prepare some common variables
//----------------------------------------------------------------------------------------------
	system.debug('Set the variables');

	// Common items
	STRING thisContactID;
	STRING thisContactOwnerID;
	STRING userID;
	STRING userProfileID;
	STRING errMsg = '';


//--	Let us get the details relating to this contact change
//----------------------------------------------------------------------------------------------
	system.debug('Contact[]');

//--	Set the details of the trigger to a variable for use through the trigger
//----------------------------------------------------------------------------------------------
	Contact[] strContact;

	if (Trigger.isDelete) 
	{	strContact = Trigger.old;	}
	else
	{	strContact = Trigger.new;	}

	Set<ID> contactID = new Set<ID>();
	FOR(Contact objCont: strContact)
	{
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------
//
//  Owner Change Approved
//
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------

		system.debug('Set the variable values');

	//--	Define any variables required
	//----------------------------------------------------------------------------------------------
		BOOLEAN approvedOwnerChange;
		STRING newContactID = '';

	//--	Assign Values to Variables
	//----------------------------------------------------------------------------------------------
		contactID.add(objCont.Id);
		thisContactID = objCont.Id;
		thisContactOwnerID = objCont.OwnerId;
		userID = userinfo.getUserId();
		userProfileID = userinfo.getProfileId();
		approvedOwnerChange = objCont.Approved_Owner_Change__c;

		system.debug('Determine if owner is to change');

	//--	Check for change forced by workflow
	//----------------------------------------------------------------------------------------------
		if( !objCont.Approved_Owner_Change__c && ( TRIGGER.newMap.get( objCont.Id ).OwnerId != '00520000000noZtAAI' || TRIGGER.newMap.get( objCont.Id ).OwnerId != '00520000000noZtAAI' ) )
		{

	//--	Check to see if we are updating the owner
	//----------------------------------------------------------------------------------------------
			if( TRIGGER.isupdate && objCont.Approved_Owner_Change__c )
			{
				system.debug('Owner is to change');
				LIST<Contact> contsToUpdate = [SELECT Id, OwnerId, Approved_Owner_Change__c, Approved_Owner_Name__c FROM Contact WHERE Id IN :contactID AND Approved_Owner_Change__c = TRUE];

	//--	Set the update list
	//----------------------------------------------------------------------------------------------
				List<Contact> updateOwner = new List<Contact>();

				FOR (Contact cont: contsToUpdate)
				{
					Boolean alterOwner = FALSE;

					IF ( cont.Approved_Owner_Change__c = TRUE )
					{	alterOwner  = TRUE;	}

					if (alterOwner) 
					{
						cont.OwnerID = cont.Approved_Owner_Name__c;
						cont.Approved_Owner_Change__c = FALSE;
						newContactID = cont.Approved_Owner_Name__c;
						updateOwner.add(cont);
					}
				}


	//--	Execute the update
	//----------------------------------------------------------------------------------------------
				if (!updateOwner.isEmpty()) 
				{
					try{ update updateOwner; }
					catch (Exception ex)
					{ System.debug('Could not update New Contact Owner [' + newContactID + '] cause: ' + ex.getCause() + 'APEX TRIGGER: CI_Trigger_Contact');}
				}
			}
		}	//--	End Check for automated change after insert



//
//  End Approve Owner Change
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------

	}
//
//  End Contact Loop 1
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------



	FOR(Contact objCont: strContact)
	{

//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------
//
//  Magic Movers
//
//	This works in conjunction with:
//		Validations
//			Magic Mover Addition (03dc00000009014)
//			Magic Mover REmoval (03dc00000009080)
//
//		Workflow Field Update
//			New Contact (01Q20000000Es9Z)
//			Set Magic Mover to False (04Yc000000090G1)
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------

	//--	Magic Movers 
		BOOLEAN magicMover;
		DECIMAL userMagicCount;

	//--	Select the Owner Information
		LIST<User> thisOwner = [Select Magic_Movers_Count__c FROM User WHERE ID = :objCont.OwnerID];
		LIST<User> updateUser = new LIST<User>();
		system.debug( 'Size of thisUser: ' + thisOwner.size());

		for ( User o1: thisOwner )
		{
			if( o1.Magic_Movers_Count__C == NULL )
			{ userMagicCount = 0; }
			else
			{ userMagicCount = o1.Magic_Movers_Count__C; }
            system.debug('No. of Magic Movers = ' + userMagicCount);

            if( !TRIGGER.isinsert && !TRIGGER.isUnDelete )
            {
                
	//--	Has Magic Mover been altered?
	//------------------------------------------------------------------------------------------------------------------------------
                if( objCont.CCC_Adviser__c != TRIGGER.oldMap.get( objCont.Id ).CCC_Adviser__c 
                    && TRIGGER.isupdate && thisContactOwnerID == userID )
                {
	//--	Check to see if the owner has the magic count limit and the change is an addition
	//------------------------------------------------------------------------------------------------------------------------------
                    if( userMagicCount < 21 && objCont.CCC_Adviser__c )
                    {
                        o1.Magic_Movers_Count__c = userMagicCount + 1;
                        updateUser.add(o1);
                        system.debug('size of: ' + updateuser.size());
                    }
                    else if ( !objCont.CCC_Adviser__c )
                    {
                        o1.Magic_Movers_Count__c = userMagicCount - 1;
                        updateUser.add(o1);
                        system.debug('size of: ' + updateuser.size());
                    }
                }
			//--	End check for change in Magic Mover

	//--	Reduce the count when the owner is changed
	//------------------------------------------------------------------------------------------------------------------------------
                else if ( TRIGGER.isUpdate && TRIGGER.isBefore && objCont.OwnerId != TRIGGER.oldMap.get( objCont.Id ).OwnerId )
                { objCont.CCC_Adviser__c = FALSE; }

	//--	When the contact is deleted, reduce the count
	//------------------------------------------------------------------------------------------------------------------------------
                else if ( TRIGGER.isDelete )
                {
                    o1.Magic_Movers_Count__c = userMagicCount - 1;
                    updateUser.add(o1);
                    system.debug('size of: ' + updateuser.size());
                }
                //--	End check for delete
            }

	//--	Process the updates
	//------------------------------------------------------------------------------------------------------------------------------
            system.debug('size of: ' + updateuser.size());
			if (!updateUser.isEmpty()) 
			{
				try{ update updateUser; }
				catch (Exception ex)
				{ objCont.addError(' Error Updating ' + ex.getCause()); System.debug('Could not update User [' + thisContactOwnerID + '] cause: ' + ex.getCause() + 'APEX TRIGGER: CI_Trigger_Contact'); }
			}
		}	//--	For Owner Loop

//
//  End Magic Movers
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------

	}
//
//  End Contact Loop 2
//
//--------------------------------------------------------------------------------------------------------------------------------------
//--------------------------------------------------------------------------------------------------------------------------------------}

 
Suraj GharatSuraj Gharat
Hi Andrew,

You can have as many loops as you want or required by your logic, just remember, no DML/SOQL/SOSL (Or any operation that gets counted against Force.com Governor Limits) inside loop. To do this you may use collections with SOQL/DML, refer below link.

https://developer.salesforce.com/page/Apex_Code_Best_Practices

In your case, you only need to move your SOQLs before loop start so that they get executed only one time. For instance, place below code before loop (line no. 49 in above snippet) and comment lines 87 & 163.
 
Set<Id> ownerIds=new Set<Id>();
for(Contact objCont: strContact)
	ownerIds.add(objCont.ownerId);

LIST<Contact> contsToUpdate = [SELECT Id, OwnerId, Approved_Owner_Change__c, Approved_Owner_Name__c FROM Contact WHERE Id IN :strContact AND Approved_Owner_Change__c = TRUE];
LIST<User> thisOwner = [Select Magic_Movers_Count__c FROM User WHERE ID IN :ownerIds];

 
This was selected as the best answer
Neetu_BansalNeetu_Bansal
Hi Andrew,

I re-write the trigger, use the below code and if will resolved the SOQL error. But I doubt that it will throw some other errors like duplicate records.
As I go through the code, I found that there are various flaws, if you let me know your exact requirement, I'll help you out in writing the correct code.

/**
 * Name: CI_Trigger_Contact
 * Purpose: This will process changes that need to be made based on fields that may change when 
            contacts are inserted, edited, deleted and undeleted
 * Created by: Andrew Telford
 * Date: 2015-07-31
**/
trigger CI_Trigger_Contact on Contact ( after insert, after update, after delete, after undelete) 
{
    STRING thisContactID;
    STRING thisContactOwnerID;
    STRING userID;
    STRING userProfileID;
    STRING errMsg = '';
    List<Contact> strContact;

    if( trigger.isDelete ) 
    {
        strContact = trigger.old;
    }
    else
    {
        strContact = trigger.new;
    }

    Set<Id> contactID = new Set<Id>();
    Set<Id> ownerIds = new Set<Id>();
    
    List<Contact> updateOwner = new List<Contact>();
    List<User> updateUser = new List<User>();
    List<Contact> contsToUpdate = [ SELECT Id, OwnerId, Approved_Owner_Change__c, Approved_Owner_Name__c FROM Contact
                                    WHERE Id IN: strContact AND Approved_Owner_Change__c = true ];
    for( Contact objCont : strContact)
    {
        ownerIds.add( objCont.OwnerId );
    }
    
    Map<Id, User> userMap = new Map<Id, User>([ Select Magic_Movers_Count__c FROM User WHERE ID IN: ownerIds ]);
    
    for( Contact objCont : strContact)
    {
        BOOLEAN approvedOwnerChange;
        STRING newContactID = '';
        contactID.add(objCont.Id);
        thisContactID = objCont.Id;
        thisContactOwnerID = objCont.OwnerId;
        userID = Userinfo.getUserId();
        userProfileID = Userinfo.getProfileId();
        approvedOwnerChange = objCont.Approved_Owner_Change__c;
       
        if( !objCont.Approved_Owner_Change__c
            && trigger.newMap.get( objCont.Id ).OwnerId != '00520000000noZtAAI' )
        {
            if( trigger.isUpdate && objCont.Approved_Owner_Change__c )
            {
                for( Contact cont: contsToUpdate )
                {
                    Boolean alterOwner = false;

                    if( cont.Approved_Owner_Change__c )
                    {
                        alterOwner  = true;
                    }

                    if( alterOwner ) 
                    {
                        cont.OwnerID = cont.Approved_Owner_Name__c;
                        cont.Approved_Owner_Change__c = FALSE;
                        newContactID = cont.Approved_Owner_Name__c;
                        updateOwner.add(cont);
                    }
                }
            }
        }   

        Boolean magicMover;
        Decimal userMagicCount;
        
        User u = ( userMap.containsKey( objCont.OwnerId ) ? userMap.get( objCont.OwnerId ) : null );
        if( u != null )
        {
            if( u.Magic_Movers_Count__c == null )
            {
                userMagicCount = 0;
            }
            else
            {
                userMagicCount = u.Magic_Movers_Count__C;
            }

            if( !trigger.isInsert && !trigger.isUnDelete )
            {
                if( trigger.isUpdate
                    && objCont.CCC_Adviser__c != trigger.oldMap.get( objCont.Id ).CCC_Adviser__c 
                    && thisContactOwnerID == userID )
                {
                    if( userMagicCount < 21 && objCont.CCC_Adviser__c )
                    {
                        u.Magic_Movers_Count__c = userMagicCount + 1;
                        updateUser.add(u);
                    }
                    else if( !objCont.CCC_Adviser__c )
                    {
                        u.Magic_Movers_Count__c = userMagicCount - 1;
                        updateUser.add(u);
                    }
                }
                else if( trigger.isUpdate
                        && trigger.isBefore && objCont.OwnerId != trigger.oldMap.get( objCont.Id ).OwnerId )
                {
                    objCont.CCC_Adviser__c = false;
                }
                else if( trigger.isDelete )
                {
                    u.Magic_Movers_Count__c = userMagicCount - 1;
                    updateUser.add(u);
                }
            }
        }
    }
    
    if (!updateOwner.isEmpty()) 
    {
        try
        {
            update updateOwner;
        }
        catch(Exception ex)
        {
            System.debug('cause: ' + ex.getCause() + 'APEX TRIGGER: CI_Trigger_Contact');
        }
    }
    
    if (!updateUser.isEmpty()) 
    {
        try
        {
            update updateUser;
        }
        catch(Exception ex)
        {
            System.debug('Could not update User [' + thisContactOwnerID + '] cause: ' + ex.getCause() + 'APEX TRIGGER: CI_Trigger_Contact');
        }
    }
}

Thanks,
Neetu
Andrew TelfordAndrew Telford
Thank you both for your input. Once I moved the select statements all went well with the validation.