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]; } }
Improve this question
2
  • 2
    Have a look at some of the documentation on bulkifying apex code - Best Practice: Bulkify Your Code. Commented Oct 4, 2012 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. Commented Oct 4, 2012 at 14:20

1 Answer 1

Reset to default
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);
Improve this answer
4
  • 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 ??? Commented Oct 4, 2012 at 14:07
  • Do you want to mail me your entire trigger on [email protected] and I will sort it out ! ;) Commented Oct 4, 2012 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! Commented Oct 4, 2012 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!! :-) Commented Oct 19, 2012 at 18:20

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.