+ Start a Discussion
Marco PouleynMarco Pouleyn 

How to optimise Trigger code to avoid exceeding governor limits

Hi,

In my situation I have a Custom Object (Object__c) which can be created in 2 types: TypeA or TypeB, where TypeA is always created first and on a later date TypeB will be created with the same Title and Episode. I created a Trigger that when TypeB is created the Trigger will find a matching TypeA card based on the Title and episode, and fill the new TypeB card with Date1 and Date2 from the matching TypeA card, and it will link TypeA to TypeB with a lookupfield ObjectMatch__c

--------------------------------------------------------
trigger ObjectFindMatch on Object__c (before update) {

  for (Object__c newObj : Trigger.new) {
  for (Object__c extObj : [SELECT Id, Date1__c, Date2__c FROM Object__c 
       WHERE Type__c = 'TypeA' 
       AND Titel_c = :newObj.Titel__c 
       AND Episode_c = :newObj.Episode__c]) { 
  
  IF (newObj.Type__c = 'TypeB') {
      newObj.ObjectMatch__c = extObj.Id;
      newObj.Date1__c = extObj.Date1__c;
      newObj.Date2__c = extObj.Date2__c;
   }

  }
 }
}
--------------------------------------------------------

The Trigger works fine but the cards can also be created in a batch (for example) episodes 1 to 10, then I get a: LIMIT_EXCEEDED: System.LimitException: Too many SOQL queries: 101
I am reading some best practices like:
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_bestpract.htm
But I can't figure out how to optimise my code :(

Please help ?

Kind regards,
Marco
Best Answer chosen by Marco Pouleyn
SATHISH REDDY.SATHISH REDDY.
This should be it.
trigger ObjectFindMatch on Object__c (before update) {
    List<Object__c> extObjList = [SELECT Id, Date1__c, Date2__c,Titel_c,Episode_c FROM Object__c WHERE Type__c = 'TypeA'];
    for (Object__c newObj : Trigger.new) {
        if(newObj.Type__c == 'TypeB'){
            for (Object__c extObj : extObjList){
                if (extObj.Titel_c == newObj.Titel__c && extObj.Episode_c == newObj.Episode_c) {
                    newObj.ObjectMatch__c = extObj.Id;
                    newObj.Date1__c = extObj.Date1__c;
                    newObj.Date2__c = extObj.Date2__c;
                }
            }
        }
    }
}

Please mark it as the best answer if this helps. Thanks!

All Answers

SATHISH REDDY.SATHISH REDDY.
Hey Marco,

You should not write a SOQL inside a for loop. Can you let me know what are these field types Titel_c, Episode_c........... Are Type A and Type B are 2 different objects? So that i can help you better. Thanks!
Marco P.Marco P.
Hi Sathish,

Thank you for reaching out.
There is only one Object (Object__c) where
-Type__c is a Picklistfield to difine the type of record: TypeA or TypeB
-Date1__c and Date2__c are Datefields
-ObjectMatch__c is a Lookupfield to Object__c
-Title__c is a Textfield
-Episode__c is a numberfield

Regards,
Marco
SATHISH REDDY.SATHISH REDDY.
This should be it.
trigger ObjectFindMatch on Object__c (before update) {
    List<Object__c> extObjList = [SELECT Id, Date1__c, Date2__c,Titel_c,Episode_c FROM Object__c WHERE Type__c = 'TypeA'];
    for (Object__c newObj : Trigger.new) {
        if(newObj.Type__c == 'TypeB'){
            for (Object__c extObj : extObjList){
                if (extObj.Titel_c == newObj.Titel__c && extObj.Episode_c == newObj.Episode_c) {
                    newObj.ObjectMatch__c = extObj.Id;
                    newObj.Date1__c = extObj.Date1__c;
                    newObj.Date2__c = extObj.Date2__c;
                }
            }
        }
    }
}

Please mark it as the best answer if this helps. Thanks!
This was selected as the best answer
SATHISH REDDY.SATHISH REDDY.
@Marco - Please mark it as the best answer if this helps. Thanks!
Marco P.Marco P.
Very late reaction. Thnx Sathish. This helped very much.
SATHISH REDDY.SATHISH REDDY.
Glad it did. Please mark it as the best answer so that it can help others in the community. Cheers!