You need to sign in to do that
Don't have an account?
SOQL Limits when Importing using DataLoader
I have a trigger which creates a new opportunity when an asset has a status of Paid and has an expiry date.
This code works fine when records are input using the web interface but when I tried to import 5,000 records using the DataLoader only 23 records imported and the rest were rejected because of SOQL limits on this trigger.
I need to know is my code very innefficient?
I thaught I had taken care to ensure that there was as few inserts and updates as possible.
Strangely when I re-imported the records they imported without any problems except MY TRIGGER SEEMS TO HAVE BEEN IGNORED SALESFORCE DID NOT RUN IT. IS THAT POSSIBLE?
trigger CreateOpportunities on Asset (after update, after insert) {
Boolean NewOpp = false;
List <Opportunity> OppToUpdate = new List <Opportunity> {};
List <Opportunity> OppToInsert = new List <Opportunity> {};
for(Asset aset: Trigger.new){
if (aset.Status == 'Paid'){
Opportunity opp = null;
try {
opp = [select Name,ACCOUNTID,CurrencyIsoCode,CLOSEDATE,Asset__c from Opportunity where asset__c =: aset.ID limit 1];
} catch (Exception e){}
if (opp == null) {
opp = new Opportunity();
NewOpp = true;
}
opp.ACCOUNTID = aset.AccountID;
opp.Name = 'Automated Opportunity for ' + aset.Name;
opp.CurrencyIsoCode = aset.CurrencyIsoCode;
opp.CLOSEDATE = aset.Maintenance_Expires__c;
opp.Asset__c = aset.ID;
if (NewOpp){
opp.StageName = 'Work in Progress';
OppToInsert.add(opp);
// insert opp;
} else {OppToUpdate.add(opp);}
NewOpp = false;
}
}
if (OppToUpdate.size() > 0) {update OppToUpdate;}
if (OppToInsert.size() > 0) {insert OppToInsert;}
}
You need to use an aggregate-query-update (AQU) pattern. Fortunately, the pattern is shortcut a little because of where this trigger is executing. Here's how I would write this trigger:
Let me know if you need any assistance understanding it.
All Answers
Kieran,
I see that you are running a SOQL inside for loop which is not recommended at all.You are bound to get Too many SOQL queries exception if the no. of records is more than 100.
I would suggest you need to bulkify your code by removing the SOQL from for loop.
You need to use an aggregate-query-update (AQU) pattern. Fortunately, the pattern is shortcut a little because of where this trigger is executing. Here's how I would write this trigger:
Let me know if you need any assistance understanding it.
No the insert and update are not in the loop. There is only one } after them.
thanks anyway
Kieran,
I am not talking about the DML statements ,I am talking about SOQL query below one which is running inside for loop.
opp = [select Name,ACCOUNTID,CurrencyIsoCode,CLOSEDATE,Asset__c from Opportunity where asset__c =: aset.ID limit 1];