Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mayank.msMayank.ms 

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: System.LimitException: Too many SOQL queries: 101

Hello Guys,

I have created a trigger on contact that insert a new opportunity. It is working properly but when we perform bulk operation then it's giving the error CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: OpportunityCreatorOnContact: System.LimitException: Too many SOQL queries: 101 

See the trigger below and please provide me the solution for it

trigger OpportunityCreatorOnContact on Contact (before update) {
   
    if(OpportunityCreatorOnContactHandler.isFirstTime){
        OpportunityCreatorOnContactHandler.isFirstTime=false;
  
       List<Contact> ContList = new List<Contact>([SELECT Name,Contact.Account.Id,Contact.Account.Name, Most_recent_Opportunity_Stage__c, Contact.Account.OwnerId,Quarantine__c,mkto71_Lead_Score__c from Contact where id IN :Trigger.newMap.keySet()]); 
                for(Contact c : trigger.new)
                {   
                    for(Contact c1: ContList){  

                      if(c.Most_recent_Opportunity_Stage__c=='Closed Lost' && trigger.oldMap.get(c.Id).Create_New_Opp__c==false && c.Create_New_Opp__c==true  && c.mkto71_Lead_Score__c>=100 ) {   
                        
			Opportunity opp = new Opportunity(Name=c1.Account.Name, AccountID=c.AccountId, StageName='Open', CloseDate=Date.Today().addDays(+30),ownerID = c1.Account.OwnerId);
                        insert opp;
                      	
			opportunityCOntactRole oppCOn = new opportunityCOntactRole(OpportunityId=opp.id, contactId= c.Id, isPrimary=true);
                      	insert oppCon;

                      }    
                  }    
                }     

                        
          }    
}
Thanks

 

 

 

 

Best Answer chosen by Mayank.ms
sunny522sunny522
Hi Kamran,
       You are doing DML operations inside for loop.we need to avoid it.
Please use the code below
trigger OpportunityCreatorOnContact on Contact (before update) {
   
    if(OpportunityCreatorOnContactHandler.isFirstTime){
        OpportunityCreatorOnContactHandler.isFirstTime=false;
        Set<Id> conIdSet = new Set<Id>();
        for(Contact c : trigger.new)
        {
            conIdSet.add(c.id);
        }
        List<Contact> ContList = new List<Contact>([SELECT Name,Contact.Account.Id,Contact.Account.Name, Most_recent_Opportunity_Stage__c, Contact.Account.OwnerId,Quarantine__c,mkto71_Lead_Score__c from Contact where id IN :conIdSet]); 
        List<opportunity> lstOppToInsert = new List<opportunity>();
        Map<String,Id> accountContactMap = new Map<String,Id>();
        for(Contact c1: ContList){  

            if(c1.Most_recent_Opportunity_Stage__c=='Closed Lost' && trigger.oldMap.get(c1.Id).Create_New_Opp__c==false && c1.Create_New_Opp__c==true  && c1.mkto71_Lead_Score__c>=100 ) {   
            
            Opportunity opp = new Opportunity(Name=c1.Account.Name, AccountID=c1.AccountId, StageName='Open', CloseDate=Date.Today().addDays(+30),ownerID = c1.Account.OwnerId);
            lstOppToInsert.add(opp);
            accountContactMap.put(c1.Account.Name+c1.AccountId,c1.id);
            }    
        }  
        insert lstOppToInsert;
        List<opportunityCOntactRole> lstOppContactRole = new List<opportunityCOntactRole>();
        for(Opportunity opp:lstOppToInsert) {
            if(accountContactMap.containsKey(opp.name+opp.AccountId)) {
            opportunityCOntactRole oppCOn = new opportunityCOntactRole(OpportunityId=opp.id, contactId= accountContactMap.get(opp.name+opp.AccountId), isPrimary=true);
            lstOppContactRole.add(oppCon);
            }
        }
        insert lstOppContactRole;
        }     
}
Let me know if u need any help

All Answers

