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
sudhirn@merunetworks.comsudhirn@merunetworks.com 

Trigger Conflict with Too many SOQL queries: 101

Hi,

 I wrote a trigger on account below is the trigger is working fine when account is update it is working as expected 
trigger logosince on Account (Before update) 
{
  Try
 {
   Set<Id> accountId = new Set<Id>();
   List<Id> chkoneprt = new List<Id>();
   List<Id> chkoneid = new List<Id>();
   for (Account a : Trigger.New) 
   {     
      accountId.add(a.id);
      chkoneid.add(a.id);
      chkoneprt.add(a.parentid);
      
      } 
       
      /* not required for ( account actone : [select id,parentid from account 
                                where id in :accountId 
                               ])
        {
          chkoneid.add(actone.id);  
          chkoneprt.add(actone.parentid);  
        }  */
        
          system.debug('ID' + chkoneid);
         system.debug('Parent ID' + chkoneprt);
         
           List<Account> accounts =   [select id,parentid from account 
                                    where  ( parentid != '' and   parentid in :chkoneprt ) or 
                                           ( parentid != '' and   parentid in :chkoneid ) or
                                                 id in :chkoneid ];
 
        List<Id> finalid = new List<Id>();
        for ( account actpar :  accounts )
        {
          finalid.add(actpar.id);  
            
        }
        system.debug('ID' + chkoneid); 
        system.debug('Final ID' + finalid);        
             
     List<AggregateResult> gr = [ 
     SELECT min(closedate) from opportunity 
     WHERE accountid IN :chkoneid or accountid in :finalid];
   
       
     for (AggregateResult ar : gr)  {
        system.debug('Min Opp date' + (Datetime)ar.get('expr0'));
        for(Account act : trigger.new) 
              {
              act.Logo_Since__c = (Datetime)ar.get('expr0') + 1;
              
              }
         }     
     
 }
 
  catch (System.NullPointerException e) {
    system.debug('Null Exception');
  }    
       
}

There is another trigger on opportunity below isthe trigger which already exist in the system before creating above trigger on account
 
