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
Charlie Chaplin 2017Charlie Chaplin 2017 

System.LimitException: Too many SOQL queries: 101 Stack Trace:

Need some help. I am moving this trigger from Partial sandbox to production. It is generating this error. 
System.LimitException: Too many SOQL queries: 101
Stack Trace: Trigger.DealflowCustomApprovalProcessTriggerAIAU: line 5, column 1

Please let me know what am i doing wrong here. 

trigger DealflowCustomApprovalProcessTriggerAIAU on Approval_History__c (after insert, after update) {
    
    Set<Id> CpId = new Set<Id>();
    List<Underwriting__c> UpdatedCP = new List<Underwriting__c>();
    List<Approval_history__c> AH = new List<Approval_history__c>([Select Id, RecordType.DeveloperName, current_approver__c, type__c,  status__c, credit_package__c,actual_approver__c,preliminary_approval__c FROM Approval_history__c WHERE Id IN: Trigger.newMap.keySet()]);
    
    For(Approval_history__c A:AH){
        CpId.add(A.credit_Package__c);
        Map<Id, Underwriting__c> Und = new Map<Id,Underwriting__c>([SELECT Id, Preliminary_Credit_Approval__c, Preliminary_Credit_Approval_Date__c, Final_Initial_Approver__c, Lock_Credit_Package__c,underwriting_stage__c,final_approver__c,final_decision_date__c, RecordType.DeveloperName,Initial_Credit_Approval__c,Initial_Credit_Approval_Date__c,final_preliminary_approver__c from Underwriting__c WHERE Id IN:CpId]);
        Underwriting__c u = und.get(A.credit_package__c);
        
        If(Trigger.IsInsert && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || U.RecordType.DeveloperName == 'Credit_Package') && U.underwriting_stage__c =='Underwriting Review'){
            A.type__c = 'Initial Loan Approval';
            u.underwriting_stage__c = 'Pending Initial Approval';
            UpdatedCP.add(u);
            Update A;
        }
        
        If(Trigger.IsInsert && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || U.RecordType.DeveloperName == 'Credit_Package') && U.underwriting_stage__c =='Borrower Accepted'){
            A.type__c = 'Preliminary Loan Approval';
            u.underwriting_stage__c = 'Pending Preliminary Approval';
            UpdatedCP.add(u);
            Update A;
        }
        
        If(Trigger.IsInsert && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || U.RecordType.DeveloperName == 'Credit_Package') && U.underwriting_stage__c =='In Process'){
            A.type__c = 'Final Loan Approval';
            u.underwriting_stage__c = 'Pending Final Approval';
            UpdatedCP.add(u);
            Update A;
        }
        
        If(Trigger.IsInsert && U.underwriting_stage__c =='Pending Initial Approval'){
            A.type__c = 'Initial Loan Approval';
            
            Update A;}
        
        If(Trigger.IsInsert && U.underwriting_stage__c =='Pending Preliminary Approval'){
            A.type__c = 'Preliminary Loan Approval';       
            Update A;
        }
        
        If(Trigger.IsInsert && U.underwriting_stage__c =='Pending Final Approval'){
            A.type__c = 'Final Loan Approval';
            Update A;
        }
        
        If(Trigger.IsInsert && (((U.RecordType.DeveloperName =='Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || 
                                  U.RecordType.DeveloperName == 'MT_Credit_Package') && U.underwriting_stage__c =='In Process') || 
                                (U.RecordType.DeveloperName !='MT_Credit_Package' && U.RecordType.DeveloperName != 'Credit_Package' && 
                                 U.RecordType.DeveloperName != 'Commercial_Direct' && U.underwriting_stage__c =='Underwriting Review'))){
                                     
                                     u.underwriting_stage__c = 'Pending Final Approval';
                                     A.type__c = 'Final Loan Approval';  
                                     //u.Committee_Type__c = a.Committee_Type__c;
                                     UpdatedCP.add(u);
                                     Update A;
                                     
                                 }
        
        //the isInsert conditions below will not execute as the below types are not set implicitly.  They are set on update, not insert.
        If(trigger.IsInsert && A.Status__c == 'Final Approved' && A.type__c == 'Initial Loan Approval'){
            U.underwriting_stage__c = 'Initial Credit Approved';
            U.Initial_Credit_Approval__c = TRUE;
            U.Initial_Credit_Approval_Date__c = date.today();
            U.Final_Initial_Approver__c = a.actual_approver__c;              
            UpdatedCP.add(u);
        } 
        
        If(trigger.IsInsert && A.Status__c == 'Final Approved' && A.type__c == 'Preliminary Loan Approval'){
            U.underwriting_stage__c = 'In Process';
            U.Preliminary_Credit_Approval__c = TRUE;
            U.Preliminary_Credit_Approval_Date__c = date.today();
            U.Final_Preliminary_Approver__c = a.actual_approver__c;            
            UpdatedCP.add(u);
        } 
        
        if(trigger.IsInsert && A.Status__c == 'Final Approved' && A.type__c == 'Final Loan Approval'){
            U.underwriting_stage__c = 'Final Credit Approved';
            U.final_approval__c = TRUE;
            U.final_decision_date__c = date.today();
            U.final_approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }
        //the isInsert conditions above will not execute as the below types are not set implicitly.  They are set on update, not insert.
        If(trigger.IsUpdate && A.Status__c == 'Final Approved' && trigger.oldmap.get(A.Id).Status__c =='Pending' && A.type__c == 'Initial Loan Approval'){
            System.debug('89');
            U.underwriting_stage__c = 'Initial Credit Approved';
            U.Initial_Credit_Approval__c = TRUE;
            U.Initial_Credit_Approval_Date__c = date.today();
            U.Final_Initial_Approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }  
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Initial Loan Approval'){
            U.underwriting_stage__c = 'Underwriting Review';        
            UpdatedCP.add(u);
        }
        
        If(trigger.IsUpdate && A.Status__c == 'Final Approved' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Preliminary Loan Approval'){
            U.underwriting_stage__c = 'In Process';
            U.Preliminary_Credit_Approval__c = TRUE;
            U.Preliminary_Credit_Approval_Date__c = date.today();
            U.final_preliminary_approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }  
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Preliminary Loan Approval'){
            U.underwriting_stage__c = 'Borrower Accepted';
            UpdatedCP.add(u);
        }
        
        If(trigger.IsUpdate && A.Status__c == 'Final Approved' && trigger.oldmap.get(A.Id).Status__c =='Pending' && A.type__c == 'Final Loan Approval'){
            u.underwriting_stage__c = 'Final Credit Approved';
            u.final_approval__c = TRUE;
            u.final_decision_date__c = date.today();
            u.final_approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Final Loan Approval' && U.RecordType.DeveloperName !='MT_Credit_Package' && U.RecordType.DeveloperName != 'Credit_Package'){
            U.underwriting_stage__c = 'Underwriting Review';
            UpdatedCP.add(u);
        }        
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Final Loan Approval' && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Credit_Package')){
            U.underwriting_stage__c = 'In Process';       
            UpdatedCP.add(u);
        } 
        
        if(trigger.IsUpdate && a.RecordType.DeveloperName == 'Credit_Committee'){
            if(u.Underwriting_Stage__c == 'In Process'){                   
                u.Final_Preliminary_Approver__c = a.Actual_Approver__c;
                u.Committee_Type__c = a.Current_Approver__c;                         
            }else if(u.Underwriting_Stage__c == 'Final Credit Approved'){                 
                u.Final_Approver__c = a.Actual_Approver__c;
                u.Committee_Type__c = a.Current_Approver__c;                 
            }else{                   
                u.Final_Initial_Approver__c = a.Actual_Approver__c;
                u.Committee_Type__c = a.Current_Approver__c;                   
            }    
            UpdatedCP.add(u);
        }
        
        If(UpdatedCp.size()>0){
            update updatedcp;}
    } 
}
 