Akshay_DhimanAkshay_Dhiman
Hi Kamran,
This type of fatal error usually comes when you try to use DML command inside for loop. Here the code which you have provided also depict the same fault as you can see at line 14 and at line 17 you are inserting the record. The best way is to use a list of type Opportunity(for Line 14) and the list of type OpportunityContactRole(for line 18) add the record to the respective list and later at line 19 i.e out of the if statement inserts the lists. Below is the complete code: 
trigger OpportunityCreatorOnContact on Contact (before update){
  List<Opportunity>oppList = new List<Opportunity>();
  List<OpportunityContactRole> ocrList = new List<OpportunityContactRole>();
   if(OpportunityCreatorOnContactHandler.isFirstTime){
     OpportunityCreatorOnContactHandler.isFirstTime=false;
List<Contact> ContList = new List<Contact>([SELECT Name,Contact.Account.Id,Contact.Account.Name,Most_recent_Opportunity_Stage__c, Contact.Account.OwnerId,Quarantine__c,mkto71_Lead_Score__c from Contact where id IN :Trigger.newMap.keySet()]);
 for(Contact c : trigger.new){   
  for(Contact c1: ContList){  
   if(c.Most_recent_Opportunity_Stage__c=='Closed Lost' && trigger.oldMap.get(c.Id).Create_New_Opp__c==false && c.Create_New_Opp__c==true  && c.mkto71_Lead_Score__c>=100){   
Opportunity opp = new Opportunity(Name=c1.Account.Name, AccountID=c.AccountId, StageName='Open', CloseDate=Date.Today().addDays(+30),ownerID = c1.Account.OwnerId);
oppList.add(opp);
opportunityCOntactRole oppCOn = new opportunityCOntactRole(OpportunityId=opp.id, contactId= c.Id, isPrimary=true);
 ocrList.add(oppCon);
      }    
    }    
  }     
}
if(oppList.size()>0){
insert oppList;
 }
if(ocrList.size()>0){
insert ocrList;
  }
}
Hope this may help you.
Regards,
Akshay
Akhil Mirthipati 2Akhil Mirthipati 2
hi akshay can u give ur mail id i want to talk with u its very urgent.....!!!! pls
sunny522sunny522
Hi Kamran,
       You are doing DML operations inside for loop.we need to avoid it.
Please use the code below
trigger OpportunityCreatorOnContact on Contact (before update) {
   
    if(OpportunityCreatorOnContactHandler.isFirstTime){
        OpportunityCreatorOnContactHandler.isFirstTime=false;
        Set<Id> conIdSet = new Set<Id>();
        for(Contact c : trigger.new)
        {
            conIdSet.add(c.id);
        }
        List<Contact> ContList = new List<Contact>([SELECT Name,Contact.Account.Id,Contact.Account.Name, Most_recent_Opportunity_Stage__c, Contact.Account.OwnerId,Quarantine__c,mkto71_Lead_Score__c from Contact where id IN :conIdSet]); 
        List<opportunity> lstOppToInsert = new List<opportunity>();
        Map<String,Id> accountContactMap = new Map<String,Id>();
        for(Contact c1: ContList){  

            if(c1.Most_recent_Opportunity_Stage__c=='Closed Lost' && trigger.oldMap.get(c1.Id).Create_New_Opp__c==false && c1.Create_New_Opp__c==true  && c1.mkto71_Lead_Score__c>=100 ) {   
            
            Opportunity opp = new Opportunity(Name=c1.Account.Name, AccountID=c1.AccountId, StageName='Open', CloseDate=Date.Today().addDays(+30),ownerID = c1.Account.OwnerId);
            lstOppToInsert.add(opp);
            accountContactMap.put(c1.Account.Name+c1.AccountId,c1.id);
            }    
        }  
        insert lstOppToInsert;
        List<opportunityCOntactRole> lstOppContactRole = new List<opportunityCOntactRole>();
        for(Opportunity opp:lstOppToInsert) {
            if(accountContactMap.containsKey(opp.name+opp.AccountId)) {
            opportunityCOntactRole oppCOn = new opportunityCOntactRole(OpportunityId=opp.id, contactId= accountContactMap.get(opp.name+opp.AccountId), isPrimary=true);
            lstOppContactRole.add(oppCon);
            }
        }
        insert lstOppContactRole;
        }     
}
Let me know if u need any help
This was selected as the best answer
Mayank.msMayank.ms
Hi Akshay,

