function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SteveMouSteveMou 

SOQL Queries inside FOR Loops

Can anyone provide me with advice on how to change my Apex Trigger below to not have the SOQL query within my for loop? I am getting the "Too many SOQL queries" error.

 

trigger AccountTeamDuplicateTrigger on Account_Team_Assignment__c (before insert,before update) {
  for (Account_Team_Assignment__c c : Trigger.new){

   Account_Team_Assignment__c[] Account_Team_Assignments= [select Account_Name__c from Account_Team_Assignment__c Where Active__c = TRUE and Business_Segments__c = :c.Business_Segments__c and Account_Name__c = :c.Account_Name__c ];
   
   if (Account_Team_Assignments.size() > 1) {
     c.Representative__c.addError('BDRep cannot be created - An active BD already exists in the Account Team for this Business Segment');
  }     
 }
}

iKnowSFDCiKnowSFDC

Hi, 

 

If you create a set of ids and add your trigger records to it and then build your list from the set of ids it should take care of it.  You could also add the accounts to a list instead of a set and then build your select statement from there.  

 

Something like this: 

Set<Id> acctIds = new Set<id>();  
for (Account_Team_Assignment__c c : Trigger.new){
  	acctsIds.add(c.id);
  }
Account_Team_Assignment__c[] Account_Team_Assignments= 

[select Account_Name__c 
  from Account_Team_Assignment__c 
  Where Active__c = TRUE 
  and Business_Segments__c = :acctsIds.Business_Segments__c 
  and Account_Name__c.id IN :acctsIds ];

 
snowMonkeysnowMonkey

I also learned that recently that this is a bad apex programming practise - to have sqls inside for loop. Seems it gets execs for every row in the base table .

 

the key here is to create list and assemble the list of ids to that list and go thru that list in your trigger loop and do whatever.

 

I am still learning apex and dont know when i will run into these governer limits where we have start looking into other functionalities of the system to optimize the code..:)

// Create an empty list of IDs
    List<Id> invoiceIds = new List<Id>();   

    for (Invoice_Statement__c invoice: Trigger.new) {
        if (invoice.status__c == 'Closed' && oldMap.get(invoice.Id).status__c !='Closed'){
            invoiceIds.add(invoice.Id);
        }
    }

    if (invoiceIds.size() > 0) {
        add your code here...
    }