SalesFORCE_enFORCErSalesFORCE_enFORCEr
You should not write any query inside for loop.
Charlie Chaplin 2017Charlie Chaplin 2017
letely new to triggers can you point where i am doing a mistake.
UC InnovationUC Innovation
Hi Jamil,

I took a look at your code and it seems like you are writing a query within a for loop.This is generally bad practice as it tends to cause errors like these. The best way to prevent these errors is to avoid writing queries within loops and try to bulkify your code. Try this out instead
  
trigger DealflowCustomApprovalProcessTriggerAIAU on Approval_History__c (after insert, after update) {
    List<Underwriting__c> UpdatedCP = new List<Underwriting__c>();
    List<Approval_history__c> AH = new List<Approval_history__c>([Select Id, RecordType.DeveloperName, current_approver__c, type__c,  status__c, credit_package__c,actual_approver__c,preliminary_approval__c FROM Approval_history__c WHERE Id IN: Trigger.newMap.keySet()]);
    Set<Id> CpId = new Set<Id>();
	
	For(Approval_history__c A:AH){
		CpId.add(A.credit_Package__c);
	}
	
	// move map outside of the for loop to prevent soql query limit
	Map<Id, Underwriting__c> Und = new Map<Id,Underwriting__c>([SELECT Id, 
																	   Preliminary_Credit_Approval__c, 
																	   Preliminary_Credit_Approval_Date__c, 
																	   Final_Initial_Approver__c, 
																	   Lock_Credit_Package__c,
																	   underwriting_stage__c,
																	   final_approver__c,
																	   final_decision_date__c, 
																	   RecordType.DeveloperName,
																	   Initial_Credit_Approval__c,
																	   Initial_Credit_Approval_Date__c,
																	   final_preliminary_approver__c 
																FROM Underwriting__c 
																WHERE Id IN:CpId]);
	
    For(Approval_history__c A:AH){
        Underwriting__c u = und.get(A.credit_package__c);
        
        If(Trigger.IsInsert && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || U.RecordType.DeveloperName == 'Credit_Package') && U.underwriting_stage__c =='Underwriting Review'){
            A.type__c = 'Initial Loan Approval';
            u.underwriting_stage__c = 'Pending Initial Approval';
            UpdatedCP.add(u);
            Update A;
        }
        
        If(Trigger.IsInsert && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || U.RecordType.DeveloperName == 'Credit_Package') && U.underwriting_stage__c =='Borrower Accepted'){
            A.type__c = 'Preliminary Loan Approval';
            u.underwriting_stage__c = 'Pending Preliminary Approval';
            UpdatedCP.add(u);
            Update A;
        }
        
        If(Trigger.IsInsert && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || U.RecordType.DeveloperName == 'Credit_Package') && U.underwriting_stage__c =='In Process'){
            A.type__c = 'Final Loan Approval';
            u.underwriting_stage__c = 'Pending Final Approval';
            UpdatedCP.add(u);
            Update A;
        }
        
        If(Trigger.IsInsert && U.underwriting_stage__c =='Pending Initial Approval'){
            A.type__c = 'Initial Loan Approval';
            
            Update A;}
        
        If(Trigger.IsInsert && U.underwriting_stage__c =='Pending Preliminary Approval'){
            A.type__c = 'Preliminary Loan Approval';       
            Update A;
        }
        
        If(Trigger.IsInsert && U.underwriting_stage__c =='Pending Final Approval'){
            A.type__c = 'Final Loan Approval';
            Update A;
        }
        
        If(Trigger.IsInsert && (((U.RecordType.DeveloperName =='Credit_Package' || U.RecordType.DeveloperName == 'Commercial_Direct' || 
                                  U.RecordType.DeveloperName == 'MT_Credit_Package') && U.underwriting_stage__c =='In Process') || 
                                (U.RecordType.DeveloperName !='MT_Credit_Package' && U.RecordType.DeveloperName != 'Credit_Package' && 
                                 U.RecordType.DeveloperName != 'Commercial_Direct' && U.underwriting_stage__c =='Underwriting Review'))){
                                     
                                     u.underwriting_stage__c = 'Pending Final Approval';
                                     A.type__c = 'Final Loan Approval';  
                                     //u.Committee_Type__c = a.Committee_Type__c;
                                     UpdatedCP.add(u);
                                     Update A;
                                     
                                 }
        
        //the isInsert conditions below will not execute as the below types are not set implicitly.  They are set on update, not insert.
        If(trigger.IsInsert && A.Status__c == 'Final Approved' && A.type__c == 'Initial Loan Approval'){
            U.underwriting_stage__c = 'Initial Credit Approved';
            U.Initial_Credit_Approval__c = TRUE;
            U.Initial_Credit_Approval_Date__c = date.today();
            U.Final_Initial_Approver__c = a.actual_approver__c;              
            UpdatedCP.add(u);
        } 
        
        If(trigger.IsInsert && A.Status__c == 'Final Approved' && A.type__c == 'Preliminary Loan Approval'){
            U.underwriting_stage__c = 'In Process';
            U.Preliminary_Credit_Approval__c = TRUE;
            U.Preliminary_Credit_Approval_Date__c = date.today();
            U.Final_Preliminary_Approver__c = a.actual_approver__c;            
            UpdatedCP.add(u);
        } 
        
        if(trigger.IsInsert && A.Status__c == 'Final Approved' && A.type__c == 'Final Loan Approval'){
            U.underwriting_stage__c = 'Final Credit Approved';
            U.final_approval__c = TRUE;
            U.final_decision_date__c = date.today();
            U.final_approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }
        //the isInsert conditions above will not execute as the below types are not set implicitly.  They are set on update, not insert.
        If(trigger.IsUpdate && A.Status__c == 'Final Approved' && trigger.oldmap.get(A.Id).Status__c =='Pending' && A.type__c == 'Initial Loan Approval'){
            System.debug('89');
            U.underwriting_stage__c = 'Initial Credit Approved';
            U.Initial_Credit_Approval__c = TRUE;
            U.Initial_Credit_Approval_Date__c = date.today();
            U.Final_Initial_Approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }  
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Initial Loan Approval'){
            U.underwriting_stage__c = 'Underwriting Review';        
            UpdatedCP.add(u);
        }
        
        If(trigger.IsUpdate && A.Status__c == 'Final Approved' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Preliminary Loan Approval'){
            U.underwriting_stage__c = 'In Process';
            U.Preliminary_Credit_Approval__c = TRUE;
            U.Preliminary_Credit_Approval_Date__c = date.today();
            U.final_preliminary_approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }  
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Preliminary Loan Approval'){
            U.underwriting_stage__c = 'Borrower Accepted';
            UpdatedCP.add(u);
        }
        
        If(trigger.IsUpdate && A.Status__c == 'Final Approved' && trigger.oldmap.get(A.Id).Status__c =='Pending' && A.type__c == 'Final Loan Approval'){
            u.underwriting_stage__c = 'Final Credit Approved';
            u.final_approval__c = TRUE;
            u.final_decision_date__c = date.today();
            u.final_approver__c = a.actual_approver__c;
            UpdatedCP.add(u);
        }
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Final Loan Approval' && U.RecordType.DeveloperName !='MT_Credit_Package' && U.RecordType.DeveloperName != 'Credit_Package'){
            U.underwriting_stage__c = 'Underwriting Review';
            UpdatedCP.add(u);
        }        
        
        If(trigger.IsUpdate && A.Status__c == 'Rejected' && trigger.oldmap.get(A.Id).Status__c =='Pending'&& A.type__c == 'Final Loan Approval' && (U.RecordType.DeveloperName =='MT_Credit_Package' || U.RecordType.DeveloperName == 'Credit_Package')){
            U.underwriting_stage__c = 'In Process';       
            UpdatedCP.add(u);
        } 
        
        if(trigger.IsUpdate && a.RecordType.DeveloperName == 'Credit_Committee'){
            if(u.Underwriting_Stage__c == 'In Process'){                   
                u.Final_Preliminary_Approver__c = a.Actual_Approver__c;
                u.Committee_Type__c = a.Current_Approver__c;                         
            }else if(u.Underwriting_Stage__c == 'Final Credit Approved'){                 
                u.Final_Approver__c = a.Actual_Approver__c;
                u.Committee_Type__c = a.Current_Approver__c;                 
            }else{                   
                u.Final_Initial_Approver__c = a.Actual_Approver__c;
                u.Committee_Type__c = a.Current_Approver__c;                   
            }    
            UpdatedCP.add(u);
        }
        
        If(UpdatedCp.size()>0){
            update updatedcp;}
    } 
}

Made some slight modifications to the above code.

Hope thsi helps!

AM
UC InnovationUC Innovation
Please remember to select Best answer if it helped resolve your issue(s).