function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

System.LimitException: Too many SOQL queries: 101

Trying to rewrite a trigger that keeps getting System.LimitException: Too many SOQL queries: 101.  Thought it was in bulk structure but I guess not.  The only reason there is a group by on the query was to try to limit the amount of data being returned.  That did not help.

 

trigger CheckDuplicateContractNumber on Apttus__APTS_Agreement__c (before update) 
{
List<Id> TheContractIds = new List<Id>(Trigger.newMap.keySet());
Integer theCountFound;

for (Apttus__APTS_Agreement__c theAgreement :Trigger.new ) 
{
        for(AggregateResult ar : [SELECT Count(Id) thecounter, APTPS_BPCS_Contract_Number__c BPCSCntr from Apttus__APTS_Agreement__c WHERE APTPS_BPCS_Contract_Number__c = :theAgreement.APTPS_BPCS_Contract_Number__c AND Id Not IN :TheContractIds Group BY APTPS_BPCS_Contract_Number__c Limit 2])
        theCountFound = Integer.valueOf(ar.get('thecounter'));  
            if (theCountFound >= 1) 
            {
                theAgreement.addError('The Contract Number already exists. Please enter a unique number.');
            }
        }
  }

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
trigger checkduplicatecontractnumber on apptus__apts_agreement__c (before insert, before update) {
	map<string,apptus__apts_agreement__c> agreements = new map<string,apptus__apts_agreement_c>();
	// Check dupes in current transaction
	for(apptus__apts_agreement__c record:trigger.new)
		if(agreements.containskey(record.aptps_bpcs_contract_number__c))
			record.aptps_bpcs_contract_number__c.addError('Duplicate contract number found.');
		else
			agreements.put(record.aptps_bpcs_contract_number__c,record);
	// Check dupes in database
	for(apptus__apts_agreement__c record:[SELECT Id,aptps_bpcs_contract_number__c from apptus__apts_agreement__c where aptps_bpcs_contract_number__c in :agreements.keyset()])
		if(agreements.get(record.aptps_bpcs_contract_number__c).id!=record.id)
			agreements.get(record.aptps_bpcs_contract_number__c).aptps_bpcs_contract_number__c.addError('Duplicate contract number found.');
}

Although... why didn't you just use a unique index?

All Answers

sfdcfoxsfdcfox
trigger checkduplicatecontractnumber on apptus__apts_agreement__c (before insert, before update) {
	map<string,apptus__apts_agreement__c> agreements = new map<string,apptus__apts_agreement_c>();
	// Check dupes in current transaction
	for(apptus__apts_agreement__c record:trigger.new)
		if(agreements.containskey(record.aptps_bpcs_contract_number__c))
			record.aptps_bpcs_contract_number__c.addError('Duplicate contract number found.');
		else
			agreements.put(record.aptps_bpcs_contract_number__c,record);
	// Check dupes in database
	for(apptus__apts_agreement__c record:[SELECT Id,aptps_bpcs_contract_number__c from apptus__apts_agreement__c where aptps_bpcs_contract_number__c in :agreements.keyset()])
		if(agreements.get(record.aptps_bpcs_contract_number__c).id!=record.id)
			agreements.get(record.aptps_bpcs_contract_number__c).aptps_bpcs_contract_number__c.addError('Duplicate contract number found.');
}

Although... why didn't you just use a unique index?

This was selected as the best answer
ShadowlessKickShadowlessKick

You are an EXCELLENT contributor!  Thank you!

 

You asked why I did not just use a "Unique Index."  Could you expand on that idea?  I am self-taught and any insight on new concepts are always appreciated. 
sfdcfoxsfdcfox
Text fields can be edited to use a Unique Index (navigate to the field in Setup, and edit the field). This is standard salesforce.com feature that allows administrators to configure a field to be unique. It requires no code, uses no queries, and processes faster than code, as it uses an actual database index to optimize the operation.