Sign Up ›

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

Help bulkifying a trigger - too many SOQL queries when finding conversionrates

Here is a trigger I wrote. I understand that I need to get the conversion rate SOQL query out of the for statement:

trigger OpportunityBeforeInsertUpdate on Opportunity (before insert, before update) {


for(Opportunity opp : Trigger.new) {
    if (opp.Total_Amount__c > 0) {
        opp.Amount = opp.Total_Amount__c;
        system.debug('opp amount is ' + opp.Amount);
        } 
    
    
     if(Trigger.isInsert 
     || 
     ((opp.Amount != Trigger.oldMap.get(opp.Id).Amount || opp.CurrencyIsoCode != Trigger.oldMap.get(opp.Id).CurrencyIsoCode)
     &&
     (!opp.IsClosed || (opp.IsClosed && !Trigger.oldMap.get(opp.Id).IsClosed)))
     ||
     (!opp.IsClosed && Trigger.oldMap.get(opp.Id).IsClosed)
     ) 
         {
   double conversionrate = [select conversionrate from CurrencyType where IsoCode = :opp.CurrencyIsoCode];
            opp.Amount_USD__c = opp.Amount/ ConversionRate ;
        }
}
}

 so I wrote the following but still get the Too many SOQL queries error when running a test where 200 opps are inserted:

trigger OpportunityBeforeInsertUpdate on Opportunity (before insert, before update) {

List<CurrencyType> currs = new List<CurrencyType>
([select id, IsoCode, ConversionRate from CurrencyType where IsActive=true limit 200]);

Map<String, Decimal> ConversionRates = new Map<String, Decimal>();

for(integer i=0; i<currs.size(); i++){
ConversionRates.put(currs[i].IsoCode, currs[i].ConversionRate);
}


for(Opportunity opp : Trigger.new) {
    if (opp.Total_Amount__c > 0) {
        opp.Amount = opp.Total_Amount__c;
        system.debug('opp amount is ' + opp.Amount);
        } 
    
    
     if(Trigger.isInsert 
     || 
     ((opp.Amount != Trigger.oldMap.get(opp.Id).Amount || opp.CurrencyIsoCode != Trigger.oldMap.get(opp.Id).CurrencyIsoCode)
     &&
     (!opp.IsClosed || (opp.IsClosed && !Trigger.oldMap.get(opp.Id).IsClosed)))
     ||
     (!opp.IsClosed && Trigger.oldMap.get(opp.Id).IsClosed)
     ) 
         {
            opp.Amount_USD__c = opp.Amount/ ConversionRates.get(opp.CurrencyIsoCode) ;
        }
}
}

 

yunicfayunicfa

try with

 

 

List<CurrencyType> currs = [select id, IsoCode, ConversionRate from CurrencyType where IsActive=true limit 200];

intead of

 

List<CurrencyType> currs = new List<CurrencyType> ([select id, IsoCode, ConversionRate from CurrencyType where IsActive=true limit 200]);
*rdinakaran**rdinakaran*

Hi Gail,

I didnt found any problems in the trigger when putting it in my dev org. So error occured due to some other trigger in opportunity.

 

For Testing deactivate all OTHER before triggers in opportunity and run the test class only for this trigger.

 

I hope it wont  show any error to  you.

 

Please reply me if it solves your issue.

 

Thanks,

RajaDinakaran