HELP : System.LimitException: Too many SOQL queries: 101 [duplicate]

Question

This is my first trigger, on my custom object Affaire:

Trigger MissionrollupAffairesDossier on Affaire__c (after delete, after insert, after update, after undelete) {

//Cumul de montants d'affaire dossiers gagnée 
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Dossiers_vendus__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Dossiers_vendus__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Dossier\' and Etape__c = \'Gagnée\'');
}

//Cumul de montants d'affaire dossiers en cours 
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Dossiers_en_cours__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Dossier_en_cours__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Dossier\' and Etape__c <> \'Gagnée\'');
}


//Cumul des affaires de type dossiers
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Dossier__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Dossier\'');
}

// Idem qu'au dessus si effacement Dossier vendus
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Dossiers_vendus__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Dossiers_vendus__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Dossier\' and Etape__c = \'Gagnée\'');
 }

 // Idem qu'au dessus si effacement Dossier en cours
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Dossiers_en_cours__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Dossier_en_cours__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Dossier\' and Etape__c <> \'Gagnée\'');
}

 // Idem qu'au dessus si effacement Nombre d'affaire dossier 
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Dossier__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Dossier\'');
}
}

I've duplicated it for having other field update, my second trigger on the same object Affaire:

Trigger MissionrollupAffairesFormation on Affaire__c (after delete, after insert, after update, after undelete) {

//Cumul de montants d'affaire Formation gagnée 
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formations_vendues__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Formations_vendues__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Formation\' and Etape__c = \'Gagnée\'');
}

//Cumul de montants d'affaire Formation en cours 
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formation_en_cours__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Formations_en_cours__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Formation\' and Etape__c <> \'Gagnée\'');
}


//Cumul des affaires de type Formation total
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Formation__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Formation\'');
}

// Idem qu'au dessus si effacement Formation vendues
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formations_vendues__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Formations_vendues__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Formation\' and Etape__c = \'Gagnée\'');
 }

 // Idem qu'au dessus si effacement Formation en cours
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formation_en_cours__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Formation__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Formation\' and Etape__c <> \'Gagnée\'');
 }

// Idem qu'au dessus si effacement nbre de Formation 
 if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Formation__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Formation\'');
}
}

My test class gives me this error:

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

I think there is a way to have only one trigger. I'm not a developer, but I try. The trigger work very well, but the test doesn't pass

The test code:

Trigger MissionrollupAffairesFormation on Affaire__c (after delete, after insert, after update, after undelete) {

//Cumul de montants d'affaire Formation gagnée 
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formations_vendues__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Formations_vendues__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Formation\' and Etape__c = \'Gagnée\'');
}

//Cumul de montants d'affaire Formation en cours 
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formation_en_cours__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Formations_en_cours__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Formation\' and Etape__c <> \'Gagnée\'');
}


//Cumul des affaires de type Formation total
if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Formation__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Formation\'');
}

// Idem qu'au dessus si effacement Formation vendues
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formations_vendues__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_de_Formations_vendues__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Formation\' and Etape__c = \'Gagnée\'');
 }

 // Idem qu'au dessus si effacement Formation en cours
if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('SUM', 'Montant__c',
 'Formation_en_cours__c'),
  new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Formation__c')
  };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c','and Type_d_affaire__c = \'Formation\' and Etape__c <> \'Gagnée\'');
 }

// Idem qu'au dessus si effacement nbre de Formation 
 if(trigger.isDelete){
list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions =
 new list<RollUpSummaryUtility.fieldDefinition> {
 new RollUpSummaryUtility.fieldDefinition('COUNT', 'Montant__c',
 'Nbre_Affaires_Formation__c')
 };
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old,
 'Affaire__c', 'Mission_affaire__c','Mission__c', 'and Type_d_affaire__c = \'Formation\'');
}
}

And my APEXCLASS

public class RollUpSummaryUtility {

//the following class will be used to house the field names
//and desired operations
public class fieldDefinition {
    public String operation {get;set;}
    public String childField {get;set;}
    public String parentField {get;set;}

