Sign Up ›

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

Trigger not supported for Bulk import operations

Hi,

 

 I have some problem in my trigger. when i insert the single record its working perfect. But when i import bulk records it cause the Too many SOQL queries Error.

 

 Please help me:

 

trigger BazookaCode on Account(after insert)
{
string name,fname,lname,substr,anumber,code,code1,code2,code3,code4,code5;
String city='';
String state='';
Integer acount;
Integer acount1;
Set<id> aid = new Set<id>();

for(Account obj : Trigger.new)
{
aid.add(obj.id);
}

List<Account> la = [SELECT id,Name,ShippingCity,ShippingState,AccountNumber FROM Account WHERE id in :aid];
for(Account a : la)
{
if(a.AccountNumber == null)
{
name = a.Name;
fname = a.Name.mid(0,3);
substr = name.substringAfter(' ')+name;
lname = substr.mid(0,3);
city = a.ShippingCity.mid(0,3);
state = a.ShippingState.mid(0,2);

if(name!=substr)
{
code = fname+lname+state;
code1 = code.toUpperCase();
code2 = code1.replaceAll('\\W','');
acount = [SELECT COUNT() FROM Account WHERE AccountNumber LIKE :code2+'%'];
if(acount == 0)
{
a.AccountNumber = code2;
}
else if(acount > 0 && acount < 9)
{
a.AccountNumber = code2 + 0 + acount;
}
else
{
a.AccountNumber = code2 + acount;
}
}
else
{
code3 = fname+city+state;
code4 = code3.toUpperCase();
code5 = code4.replaceAll('\\W','');
acount1 = [SELECT COUNT() FROM Account WHERE AccountNumber LIKE :code5+'%'];
if(acount1 == 0)
{
a.AccountNumber = code5;
}
else if(acount1 > 0 && acount1 < 9)
{
a.AccountNumber = code5 + 0 + acount1;
}
else
{
a.AccountNumber = code5 + acount1;
}
}
update a;
}
}
}

apex_keenapex_keen

You  have put  query  'acount = [SELECT COUNT() FROM Account WHERE AccountNumber LIKE :code2+'%'];' inside for loop which seems to be causing problem. Put this one outside the foor loop as this seems to be giving constant result wherever it will be running.
TrinayTrinay

HI apex,

 Thank you for your reply. it doen't give the constant result. it count the number of records based on the String Code2. i am trying to put the query out of the loop but i don't get expected value.

 

 can you please give some idea fro moving the query out of the loop. 
Andrew WilkinsonAndrew Wilkinson

I changed the code2 one to be outside of the for loop. Do the same for code5 and you should stop getting the error.

trigger BazookaCode on Account(after insert)
{
string name,fname,lname,substr,anumber,code,code1,code2,code3,code4,code5;
String city='';
String state='';
Integer acount;
Integer acount1;
Set<id> aid = new Set<id>();
//value for code2
Integer code2Count = [SELECT COUNT() FROM Account WHERE AccountNumber LIKE :code2+'%'];
//do the same for code 5
for(Account obj : Trigger.new)
{
aid.add(obj.id);
}

List<Account> la = [SELECT id,Name,ShippingCity,ShippingState,AccountNumber FROM Account WHERE id in :aid];
for(Account a : la)
{
if(a.AccountNumber == null)
{
name = a.Name;
fname = a.Name.mid(0,3);
substr = name.substringAfter(' ')+name;
lname = substr.mid(0,3);
city = a.ShippingCity.mid(0,3);
state = a.ShippingState.mid(0,2);

if(name!=substr)
{
code = fname+lname+state;
code1 = code.toUpperCase();
code2 = code1.replaceAll('\\W','');
acount = code2Count;
if(acount == 0)
{
a.AccountNumber = code2;
}
else if(acount > 0 && acount < 9)
{
a.AccountNumber = code2 + 0 + acount;
}
else
{
a.AccountNumber = code2 + acount;
}
}
else
{
code3 = fname+city+state;
code4 = code3.toUpperCase();
code5 = code4.replaceAll('\\W','');
acount1 = [SELECT COUNT() FROM Account WHERE AccountNumber LIKE :code5+'%'];
if(acount1 == 0)
{
a.AccountNumber = code5;
}
else if(acount1 > 0 && acount1 < 9)
{
a.AccountNumber = code5 + 0 + acount1;
}
else
{
a.AccountNumber = code5 + acount1;
}
}
update a;
}
}
}

 
bpl3792bpl3792

If you're still having problems with record load, you might want to leverage a batchable class. Bulkification seems on point in the code though so It's really going to come down to how much data you have.
apex_keenapex_keen

I see a point in Trinay''s argument. Logic to get 'code2' is inside the for loop. so we can't put the query outside the for loop. Wondering what option we have, when we have to have execute query inside 'for loop' . Any idea?
harsha__charsha__c

Hi keen,

 

That is 100% not a best practice and not allowable also in apex as keeping the Governer limits in mind..!

 

One has to try to avoid this by the help of maps or any other collections

 

In Trinay's case the following can be done

 

Take a map as map<Integer, Account>()

 

We can store the Accounts in this map and inside the for loop we can just use

 

if(map.containskey(code2))
{
account = map.get(code2);
}

 But this again will face limitations of collection size...!

 

 

However if Number of Account records are less than 1000 this can help out