1

The following is a part of an AFTER trigger that I have written.

It works fine - on 1 record... but when you get a few more than 1 I am getting the Too Many SOQL Queries error.

  • The first select (lstFA) is on the parent object (Fixed_Asset__c)

  • The second select (lstHIST) is a child object of Fixed_Asset__c and I want to only return 0 or 1 records per Fixed_Asset__c - the last one of a certain type (or none at all for that Asset).

  • Then based on all of that in lstHIST there is a field Assigned_Key__c and depending on the value in that field I have to query either an Account, Contact, Opportunity, User or Fixed_Asset_Holder__c.

The 'for' in this trigger is a ways farther down - but there is no more SOQL after what I show below.

Can anyone assist me in re-working these queries so the trigger works for more than 1 update at a time?

if (trigger.isUpdate){
       lstFA = [select Id, Name, AcctSol__Internal_Conversion__c, AcctSol__Transfer_Date__c, AcctSol__Assigned_Date__c,
         AcctSol__Account__c,AcctSol__Assigned_Address__c,AcctSol__Assigned__c,AcctSol__Assigned_City__c,
         AcctSol__Contact__c,AcctSol__Assigned_Country__c,AcctSol__DepartmentInfo__c,AcctSol__Assigned_Email__c,
         AcctSol__Fixed_Asset_Holder__c,AcctSol__Assigned_Mobile__c,AcctSol__Assigned_Name__c,AcctSol__Opportunity__c,
         AcctSol__Assigned_Other_Phone__c,AcctSol__Assigned_Phone__c,AcctSol__Assigned_State_Province__c,
         AcctSol__Assigned_Postal_Code__c,
         AcctSol__Loan_Account__c,AcctSol__Loan_Address__c,AcctSol__Loan_Start_Date__c,AcctSol__Loan_Type__c,
         AcctSol__Loan_City__c,AcctSol__Loan_Contact__c,AcctSol__Loan_DepartmentInfo__c,AcctSol__Loan_Country__c,
         AcctSol__Loan_Email__c,AcctSol__Loan_Estimated_Return_Date__c,AcctSol__Loan_Mobile__c,AcctSol__Loan_Name__c,
         AcctSol__Loan_Opportunity__c,AcctSol__Loan_Other_Phone__c,AcctSol__Loan_Phone__c,AcctSol__Loan_State_Province__c,
         AcctSol__Loan_User__c,AcctSol__Loan_Zip_Postal_Code__c,AcctSol__QR_Code__c,AcctSol__QR_Code_Asset_ID__c,
         AcctSol__Remaining_Orginal_Cost__c,AcctSol__Original_Cost__c,AcctSol__Insurance_Policy_LU__c,
         from AcctSol__Fixed_Asset__c where Id = :Trigger.New];

       AcctSol__Fixed_Asset__c OLDFA = Trigger.OldMap.get(lstFA[0].Id);

       if(lstFA[0].AcctSol__Internal_Conversion__c == '18U') 
       {
         lstHIST = 
           [SELECT Id,AcctSol__Address__c,AcctSol__Assigned_Key__c,AcctSol__Assigned_Type__c,AcctSol__Current_Date__c,
           AcctSol__Description__c,AcctSol__Email__c,AcctSol__Fixed_Asset__c,AcctSol__Loan_Date__c,AcctSol__Name1__c,
           AcctSol__New_User__c,AcctSol__Phone_Number__c,AcctSol__User__c,AcctSol__Return_Date__c,AcctSol__Starting_Date__c
           FROM AcctSol__History_Tracking_Fixed_Asset__c 
           WHERE AcctSol__Fixed_Asset__c=:Trigger.New and AcctSol__Loan_Date__c <> null 
           ORDER BY Name DESC LIMIT 1]; 

           if(lstHIST.size() > 0)
           {
             if(lstHIST[0].AcctSol__Assigned_Key__c <> null)
             {
                 if (lstHIST[0].AcctSol__Assigned_Type__c == 'Account')
                 {
                   lstAcc = [select id,Name,Phone,BillingCity,BillingCountry ,BillingPostalCode,BillingState,
                            BillingStreet,Department__c from Account where Account.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
                 }
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Contact')     
                 {
                   lstCon = [select id,Name,Phone,Email,OtherPhone,MobilePhone,Department,MailingCity,MailingCountry,
                            MailingPostalCode,MailingState,MailingStreet 
                            from Contact where Contact.Name = :lstHIST[0].AcctSol__Assigned_Key__c];
                 }
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Asset Holder')
                 {
                   lstFAH = [select id,Name,Phone__c,Email__c,Other_Phone__c,Mobile__c,Department__c,City__c,Country__c,
                           Postal_Code__c,State_Province__c,Address__c 
                           from AcctSol__Fixed_Asset_Holder__c where AcctSol__Fixed_Asset_Holder__c.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
                 }
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Opportunity')
                 {
                   lstOpp = [select id,Name from Opportunity 
                            where Opportunity.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
                 }        
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'User')                                        
                     lstUsr = [select id,Name,MobilePhone,Email,Phone,Street,State,PostalCode,Country,City,
                               Department from User where User.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
            }
           }
  • 2
    Have a look at some of the documentation on bulkifying apex code - Best Practice: Bulkify Your Code. – Daniel Ballinger Oct 4 '12 at 1:30
  • Yes - thanks - I have read it several times in relation to this problem. Best practice #2 I thought would help me with effectively doing a 'sub-select' to get 1 query for all the objects except that my objects are 'optional' relationships so I can't make one big query that gets me the Asset, History Record and then either Account/Contact/Opp/User/etc. – AngiB Oct 4 '12 at 14:20
1
    List<Id> assignedKeys = new List<Id>{};
    Map<Id, Account> assignedObjects = new Map<Id, sObject>{};
    if (trigger.isUpdate){
    for(Fixed_Asset__c lstFA : trigger.new){
    //collect all the assigned keys in a list
    if(Trigger.OldMap.get(lstFA[0].Id).AcctSol__Internal_Conversion__c == '18U')
    assignedKeys.add(lstFA.AcctSol__Assigned_Type__c);


//select all the accounts which have name in the assigned keys collection above
    for(Account acc : [select id,Name,Phone,BillingCity,BillingCountry ,BillingPostalCode,BillingState,BillingStreet,Department__c from Account where Account.Name IN :assignedKeys])
assignedObjects.put(acc.Name, acc);

 for (Contact con : [select id, Name, Phone, Email, OtherPhone, MobilePhone, Department,MailingCity,MailingCountry,MailingPostalCode,MailingState,MailingStreet from Contact where Contact.Name IN :lstHIST[0].AcctSol__Assigned_Key__c])
assignedObjects.put(con.Name, con);

... ditto for Opportunities and Asset Holders

}

//Now assignedObjects contains a Map of the key and Object (Acc, Con, ...)
To access Account, just type cast to account
lstAcc  = (Account)assignedObjects.get(Key);
  • I really hate to sound like a complete ghit but I am still confused. I am not seeing how this is reducing the number of SOQL queries in my trigger ??? – AngiB Oct 4 '12 at 14:07
  • Do you want to mail me your entire trigger on techtrekker@outlook.com and I will sort it out ! ;) – techtrekker Oct 4 '12 at 17:13
  • That might be the best offer I have received EVER :-) E-mail will be on it's way shortly - thank you VERY much! – AngiB Oct 4 '12 at 18:45
  • I am going to mark this question as 'Answered'. It really wasn't but then again somehow I appear to have gotten my code to actually work (although I am still waiting to apply it to an org with a seriously large set of assets as the real test). Anyways once again thanks for the help!! :-) – AngiB Oct 19 '12 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.