rigger Public_Opportunity_View_trg on Opportunity (After Insert, After Update, After Delete) {

/* Fires during INSERT */
if(trigger.isInsert)
 {
   List<Public_Opportunity_View__c> POVi = new list<Public_Opportunity_View__c>();
   List<Opportunity_Compensation__c> OCi = new list<Opportunity_Compensation__c>();
   
 for(Opportunity opp :trigger.new)
  {
   Public_Opportunity_View__c POV = new Public_Opportunity_View__c();
   Opportunity_Compensation__c OC = new Opportunity_Compensation__c();
    
      POV.Account_ID__c = opp.accountid;
      POV.Opportunity_ID__c    =  opp.ID;
      POV.Close_Date__c        =  opp.CloseDate;
      POV.Opportunity_Name__c  =  opp.Name;
      POV.Name  =  opp.Name;
      POV.Stage__c             =  opp.StageName;
      POV.Lead_Source__c = opp.LeadSource;
      POVi.add(POV);
      
      /* Insert into Opportunity_Compensation__c */
      OC.Name =  opp.Name;
      OC.Opportunity_ID__c =  opp.ID;
      OCi.add(OC);
   
    }
     
     Insert POVi;
     Insert OCi;
   
   }

/* Fires during UPDATE */
  if(trigger.isUpdate)
   {
     List <Opportunity> Opp = [ SELECT id,accountid,CloseDate,Name,StageName,LeadSource FROM Opportunity WHERE id = :Trigger.newMap.keySet()]; 
  List <Public_Opportunity_View__c> Pubopp = [  select id,Account_ID__c,Opportunity_ID__c,Close_Date__c,Opportunity_Name__c,Stage__c,Lead_Source__c   
                                                from Public_Opportunity_View__c
                                                WHERE Opportunity_ID__c = :Trigger.newMap.keySet()];

  
  for ( opportunity opps : opp )
  {
    for (Public_Opportunity_View__c Pubopps : Pubopp )
     {
       
        Pubopps.Account_ID__c        =  opps.accountid;
        Pubopps.Opportunity_ID__c    =  opps.ID;
        Pubopps.Close_Date__c        =  opps.CloseDate;
        Pubopps.Opportunity_Name__c  =  opps.Name;
        Pubopps.Name  =  opps.Name;
        Pubopps.Stage__c             =  opps.StageName;
        Pubopps.Lead_Source__c   =       opps.LeadSource;
      
      
      }
  
    update Pubopp;
    } 
     
   }  
 
   /* Fires during DELETE*/
 if(Trigger.isDelete) 
 {
     
  for(Opportunity opp :trigger.old)
  {   
     
    List<Public_Opportunity_View__c> existopp = [Select Id from Public_Opportunity_View__c where Opportunity_ID__c = :opp.id];
     
     delete existopp;
   }   
    
    List<Public_Opportunity_View__c> nullopp = [Select Id from Public_Opportunity_View__c where Opportunity_ID__c = NULL]; 
     
     delete nullopp;
  }

Now the issue am facing is when converting lead to account and opportunity I get below error message

Error: System.LimitException: Too many SOQL queries: 101 Trigger.Public_Opportunity_View_trg: line 39, column 1

When trigger logosince is disable I dont get any error message it is working as expected. Please suggest me how to resolve this issue 

Thanks
Sudhir
 
Best Answer chosen by sudhirn@merunetworks.com
Arun KumarArun Kumar
Hi Sudhir,

I think it's dur to the recursion. Please go with the below code:

Create a new class named :
checkRecursive
Public Class checkRecursive{
    private static boolean run = true;
    public static boolean runOnce(){
    if(run){
     run=false;
     return true;
    }else{
        return run;
    }
    }
}


Trigger code :

trigger Public_Opportunity_View_trg on Opportunity (After Insert, After Update, After Delete) {

    if(checkRecursive.runOnce())
    {
    //place all your code here            
    }

}
Refer: https://help.salesforce.com/apex/HTViewSolution?id=000133752&language=en_US

All Answers

Arun KumarArun Kumar
Hi Sudhir,

I think it's dur to the recursion. Please go with the below code:

Create a new class named :
checkRecursive
Public Class checkRecursive{
    private static boolean run = true;
    public static boolean runOnce(){
    if(run){
     run=false;
     return true;
    }else{
        return run;
    }
    }
}


Trigger code :

trigger Public_Opportunity_View_trg on Opportunity (After Insert, After Update, After Delete) {

    if(checkRecursive.runOnce())
    {
    //place all your code here            
    }

}
Refer: https://help.salesforce.com/apex/HTViewSolution?id=000133752&language=en_US
This was selected as the best answer
Deepthi BDeepthi B
Hi Sudhir,
When query operations are placed inside a for loop, database operations are invoked once per iteration of the loop making it very easy to reach these governor limits. Kindly query the records before using them inside FOR loop.

For more reference, look at the best practices for writing triggers https://developer.salesforce.com/page/Apex_Code_Best_Practices
sudhirn@merunetworks.comsudhirn@merunetworks.com
Thanks Arun you solved my problem. Now only convern is code coverage droped to 25%  Below is the test class which am using for trigger Public_Opportunity_View_trg please suggest me how to get this up am not able to deploy this to production becasue of this issue
 
@isTest(SeeAllData = true)
private class  Public_Opportunity_View
{
  public static testmethod void testopp()
    { 
 
  test.startTest();
  
    Account testAcc = new Account (Name = 'Test Sudhir', Email_Domain__c = 'sudhir@samarth.com');
       {
        insert testAcc;
        }
  
 /*  Opportunity opp = new Opportunity (Name = 'Test Name',                       
                                     AccountId = testAcc.Id,
                                     Type = 'New Customer',                                   
                                     StageName = 'Open',
                                     Amount = 50000.00,
                                     CloseDate = System.today(),
                                     LeadSource = 'Community'
                                     ); */
                                     
                                     
   Opportunity opp = new Opportunity ( name = 'test', accountid = testAcc.Id, CloseDate=system.today(),
    StageName = 'Omitted',Type = 'New Customer',ForecastCategoryName = 'Omitted', LeadSource = 'MKTG-SEM'   );
    
    
   Public_Opportunity_View__c  po = new Public_Opportunity_View__c(
      Account_ID__c = opp.accountid,
      Opportunity_ID__c    =  opp.ID,
      Close_Date__c        =  opp.CloseDate,
      Opportunity_Name__c  =  opp.Name,
      Name  =  opp.Name,
      Stage__c             =  opp.StageName,
      Lead_Source__c = opp.LeadSource );
   
     
  
   insert opp;
   insert po;
   
   update opp;
   update po;
   
   delete opp;
   
  test.stopTest();
   
    }

 }

Thanks
Sudhir
Arun KumarArun Kumar
Hi Sudhir,

put this line in your test method on the top:
checkRecursive.run = true;