I have already tried add the record to the respective list but it's giving the error  "Error:Apex trigger OpportunityCreatorOnContact caused an unexpected exception, contact your administrator: OpportunityCreatorOnContact: execution of BeforeUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [Opportunity]: [Opportunity]: Trigger.OpportunityCreatorOnContact: line 37, column 1"

See the below code

if(ocrList.size()>0){
insert ocrList;               // line no 37
  }

 
Amit Singh 1Amit Singh 1
Hello Kamraan,

You do not need to use SOQL and two for loop for creation of Opportuntiy. You need for loops when your are inserting OpportunityContactRole. Use below code. 
trigger OpportunityCreatorOnContact on Contact (before update) {
   
    if(OpportunityCreatorOnContactHandler.isFirstTime){
        OpportunityCreatorOnContactHandler.isFirstTime=false;
        Map<Id, List<Opportunity>> opportunityMap = new Map<Id, List<Opportunity>>();
        List<opportunityCOntactRole> oppContactRoleList = new List<opportunityCOntactRole>();
        //Map<Id,Contact> contactMap = new Map<Id,Contact>([SELECT Name,Contact.Account.Id,Contact.Account.Name, Most_recent_Opportunity_Stage__c, Contact.Account.OwnerId,Quarantine__c,mkto71_Lead_Score__c from Contact where id IN :Trigger.newMap.keySet()]); 
        for(Contact c : trigger.new){  
           if(c.Most_recent_Opportunity_Stage__c=='Closed Lost' && trigger.oldMap.get(c.Id).Create_New_Opp__c==false && c.Create_New_Opp__c==true  && c.mkto71_Lead_Score__c>=100 ) {   
                        
              Opportunity opp = new Opportunity(Name=c.Account.Name, AccountID=c.AccountId, 
                                              StageName='Open', CloseDate=Date.Today().addDays(+30),
                                              ownerID = c.Account.OwnerId);
                If(!opportunityMap.containsKey(c.Id)){
                    List<Opportunity> oppList = new List<Opportunity>();
                    oppList.add(opp);
                    opportunityMap.put(c.Id, oppList);
                }else{
                    List<Opportunity> oppList1 = opportunityMap.get(c.Id);
                    oppList1.add(opp);
                    opportunityMap.put(c.Id, oppList1);
                }
           }    
        }  
        If(opportunityMap.values().size()>0){
            insert opportunityMap.values();
            For(Id cId : opportunityMap.keySet()){
                For(Opportunity o : opportunityMap.get(cId)){
                    opportunityCOntactRole oppCOn = new opportunityCOntactRole(OpportunityId=o.id, 
                                                                contactId= cId, isPrimary=true);
                    oppContactRoleList.add(oppCon);
                }
            }
            insert oppContactRoleList;
        }                          
    }    
}


Let me know the outcomes.
Thanks,
AMit Singh.
Akshay_DhimanAkshay_Dhiman
Hi Kamran,
It's my pleasure to help you! Please mark my answer as the best answer, if it is really helpful.
Email Address: akshaydhiman@cloudanalogy.com

Regards,
Akshay
Akshay_DhimanAkshay_Dhiman
Hi Kamran,
My apologize that you have faced the error. Please use the below trigger:
  
