function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
LBartonLBarton 

Why do I get the Too Many SOQL Queries: 101 for This trigger

I think I have done all I can to "bulkify" this trigger but if I update 200 opportunities at once then I get the error about too many SOQL queries. I can't see anything more I need to do for this trigger. What else is there to do?

trigger Update_Sam_Marketing_Customer_Field on Opportunity (after insert, after update, after delete) {

try{
    //the trigger will update the account's SAM Marketing Customer field
    if (! trigger.isDelete) {
    
  List<Id> oppIds = new List<Id>() ;
  List<Id> AccountIds = new List<Id>() ;
  List<Account> AcctToUpdate = new List<Account>() ;
     
      for (opportunity op : trigger.New){
        oppIds.add(op.Id);
        AccountIds.add(op.AccountId);
        
  Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, StageName from Opportunity where id in :oppIds and
   name = 'Security Audit Manager'and Status__c != 'Inactive']) ;   
    
  Map<Id,Account> acctMap = new Map<Id,Account>([select id, SAM_Marketing_Customer__c, name, Closed_Won_Opps__c, Type 
   from Account where id in :AccountIDs]);
  
        //Find the account for this opportunity which is being updated
        Account oAccount =  acctMap.get(op.AccountId);
        Opportunity SamOpportunity = oppMap.get(op.Id);

         if (oppMap.isEmpty()){  //No SAM opportunities
    if (oAccount.Closed_Won_Opps__c == 0 && SamOpportunity.StageName != 'Closed Won') {
     oAccount.SAM_Marketing_Customer__c = 5;
    }
    else {
     oAccount.SAM_Marketing_Customer__c = 4;
    }
    AcctToUpdate.add(oAccount);
         }
         else { //There are SAM opportunities so see how many of them are closed/won
          Integer iCountClosedWon = 0;
          
      for(Opportunity samMap: oppMap.values()){       
     if (samMap.StageName == 'Closed Won') {
      iCountClosedWon += 1;
     }      
      }   
          
          if (iCountClosedWon > 0) {
     oAccount.SAM_Marketing_Customer__c = 1;
          }
          else {
           if (oAccount.Closed_Won_Opps__c == 0){
            oAccount.SAM_Marketing_Customer__c = 3;
      //update oAccount;  
           }
           else {
             oAccount.SAM_Marketing_Customer__c = 2;
           }
           
          }
          AcctToUpdate.add(oAccount);
         }
       }
       update AcctToUpdate;
   }
 
 if (trigger.isDelete) {
    
  List<Id> oppIds = new List<Id>() ;
  List<Id> AccountIds = new List<Id>() ;
  List<Account> AcctToUpdate = new List<Account>() ;
    
     for (opportunity op : trigger.Old){
        oppIds.add(op.Id);
        AccountIds.add(op.AccountId);
        
  Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, StageName from Opportunity where id in :oppIds and
   name = 'Security Audit Manager'and Status__c != 'Inactive']) ;   
    
  Map<Id,Account> acctMap = new Map<Id,Account>([select id, SAM_Marketing_Customer__c, name, Closed_Won_Opps__c, Type 
   from Account where id in :AccountIDs]);
  
        //Find the account for this opportunity which is being updated
        Account oAccount =  acctMap.get(op.AccountId);
        Opportunity SamOpportunity = oppMap.get(op.Id);

         if (oppMap.isEmpty()){  //No SAM opportunities
    if (oAccount.Closed_Won_Opps__c == 0 && SamOpportunity.StageName != 'Closed Won') {
     oAccount.SAM_Marketing_Customer__c = 5;
    }
    else {
     oAccount.SAM_Marketing_Customer__c = 4;
    }
    AcctToUpdate.add(oAccount);
         }
         else { //There are SAM opportunities so see how many of them are closed/won
          Integer iCountClosedWon = 0;
          
      for(Opportunity samMap: oppMap.values()){       
     if (samMap.StageName == 'Closed Won') {
      iCountClosedWon += 1;
     }      
      }   
          
          if (iCountClosedWon > 0) {
     oAccount.SAM_Marketing_Customer__c = 1;
          }
          else {
           if (oAccount.Closed_Won_Opps__c == 0){
            oAccount.SAM_Marketing_Customer__c = 3;
      //update oAccount;  
           }
           else {
             oAccount.SAM_Marketing_Customer__c = 2;
           }
          }
          AcctToUpdate.add(oAccount);
         }
     }
     update AcctToUpdate;    
   }
}
catch (Exception e ){
            System.debug('Create customer field trigger exception ' + e.getMessage());
            
      }
 }


MTBRiderMTBRider
You have to move the the Updates and Selects out of the For statements.
Vinay JVinay J
Almost all your queries are within for loop, which is not a good practice and the reason of your error.
shiv@SFDCshiv@SFDC
Because you are doing following queries in for loop, that's why you are getting so many sql errors..

Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, StageName from Opportunity where id in :oppIds and name = 'Security Audit Manager'and Status__c != 'Inactive']) ;  
   
   Map<Id,Account> acctMap = new Map<Id,Account>([select id, SAM_Marketing_Customer__c, name, Closed_Won_Opps__c, Type from Account where id in :AccountIDs]);