    public fieldDefinition (String o, String c, String p) {
        operation = o;
        childField = c;
        parentField = p;
    }
}

public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
list<sObject> records, String childObject, String childParentLookupField,
String parentObject, String queryFilter) {

    //Limit the size of list by using Sets which do not contain duplicate
    //elements prevents hitting governor limits
    set<Id> parentIds = new set<Id>();

    for(sObject s : records) {
        parentIds.add((Id)s.get(childParentLookupField));
    }

    //populate query text strings to be used in child aggregrator and
    //parent value assignment
    String fieldsToAggregate = '';
    String parentFields = '';

    for(fieldDefinition d : fieldDefinitions) {
        fieldsToAggregate += d.operation + '(' + d.childField + ') ' +
        ', ';
        parentFields += d.parentField + ', ';
    }

    //Using dynamic SOQL with aggergate results to populate parentValueMap
    String aggregateQuery = 'Select ' + fieldsToAggregate +
    childParentLookupField + ' from ' + childObject + ' where  ' +
    childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
    ' group by ' + childParentLookupField;

    //Map will contain one parent record Id per one aggregate object
    map<Id, AggregateResult> parentValueMap =
    new map <Id, AggregateResult>();

    for(AggregateResult q : Database.query(aggregateQuery)){
        parentValueMap.put((Id)q.get(childParentLookupField), q);
    }

    //list of parent object records to update
    list<sObject> parentsToUpdate = new list<sObject>();

    String parentQuery = 'select ' + parentFields + ' Id ' +
     ' from ' + parentObject + ' where Id IN :parentIds';

    //for each affected parent object, retrieve aggregate results and
    //for each field definition add aggregate value to parent field
    for(sObject s : Database.query(parentQuery)) {

        Integer row = 0; //row counter reset for every parent record
        for(fieldDefinition d : fieldDefinitions) {
            String field = 'expr' + row.format();
            AggregateResult r = parentValueMap.get(s.Id);
            //r will be null if no records exist
            //(e.g. last record deleted)
            if(r != null) {
                Decimal value = ((Decimal)r.get(field) == null ) ? 0 :
                    (Decimal)r.get(field);
                s.put(d.parentField, value);
            } else {
                s.put(d.parentField, 0);
            }
            row += 1; //plus 1 for every field definition after first
        }
        parentsToUpdate.add(s);
    }

    //if parent records exist, perform update of all parent records
    //with a single DML statement
    if(parentsToUpdate.Size() > 0) {
        update parentsToUpdate;
    }

}

}

Thank you very much if you can help.

My Test class for the trigger Dossier

     @isTest
 private class TestMissionrollupAffairesdossier {
 static testMethod void myUnitTest() {
 //Profile pf ='00e20000001GlJM';//
 //[Select Id from Profile where Name = 'System Administrator']//

 User u = new User();
 u.FirstName = 'Test';
 u.LastName = 'User';
 u.Email = '[email protected]';
 u.CompanyName = 'test.com';
 u.Title = 'Test User';
 u.Username = '[email protected]';
 u.Alias = 'testuser';
 u.CommunityNickname = 'Test User';
 u.TimeZoneSidKey = 'America/Mexico_City';
 u.LocaleSidKey = 'en_US';
 u.EmailEncodingKey = 'ISO-8859-1';
 u.ProfileId = '00e20000001GlJM';
 u.LanguageLocaleKey = 'en_US';
 insert u;

 // Création d'un compte pro
  system.runAs(u){
Account acc = new Account();
acc.RecordTypeId='012200000006EEH';
acc.Name='PRO';
acc.Activit_principale__c='Biomasse';
acc.SIREN__c='123456789';
acc.Phone ='0122112211';
 insert acc;

 // Création d'un compte fédérateur
   system.runAs(u){
Account accf = new Account();
accf.RecordTypeId='012200000006EEC';
accf.Name='Federateur';
accf.Activit_principale__c='Biomasse';
accf.SIREN__c='111222333';
accf.Phone ='0122112222';
 insert accf;

  // Création d'une Operation
 system.runAs(u){
 Operation__c opp = new Operation__c();
 opp.Name='TEST';
 opp.Federateur__c=accf.Id;
 insert opp;

   // Création d'une Mission
 system.runAs(u){
Mission__c mi = new Mission__c();
mi.Name = 'NE RIEN INSCRIRE';
mi.Operation__c=opp.Id;
mi.Statut__c='Non démarré';
mi.Statut_de_diagnostic__c='Non contacté';
mi.Pro__c=acc.Id;
insert mi;

  // Création d'une affaire
system.runAs(u){
 Affaire__c aff = new Affaire__c();
 aff.Name = 'Test Opportunity';
 aff.Etape__c ='Gagnée';
 aff.Compte__c=acc.Id;
 aff.Mission_affaire__c=mi.Id;
insert aff;

  // Création d'un produit de l'affaire
 system.runAs(u){
Produit_affaire__c pdt = new Produit_affaire__c();
pdt.Famille__c = 'Dossier';
pdt.Type_de_produit__c = 'Dossier Initial + mention EE (Dossier)';
pdt.Quantite__c=1;
pdt.Montant__c = 400;
pdt.Affaire__c=aff.Id;
insert pdt;

//Test Affaire on insert
 Affaire__c aff1 = new Affaire__c();
aff1.Name = 'Test Opportunity1';
aff1.Etape__c ='Gagnée';
aff1.Compte__c=acc.Id;
aff1.Mission_affaire__c = mi.Id;
 insert aff1;

//Test Produit de l'Affaire 1 on insert
Produit_affaire__c pdt1 = new Produit_affaire__c();
pdt1.Famille__c = 'Dossier';
pdt1.Type_de_produit__c = 'Dossier Initial + mention EE (Dossier)';
pdt1.Quantite__c=1;
pdt1.Montant__c = 300;
pdt1.Affaire__c=aff1.Id;
insert pdt1;

// Test Produit de l'affaire 1 on update
Produit_affaire__c pdt1u = [select Montant__c from Produit_affaire__c where Id = :pdt1.Id];
pdt1u.Montant__c = 200;
update pdt1u;

//Mission__c ou1 = [select Montant_affaires_mission__c from Mission__c where Id = :mi.Id];
//system.assertEquals(ou1.Montant_affaires_mission__c,aff1.Montant__c);

//Test Affaire on second insert
 Affaire__c aff2 = new Affaire__c();
aff2.Name = 'Test Opportunity2';
aff2.Etape__c ='Gagnée';
aff2.Compte__c=acc.Id;
aff2.Mission_affaire__c = mi.Id;
 insert aff2;

//Test Produit de l'Affaire on insert
Produit_affaire__c pdt2 = new Produit_affaire__c();
pdt2.Famille__c = 'Dossier';
pdt2.Type_de_produit__c = 'Dossier Initial + mention EE (Dossier)';
pdt2.Quantite__c=1;
pdt2.Montant__c = 800;
pdt2.Affaire__c=aff2.Id;
insert pdt2;

//Test Affaire dossier
AggregateResult ag1 = [select sum(Montant__c) from Affaire__c where Mission_affaire__c = :mi.Id];
Mission__c ou3 = [select Dossiers_vendus__c from Mission__c where Id = :mi.Id];
system.assertEquals(ou3.Dossiers_vendus__c,ag1.get('expr0'));

// AggregateResult ag3 = [select sum(Montant__c) from Affaire__c where Mission_affaire__c = :mi.Id];
//Mission__c ou5 = [select Formations_vendues__c from Mission__c where Id = :mi.Id];
//system.assertEquals(ou5.Formations_vendues__c,ag3.get('expr0'));

// AggregateResult ag5 = [select sum(Montant__c) from Affaire__c where Mission_affaire__c = :mi.Id];
//Mission__c ou7 = [select Formation_en_cours__c from Mission__c where Id = :mi.Id];
//system.assertEquals(ou7.Formation_en_cours__c,ag5.get('expr0'));

//Test Affaire on delete
 delete aff2;
AggregateResult ag2 = [select sum(Montant__c) from Affaire__c where Mission_affaire__c = :mi.Id];
Mission__c ou4 = [select Dossiers_vendus__c from Mission__c where Id = :mi.Id];
 system.assertEquals(ou4.Dossiers_vendus__c,ag2.get('expr0'));

//AggregateResult ag4 = [select sum(Montant__c) from Affaire__c where Mission_affaire__c = :mi.Id];
//Mission__c ou6 = [select Formations_vendues__c from Mission__c where Id = :mi.Id];
// system.assertEquals(ou6.Formations_vendues__c,ag4.get('expr0'));

//AggregateResult ag6 = [select sum(Montant__c) from Affaire__c where Mission_affaire__c = :mi.Id];
//Mission__c ou8 = [select Formation_en_cours__c from Mission__c where Id = :mi.Id];
 //system.assertEquals(ou8.Formation_en_cours__c,ag6.get('expr0'));


}
}
}
}
}
}
}
}