trigger OpportunityCreatorOnContact on Contact (before update){
Map<Id, List<Opportunity>> opportunityContactIdMap = new Map<Id, List<Opportunity>>();
List<opportunityCOntactRole> opportunityContactRoleList = new List<opportunityCOntactRole>();
if(OpportunityCreatorOnContactHandler.isFirstTime){
  OpportunityCreatorOnContactHandler.isFirstTime=false;
for(Contact c : trigger.new){  
if(c.Most_recent_Opportunity_Stage__c=='Closed Lost' && 
trigger.oldMap.get(c.Id).Create_New_Opp__c==false && c.Create_New_Opp__c==true  && c.mkto71_Lead_Score__c>=100){   
Opportunity opp = new Opportunity(Name=c.Account.Name,AccountID=c.AccountId,StageName='Open', CloseDate=Date.Today().addDays(+30),ownerID = c.Account.OwnerId);
 if(!opportunityMap.containsKey(c.Id)){
 List<Opportunity> oppList = new List<Opportunity>();
   oppList.add(opp);
 opportunityContactIdMap.put(c.Id,oppList);
}else{
    opportunityContactIdMap.get(c.Id).add(opp);
    }
  }    
}  
if(opportunityContactIdMap.values().size()>0){
   insert opportunityContactIdMap.values();
for(Id contId : opportunityContactIdMap.keySet()){
List<Opportunity> oppList = new List<Opportunity>();
oppList=opportunityContactIdMap.get(cId);
if(oppList.size()>0){
for(Opportunity opp :oppList){
 OpportunityContactRole oppCOn = new OpportunityContactRole(OpportunityId=opp.id,contactId= contId);
   opportunityContactRoleList.add(oppCon);
   }
  }
}
if(opportunityContactRoleList.size()>0){
   insert opportunityContactRoleList ;
     }
   }                          
 }    
}
Regards,
Akshay
Akshay_DhimanAkshay_Dhiman
Hi Akhil,
My Email Address is akshaydhiman@cloudanalogy.com

Regards,
Akshay
Mayank.msMayank.ms
Thanks Sunny, Amit, Akshay for your help.
Ingrid Stone 1Ingrid Stone 1
Hi I found this answer as I am habing exatly the same probem

I will try and figure out the code but if anyone can help me with this quicky that will be fantastic

trigger createoppfromcontentcontact on Contact (after insert){
    List<Opportunity> oppsToCreate = new List<Opportunity>();

    ID contactrecordid =[select Id from RecordType where Name = 'Content contacts' and SobjectType = 'Contact' limit 1].ID;
    ID opprecordid = [select Id from RecordType where Name = 'Content opportunity' and SobjectType = 'Opportunity' limit 1].ID;
     
      for(Contact c : [SELECT Id, Name, AccountId,recordtypeid,Content_opp_Solictior__c,Content_Opp_Source__c, Content_Opp_Rating__c,
             Content_Opp_Campaign__c,content_Opp_Real_or_Annon__c,Content_Dropbox__c,Content_Recording_ID__c,
             Content_Review_Type__c,Content_Business_Rel__c,Content_opp_Product_ID__c FROM Contact Where id IN :Trigger.New]) {

   if(c.RecordTypeid == contactrecordid) { 

       Opportunity o = new Opportunity ();
       o.Name = 'Content Review - ' + c.Name;
       o.RecordTypeid = opprecordid;
       o.Content_Contact__c = c.id ;
       o.StageName = 'In Progress';
       o.CloseDate = Date.today() + 365;
       o.AccountId = c.AccountId;
       o.Opportunity_Source__c = c.Content_Opp_Source__c;
       o.Content_Review_Rating__c = c.Content_Opp_Rating__c;
       o.Content_Review_type__c =  c.Content_Review_Type__c; 
  // setting solicitor to picklist field not lookup
       o.Content_Solicitor_2__c = c.Content_opp_Solictior__c;
       o.Content_Base_Campaign_txt__c = c.Content_Opp_Campaign__c;
       o.Content_Real_name_or_Anonymous__c = c.content_Opp_Real_or_Annon__c;
       o.Content_Dropbox__c =  c.Content_Dropbox__c;  
       o.Content_Recording_ID__c =  c.Content_Recording_ID__c; 
       o.Content_Business_Relationship__c =  c.Content_Business_Rel__c;  
       o.Content_Base_Campaign_txt__c = c.Content_Opp_Campaign__c;
       oppsToCreate.add(o);

 list<ITCS_product__c> content_opp = [select ID from ITCS_Product__c where MySQL_Product_ID_Number__c = :c.Content_opp_Product_ID__c];
    If (content_opp.size ( ) > 0) 
    { o.Content_ITCS_product__c = content_opp[0].id;
    } else o.Content_ITCS_product__c = null;

    }
    }
        insert oppsToCreate;
}
Ingrid Stone 1Ingrid Stone 1
my probelm appears to be with the final list ITCS_product_c.   I commented this block out anf it works ok but I need this to add the lookup to the product code to the opportuntiy