I have modified the code, please check it once..
trigger Update_Sam_Marketing_Customer_Field on Opportunity (after insert, after update, after delete) 
{
	try
	{
		//the trigger will update the account's SAM Marketing Customer field
		if (! trigger.isDelete) 
		{
    
			List<Id> oppIds = new List<Id>() ;
			List<Id> AccountIds = new List<Id>() ;
			List<Account> AcctToUpdate = new List<Account>() ;
     
		    for (opportunity op : trigger.New)
		    {
				oppIds.add(op.Id);
			    AccountIds.add(op.AccountId);
		    } 
	  
			Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, StageName from Opportunity where id in :oppIds and name = 'Security Audit Manager'and Status__c != 'Inactive']) ;   
    
			Map<Id,Account> acctMap = new Map<Id,Account>([select id, SAM_Marketing_Customer__c, name, Closed_Won_Opps__c, Type from Account where id in :AccountIDs]);
	
			for (opportunity op : trigger.New)
		    {
				//Find the account for this opportunity which is being updated
			
				Account oAccount =  acctMap.get(op.AccountId);
				Opportunity SamOpportunity = oppMap.get(op.Id);

				if (oppMap.isEmpty())
				{  //No SAM opportunities
					if (oAccount.Closed_Won_Opps__c == 0 && SamOpportunity.StageName != 'Closed Won') {
					 oAccount.SAM_Marketing_Customer__c = 5;
					}
					else {
					 oAccount.SAM_Marketing_Customer__c = 4;
					}
					
					AcctToUpdate.add(oAccount);
				}
				else{ //There are SAM opportunities so see how many of them are closed/won
					Integer iCountClosedWon = 0;
			  
					for(Opportunity samMap: oppMap.values()){       
						if (samMap.StageName == 'Closed Won') {
							iCountClosedWon += 1;
						}      
					}   
			  
					if (iCountClosedWon > 0) {
						oAccount.SAM_Marketing_Customer__c = 1;
					}
					else {
						if (oAccount.Closed_Won_Opps__c == 0){
							oAccount.SAM_Marketing_Customer__c = 3;
							//update oAccount;  
						}
					   else {
						 oAccount.SAM_Marketing_Customer__c = 2;
					   }
			   
					}
					AcctToUpdate.add(oAccount);
				}
			}
       
			update AcctToUpdate;
		}
 
		if (trigger.isDelete) 
		{
    
			List<Id> oppIds = new List<Id>() ;
			List<Id> AccountIds = new List<Id>() ;
			List<Account> AcctToUpdate = new List<Account>() ;
    
			for (opportunity op : trigger.Old){
				oppIds.add(op.Id);
				AccountIds.add(op.AccountId);
            }
			for (opportunity op : trigger.Old){	
				Map<Id,Opportunity> oppMap = new Map<Id,Opportunity>([Select id, StageName from Opportunity where id in :oppIds and name = 'Security Audit Manager'and Status__c != 'Inactive']) ;   
    
				Map<Id,Account> acctMap = new Map<Id,Account>([select id, SAM_Marketing_Customer__c, name, Closed_Won_Opps__c, Type from Account where id in :AccountIDs]);
  
				//Find the account for this opportunity which is being updated
				Account oAccount =  acctMap.get(op.AccountId);
				Opportunity SamOpportunity = oppMap.get(op.Id);

				if (oppMap.isEmpty()){  //No SAM opportunities
					if (oAccount.Closed_Won_Opps__c == 0 && SamOpportunity.StageName != 'Closed Won') {
					 oAccount.SAM_Marketing_Customer__c = 5;
					}
					else {
					 oAccount.SAM_Marketing_Customer__c = 4;
					}
					AcctToUpdate.add(oAccount);
				}
				else { //There are SAM opportunities so see how many of them are closed/won
					Integer iCountClosedWon = 0;
          
					for(Opportunity samMap: oppMap.values()){       
						if (samMap.StageName == 'Closed Won') {
							iCountClosedWon += 1;
						}      
					}   
          
					if (iCountClosedWon > 0) {
						oAccount.SAM_Marketing_Customer__c = 1;
					}
					else {
						if (oAccount.Closed_Won_Opps__c == 0){
							oAccount.SAM_Marketing_Customer__c = 3;
							//update oAccount;  
						}
						else {
							oAccount.SAM_Marketing_Customer__c = 2;
						}
					}
					AcctToUpdate.add(oAccount);
				}
			}
			update AcctToUpdate;    
		}
	}
	catch (Exception e ){
            System.debug('Create customer field trigger exception ' + e.getMessage());
    }
 }