+ Start a Discussion
Denise CrosbyDenise Crosby 

need help with trigger bulkification

Hello. I wrote an Apex trigger that works fine to update a custom field on the opportunity when an opportunitylineitem is updated. The problem is when I try to import 200 records I get the error: UpdateOpptyProductText: System.LimitException: Too many SOQL queries: 101. Many thanks for helping.
 
trigger UpdateOpptyProductText on OpportunityLineItem (after insert, after update) {
    for (OpportunityLineItem oli : Trigger.New) {
      
        Opportunity o = [select id from Opportunity where id =: oli.OpportunityId];
        o.Opportunity_Products__c = '';
        OpportunityLineItem[] lis = [Select id, name, product2id from opportunitylineitem where opportunityid=:o.Id];
        
        integer count = 0;
        for (OpportunityLineItem li : lis) {
            if (count > 0) {
                o.Opportunity_Products__c += '; ';
            }
            Product2 p = [select id,name from Product2 where id =: li.Product2Id];
            o.Opportunity_Products__c += p.name;
            count ++;
        } 
        update o;
    }
}

 
Best Answer chosen by Denise Crosby
RamakantRamakant
Hi Denise,

Please use below code as a refrence. This code is working for me for to get Contact Count on Account and Printing contact names on Account. Please change your Object names and fields needed.
Trigger ContactRecordCount on Contact(After Insert, After Update, After Delete, After UnDelete){
    
    List<ID> accountIds = New List<ID>();
	List<ID> ContactIds = New List<ID>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUnDelete){
        For(Contact con: Trigger.New){
            if(con.AccountId  != null){
                accountIds.add(con.AccountId);
				ContactIds.add(con.Id);
            }  
        }
    }
    If(Trigger.IsDelete){
        For(Contact con: Trigger.Old){
            accountIds.add(con.AccountId);
			ContactIds.add(con.Id);
        }
    }
     
    List<Account> AccountListToUPdate = New List<Account>();
    String nameString = '';
    For(Account act: [Select Id, Contact_Count__c, (Select ID, FirstName, LastName FROM Contacts) FROM Account WHERE ID = :accountIds]){
		for(Contact con:act.Contacts){
			//act.Con_names__c  = con.FirstName +' '+ con.LastName +',';
			nameString = nameString + con.FirstName +' '+ con.LastName +',';
		}
		act.Con_names__c = nameString;
		AccountListToUPdate.add(act);
    }
     
     
    try{
        Update AccountListToUPdate;
    }
     Catch(Exception E){
        System.Debug('Error Message: ' + e.getMessage());
    }
}

Please let me know if helped by marking best answer.

Regards,
Ramakant

All Answers

Raj VakatiRaj Vakati
Pseudo code is here 
 
trigger UpdateOpptyProductText on OpportunityLineItem (after insert, after update) {

Map<id,OpportunityLineItem> oliMap =Trigger.newMap ;
Map<Id,Opportunity> oppIdMap = new Map<Id,Opportunity>();
Map<Id,List<Opportunity>> oppListIdMap = new Map<Id,List<Opportunity>>();
Map<Id,List<String>> prodListIdMapString = new Map<Id,List<String>>();


for(OpportunityLineItem oll : oliMap.keySet){
      oppIdMap.put(oli.OpportunityId , oli.Opportunity);
	  if(oppListIdMap.contains(oll.id)){
	  oppListIdMap.get(oli.id).add(oli.Opportunity);
	  }else{
	  oppListIdMap.put(oli.id , oli.Opportunity);
	  }
	  if(prodListIdMapString.contains(oll.OpportunityId)){
	  prodListIdMapString.get(oli.OpportunityId).add(oli.Product2.Name);
	  }else{
	  prodListIdMapString.put(oli.OpportunityId , oli.Product2.Name);
	  }
	  
	  
}

for(Opportunity opp : oppIdMap.values){
List<String> str = prodListIdMapString.get(opp.id) ; 
String s = '';
for(String stemo :str){
s = s+stemo;
}

                opp.Opportunity_Products__c = s ; 


}

update oppIdMap.values ;
}

 
RamakantRamakant
Hi Denise,

