Apex Code Development You need to sign in to do that
Don't have an account?
Marcel Gjaltema 7 Timebased workflow to update field, which fires a trigger and results in 'Too many SOQL queries: 101'
Hi,
We have a trigger that is 'firing' when a status field is updated by a timebased workflow rule, but when the trigger is firing we are getting a lot of 'Too many SOQL queries: 101' errors. Does somebody has some ideas where to start looking? The trigger looks like this:
/*
* Trigger : Contract_Start_New_Renewal
* Version : 1.3
* Author : M. Gjaltema, Proact
* Lastupdate : 5-5-2015
*
*/
trigger Contract_Start_New_Renewal on Contract (after update) {
system.debug('Contract_Start_New_Renewal - Trigger is fired.');
/* Get the correct opportunity record type */
Id rtId = [select Id, Name from RecordType where name='Proposing' and SObjectType='Opportunity' limit 1].Id;
// Get related account details
Set<Id> idsAccs = new Set<Id>();
For (Contract con : Trigger.new){ idsAccs.add(con.AccountId); }
Map<Id, Account> mAccs = new Map<Id, Account>([SELECT Id, Name, OwnerId FROM Account WHERE Id IN: idsAccs]);
system.debug('Contract_Start_New_Renewal - Account list size is ' + mAccs.size());
/* Get dvs products */
Map<String, Id> mPbeIds = new Map<String, Id>();
For(PricebookEntry pbe : [SELECT Id, CurrencyIsoCode From PriceBookEntry WHERE ProductCode = 'DVS-ALL']) {
mPbeIds.put(pbe.CurrencyIsoCode, pbe.Id);
}
system.debug('Contract_Start_New_Renewal - DVS product list size is ' + mPbeIds.size());
/* DUPE CHECK : Get existing contract renewals and DVS opportunities */
Map<Id, Contract_Renewal__c> mRnws = new Map<Id, Contract_Renewal__c>([SELECT Id, CurrentStartDate__c, CurrentEnddate__c, Contract__c FROM Contract_Renewal__c WHERE Contract__c IN: trigger.new]);
system.debug('Contract_Start_New_Renewal - Number of dupe check renewals are : ' + mRnws.size());
Map<Id, Opportunity> mOpps = new Map<Id, Opportunity>([SELECT Id, Name, AccountId, CloseDate, Amount FROM Opportunity WHERE AccountId IN : idsAccs AND Name LIKE 'Support Renewal for Direct Vendor Support']);
system.debug('Contract_Start_New_Renewal - Number of dupe check DVS opportunities are : ' + mOpps.size());
for (Contract contract : trigger.new) {
system.debug('Contract_Start_New_Renewal - In loop for all triggered contracts ' + contract.Id);
/* Check if contract status changed to renewal AND extension period > 0, otherwise don't do anything. */
If (contract.ContractStatus__c == 'Renewal') If (Trigger.oldMap.get(contract.Id).ContractStatus__c != 'Renewal') If (contract.ContractExtensionPeriod__c > 0) {
system.debug('Contract_Start_New_Renewal - Contract status changed to ' + contract.ContractStatus__c);
/* Get Contract Administration Settings */
Boolean CreateDVSOpportunities = false;
Boolean CreateCSRenewals = false;
Boolean CreateMCSRenewals = false;
Boolean DVSAssignNewOppToAccountOwner = false;
String DVSNewOppOwnerId = '';
ContractAdminSettings__c mCAS = ContractAdminSettings__c.getValues(contract.Proact_Country__c);
If (mCAS != null){
CreateDVSOpportunities = mCAS.CreateDVSOpportunities__c;
CreateCSRenewals = mCAS.CreateCSRenewals__c;
CreateMCSRenewals = mCAS.CreateMCSRenewals__c;
DVSAssignNewOppToAccountOwner = mCAS.DVSAssignNewOppToAccountOwner__c;
DVSNewOppOwnerId = mCAS.DVSNewOppOwnerId__c;
}
system.debug('Contract_Start_New_Renewal - contract.Proact_Country__c is ' + contract.Proact_Country__c);
system.debug('Contract_Start_New_Renewal - CreateDVSOpportunities custom setting is ' + CreateDVSOpportunities);
system.debug('Contract_Start_New_Renewal - CreateCSRenewals custom setting is ' + CreateCSRenewals);
system.debug('Contract_Start_New_Renewal - CreateMCSRenewals custom setting is ' + CreateMCSRenewals);
system.debug('Contract_Start_New_Renewal - DVSAssignNewOppToAccountOwner custom setting is ' + DVSAssignNewOppToAccountOwner);
system.debug('Contract_Start_New_Renewal - DVSNewOppOwnerId custom setting is ' + DVSNewOppOwnerId);
/* Process Direct Vendor Support renewals */
If (CreateDVSOpportunities == true) If (contract.RecordTypeId == '012D00000007MqfIAE') {
system.debug('Contract_Start_New_Renewal - DVS - contract.RecordTypeId is ' + contract.RecordTypeId);
/* Check for existing DVS opportunity */
Boolean oppExists = false;
For (Opportunity oppDup : mOpps.values()) {
If (oppDup.AccountId == contract.AccountId)
If (oppDup.CloseDate == contract.EndDate)
If (oppDup.Amount == contract.ContractValue__c) {
oppExists = true;
}
}
system.debug('Contract_Start_New_Renewal - DVS - Checking for existing DVS opportunity : ' + oppExists);
// Create new opportunity
If (!oppExists || mOpps.Size() == 0) {
// Check if opportunity needs to be assigned to static owner or account owner
If (DVSAssignNewOppToAccountOwner == True){
DVSNewOppOwnerId = String.valueOf(mAccs.get(contract.AccountId).OwnerId);
} else {
DVSNewOppOwnerId = mCAS.DVSNewOppOwnerId__c;
}
Opportunity opp = new Opportunity(AccountId = contract.AccountId,
CurrencyIsoCode = contract.CurrencyIsoCode,
OwnerId = DVSNewOppOwnerId,
RecordTypeId = rtId,
Amount = contract.ContractValue__c,
Description = 'Original opportunity was ' + URL.getSalesforceBaseUrl().toExternalForm() + '/' + contract.Opportunity__c,
StageName = 'Price Estimate Submitted',
LeadSource = 'Existing Customer',
Name = 'Support Renewal for Direct Vendor Support',
CloseDate = contract.EndDate);
insert opp;
system.debug('Contract_Start_New_Renewal - DVS - Created new opportunity : ' + opp.Id);
/* Create a new product line item */
system.debug('Contract_Start_New_Renewal - DVS - Opportunity Line Item will be : ' + mPbeIds.get(contract.CurrencyIsoCode));
OpportunityLineItem li = new OpportunityLineItem(OpportunityId = opp.Id,
PricebookEntryId = mPbeIds.get(contract.CurrencyIsoCode),
ServiceDate = contract.EndDate,
Quantity = contract.ContractTerm,
Unit_Cost_Price__c = contract.ContractCosts__c / contract.ContractTerm,
UnitPrice = contract.ContractValue__c / contract.ContractTerm);
insert li;
system.debug('Contract_Start_New_Renewal - DVS - Opportunity Line Item inserted : ' + li.Id);
} // End check if opportunity exists
} //End check if contract is Direct Vendor Support record type
/* Process Customer Support renewals */
If (CreateCSRenewals == true) If (contract.RecordTypeId == '012D00000007MqaIAE') {
system.debug('Contract_Start_New_Renewal - CS - contract.RecordTypeId is ' + contract.RecordTypeId);
/* Check for existing contract renewal */
Boolean rnwExists = false;
For (Contract_Renewal__c rnwDup : mRnws.values()) {
If (rnwDup.Contract__c == contract.Id)
If (rnwDup.CurrentStartDate__c == contract.StartDate)
If (rnwDup.CurrentEndDate__c == contract.Enddate) {
rnwExists = true;
}
}
system.debug('Contract_Start_New_Renewal - CS - Checking for existing renewal : ' + rnwExists);
If (!rnwExists || mRnws.Size() == 0) {
Contract_Renewal__c rnw = new Contract_Renewal__c(Contract__c = contract.Id,
CurrencyIsoCode = contract.CurrencyIsoCode,
Account__c = contract.AccountId,
CurrentEndDate__c = contract.EndDate,
CurrentContractCosts__c = contract.ContractCosts__c,
CurrentContractTerm__c = contract.ContractTerm,
CurrentStartDate__c = contract.StartDate,
CurrentContractValue__c = contract.ContractValue__c,
contractNoticePeriod__c = contract.ContractNoticePeriod__c,
RenewalApproved__c = false,
CurrentRenewalDate__c = contract.RenewalDate__C,
PurchaseAM__c = (((contract.ContractCosts__c / contract.ContractTerm) * contract.ContractExtensionPeriod__c) / 48 * 100) * 0.6,
CurrentContractExtensionPeriod__c = contract.ContractExtensionPeriod__c);
insert rnw;
system.debug('Contract_Start_New_Renewal - CS - Inserted new contract renewal : ' + rnw.Id);
} // End check for existing contract renewal
} // End check if contract is Customer Support record type
/* Process MCS renewals */
If (CreateMCSRenewals == true) If (contract.RecordTypeId == '012D00000007MqkIAE') {
system.debug('Contract_Start_New_Renewal - MCS - contract.RecordTypeId is ' + contract.RecordTypeId);
/* Check for existing contract renewal */
Boolean rnwExists = false;
For (Contract_Renewal__c rnwDup : mRnws.values()) {
If (rnwDup.Contract__c == contract.Id)
If (rnwDup.CurrentStartDate__c == contract.StartDate)
If (rnwDup.CurrentEndDate__c == contract.Enddate) {
rnwExists = true;
}
}
system.debug('Contract_Start_New_Renewal - MCS - Checking for existing renewal : ' + rnwExists);
If (!rnwExists || mRnws.Size() == 0) {
Contract_Renewal__c rnw = new Contract_Renewal__c(Contract__c = contract.Id,
Account__c = contract.AccountId,
CurrencyIsoCode = contract.CurrencyIsoCode,
CurrentEndDate__c = contract.EndDate,
CurrentContractCosts__c = contract.ContractCosts__c,
CurrentContractTerm__c = contract.ContractTerm,
CurrentStartDate__c = contract.StartDate,
CurrentContractValue__c = contract.ContractValue__c,
contractNoticePeriod__c = contract.ContractNoticePeriod__c,
RenewalApproved__c = false,
CurrentRenewalDate__c = contract.RenewalDate__C,
PurchaseAM__c = (((contract.ContractCosts__c / contract.ContractTerm) * contract.ContractExtensionPeriod__c) / 48 * 100) * 0.6,
CurrentContractExtensionPeriod__c = contract.ContractExtensionPeriod__c);
insert rnw;
system.debug('Contract_Start_New_Renewal - MCS - Inserted new contract renewal : ' + rnw.Id);
} // End check for existing contract renewal
} // End check if contract is MCS record type
} // End if status changed to renewal
} // End loop contracts in trigger
} // End trigger
Thank you very much!
- First I would check the debug log to see where the queries add up;
- Generically speaking, It is not best practice to do DML inside a loop... create a list(like myOpps) of opps, add to it in the loop(myOpps.add(opp) and after the loop do an insert myOpps; Same with rnw.
I hope this helps, please like if it does. Let me know if you need more help.Hi Lynn,
Thank you for your response. I'm curious how to find the debug logs, as the trigger is fired by a timebased workflow field update?!
Secondly, as far as I can see I don't have a DML query within a for loop, but please correct me if i'm wrong ;)
Thx, Marcel