BIG THANK FOR YOU HELP

Also your test class - you seem to have pasted in another copy of the trigger. — metadaddy, Jun 16, 2014 at 21:51
Note this problem is amenable to running with the debug log on to see if either a) you have soql queries inside of for loops (bad practice) or b) you have recursive triggers (your trigger updates its object record and then gets invoked again thus reupdating itself, ad infinitum — cropredy, Jun 17, 2014 at 16:51

Matt Lacey · Accepted Answer · 2014-08-12 00:13:21Z

2

I wrote the following bash script you can run against a downloaded debug log file to see how many times each soql statement is run. It's useful for situations like this.

grep SOQL_EXECUTE_BEGIN debugLog.txt | awk 'BEGIN { FS = "|" }; { print $5}'| sort | uniq -c -d

edited Aug 12, 2014 at 0:13

Matt Lacey♦

25.4k7 gold badges64 silver badges148 bronze badges

answered Jun 22, 2014 at 5:44

donjay

2761 silver badge6 bronze badges

Add a comment |

Matt Lacey · Accepted Answer · 2014-08-12 00:13:07Z

In order to avoid the situation of recursive call, make sure your trigger is getting executed only one time. To do so, you can create a class with a static boolean variable with default value true.

In the trigger, before executing your code keep a check that the variable is true or not.

Once you check make the variable false.

Class code :

public Class checkRecursive{
    private static boolean run = true;
    public static boolean runOnce(){
    if(run){
     run=false;
     return true;
    }else{
        return run;
    }
    }
}

Trigger code :

trigger updateTrigger on anyObject(after update) {

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

Stack Exchange Network

HELP : System.LimitException: Too many SOQL queries: 101 [duplicate]

2 Answers 2

Not the answer you're looking for? Browse other questions tagged
apex
trigger
governorlimits
.

Linked

Hot Network Questions

HELP : System.LimitException: Too many SOQL queries: 101 [duplicate]

2 Answers 2

Not the answer you're looking for? Browse other questions tagged apextriggergovernorlimits.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
apex
trigger
governorlimits
.