+ Start a Discussion
Andrew TelfordAndrew Telford 

Trigger Assistance

Hi

I am trying to get a trigger based on Contact to funciton but I am continually getting an error relating to too many SOQL Queries. The error states I am getting the error on Line 60.

Should I create a 2nd Contact Trigger file and have it only execute AFTER update?
LIST<Contact> thisCount = [SELECT Id FROM Contact WHERE OwnerId IN :ownerIds AND CCC_Adviser__c = TRUE];
 
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------
//
//  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-08-17
//
//----------------------------------------------------------------------------------------------
//----------------------------------------------------------------------------------------------

trigger CI_Trigger_Contact on Contact ( after insert, after update, before 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>();
	Set<ID> ownerIds = new Set<ID>();
	for(Contact objCont: strContact)
	{	ownerIds.add(objCont.ownerId);	}

    //--	For Owner Change Approved
	//----------------------------------------------------------------------------------------------
	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 Magic Movers
	//----------------------------------------------------------------------------------------------
    LIST<User> thisOwner = [Select Id FROM User WHERE ID IN :ownerIds];
//-- Line 60 is below
    LIST<Contact> thisCount = [SELECT Id FROM Contact WHERE OwnerId IN :ownerIds AND CCC_Adviser__c = TRUE];

	FOR(Contact objCont: strContact)
	{

	//----------------------------------------------------------------------------------------------
	//----------------------------------------------------------------------------------------------
	//
	//  Owner Change Approved
	//
	//
	//

	//--	Define any variables required
	//----------------------------------------------------------------------------------------------
		system.debug('Set the variable values');
		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;


	//--	Check to see if we are updating the owner
	//----------------------------------------------------------------------------------------------
		system.debug('Owner is to change');
		if( TRIGGER.isupdate && objCont.Approved_Owner_Change__c )
		{

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

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

				IF ( cont.Approved_Owner_Change__c )
				{
					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 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 
	//------------------------------------------------------------------------------------------------------------------------------
		IF ( TRIGGER.isAfter )
        {
            BOOLEAN magicMover;
			DECIMAL userMagicCount;

	//--	Select the Owner Information
	//------------------------------------------------------------------------------------------------------------------------------
            system.debug( 'Size of thisUser: ' + thisOwner.size());
            LIST<User> updateUser = new LIST<User>();

            for ( User o1: thisOwner )
            {
                o1.Magic_Movers_Count__c = thisCount.size();
                updateUser.Add(o1);

            }	//--	For Owner Loop
	//--	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'); }
                }
        }	//--	End Check for After          

	//
	//  End Magic Movers
	//
	//--------------------------------------------------------------------------------------------------------------------------------------
	//--------------------------------------------------------------------------------------------------------------------------------------
		//objCont.addError(errMsg);
	}

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

 
Edward EncarnacionEdward Encarnacion
I think your big for loop also includes the update command in it and since your also updating contacts, this leads to multiple trigger executions. Hence, SOQL query limit is breached. 

Probably need to refactor trigger with bulkification in mind so at most only one update command is called.

Also you might want to look at structuring your trigger to gain clarity around the current trigger context
Andrew TelfordAndrew Telford
Thanks Edward for your response.

I thought I had bulkified the query?

As for structure, I have commented the code to ensure the processes completed are cleary identified. Is there something else I should be doing differently?