Please use below code as a refrence. This code is working for me for to get Contact Count on Account and Printing contact names on Account. Please change your Object names and fields needed.
Trigger ContactRecordCount on Contact(After Insert, After Update, After Delete, After UnDelete){
    
    List<ID> accountIds = New List<ID>();
	List<ID> ContactIds = New List<ID>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUnDelete){
        For(Contact con: Trigger.New){
            if(con.AccountId  != null){
                accountIds.add(con.AccountId);
				ContactIds.add(con.Id);
            }  
        }
    }
    If(Trigger.IsDelete){
        For(Contact con: Trigger.Old){
            accountIds.add(con.AccountId);
			ContactIds.add(con.Id);
        }
    }
     
    List<Account> AccountListToUPdate = New List<Account>();
    String nameString = '';
    For(Account act: [Select Id, Contact_Count__c, (Select ID, FirstName, LastName FROM Contacts) FROM Account WHERE ID = :accountIds]){
		for(Contact con:act.Contacts){
			//act.Con_names__c  = con.FirstName +' '+ con.LastName +',';
			nameString = nameString + con.FirstName +' '+ con.LastName +',';
		}
		act.Con_names__c = nameString;
		AccountListToUPdate.add(act);
    }
     
     
    try{
        Update AccountListToUPdate;
    }
     Catch(Exception E){
        System.Debug('Error Message: ' + e.getMessage());
    }
}

Please let me know if helped by marking best answer.

Regards,
Ramakant
This was selected as the best answer
Denise CrosbyDenise Crosby
Hi Raj,
Can you point me to any links explaining this syntax below. I'm having some trouble understanding.

Map<Id,List<Opportunity>> oppListIdMap = new Map<Id,List<Opportunity>>();
Map<Id,List<String>> prodListIdMapString = new Map<Id,List<String>>();

I started converting your pseudo code and got stuck on a few lines. Thanks for your help
 
trigger UpdateOpptyProductText on OpportunityLineItem (after insert, after update) {
    
    Map<id,OpportunityLineItem> oliMap =Trigger.newMap ;
    Map<Id,Opportunity> oppIdMap = new Map<Id,Opportunity>();
    
    Map<Id,List<Opportunity>> oppListIdMap = new Map<Id,List<Opportunity>>();
    Map<Id,List<String>> prodListIdMapString = new Map<Id,List<String>>();
    
    for(OpportunityLineItem oli : oliMap.keySet())
    {
        oppIdMap.put(oli.OpportunityId , oli.Opportunity);
        if(oppListIdMap.containsKey(oli.id))
        {
            oppListIdMap.get(oli.id).add(oli.Opportunity);
        }
        else
        {
            oppListIdMap.put(oli.OpportunityId , oli.Opportunity);
        }
        if(prodListIdMapString.contains(oli.OpportunityId))
        {
            prodListIdMapString.get(oli.OpportunityId).add(oli.Product2.Name);
        }
        else
        {
            prodListIdMapString.put(oli.OpportunityId , oli.Product2.Name);
        }
    }
    
    for(Opportunity opp : oppIdMap.keyset()){
        List<String> str = prodListIdMapString.get(opp.id) ; 
        String s = '';
        for(String stemo :str){
            s = s+stemo;
        }
        
        opp.Opportunity_Products__c = s ; 
        
        
    }
    
    update oppIdMap.keyset() ;
}

 
Denise CrosbyDenise Crosby
Thank you Ramakant very much! I modified your code as follows and it works fine. Thanks so much for sharing!!
 
trigger UpdateOpptyProductText on OpportunityLineItem (after insert, after update, after delete, after undelete) {
    
    List<ID> opptyIds = New List<ID>();
	List<ID> opptyproductIds = New List<ID>();
    
    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUnDelete){
        For(OpportunityLineItem oli: Trigger.New){
            if(oli.OpportunityId  != null){
                opptyIds.add(oli.OpportunityId);
				opptyproductIds.add(oli.Id);
            }  
        }
    }
    If(Trigger.IsDelete){
        For(OpportunityLineItem oli: Trigger.Old){
            opptyIds.add(oli.OpportunityId);
			opptyproductIds.add(oli.Id);
        }
    }
     
    List<Opportunity> OpportunityListToUPdate = New List<Opportunity>();
    For(Opportunity oo: [Select Id, (Select ID, name,product2.name FROM OpportunityLineItems) FROM Opportunity WHERE ID = :opptyIds])
    {
		String nameString = '';
        integer count = 0;
        for(OpportunityLineItem oli:oo.OpportunityLineItems){
			count ++;
            if (count>1) {
                nameString = nameString + '; ';
            } 
            nameString = nameString + oli.product2.name;
            
		}
		oo.Opportunity_Products__c = nameString;
		OpportunityListToUPdate.add(oo);
    }
     
    try{
        Update OpportunityListToUPdate;
    }
     Catch(Exception E){
        System.Debug('Error Message: ' + e.getMessage());
    }
}