You need to sign in to do that
Don't have an account?
ankush
How to avoild soql query in for loop
Can some one please help me how to avooid the soql query in the last for loop to assign user id. Stuck on this
trigger COITrigger on Panel_Assignment__c (after insert, after update, after delete, before delete) {
//If Panel Assignment is inserted then we have COI inserted along with Status as Active
if(Trigger.isafter && trigger.isInsert){
Set<Id> panelIds = new Set<Id>();
list<COI_Expertise__c> coilist=new list<COI_Expertise__c>();
for ( Panel_Assignment__c panelAssignment : Trigger.new) {
// Store all panel's Id
panelIds.add(panelAssignment.Panel__c);
}
//Below soql query fetches the related Research Applications
//Also based on research applications the COI records are created for each reviewer assigned to the panel.
list<Research_Application__c> r=[select id,Technical_Abstract__c from Research_Application__c where Panel__c=:panelIds];
for(Research_Application__c ra:r){
for(Panel_Assignment__c panelAssignment : Trigger.new){
COI_Expertise__c c=new COI_Expertise__c();
c.Research_Application__c= ra.Id;
c.Technical_Abstract__c=ra.Technical_Abstract__c;
c.Active__c=true;
c.Reviewer_Name__c = panelAssignment.Reviewer_Name__c;
user u=[select id,firstname,lastname from user where contactID=:c.Reviewer_Name__c];
c.OwnerId=u.Id;
coilist.add(c);
}
}
insert coilist;
}
trigger COITrigger on Panel_Assignment__c (after insert, after update, after delete, before delete) {
//If Panel Assignment is inserted then we have COI inserted along with Status as Active
if(Trigger.isafter && trigger.isInsert){
Set<Id> panelIds = new Set<Id>();
list<COI_Expertise__c> coilist=new list<COI_Expertise__c>();
for ( Panel_Assignment__c panelAssignment : Trigger.new) {
// Store all panel's Id
panelIds.add(panelAssignment.Panel__c);
}
//Below soql query fetches the related Research Applications
//Also based on research applications the COI records are created for each reviewer assigned to the panel.
list<Research_Application__c> r=[select id,Technical_Abstract__c from Research_Application__c where Panel__c=:panelIds];
for(Research_Application__c ra:r){
for(Panel_Assignment__c panelAssignment : Trigger.new){
COI_Expertise__c c=new COI_Expertise__c();
c.Research_Application__c= ra.Id;
c.Technical_Abstract__c=ra.Technical_Abstract__c;
c.Active__c=true;
c.Reviewer_Name__c = panelAssignment.Reviewer_Name__c;
user u=[select id,firstname,lastname from user where contactID=:c.Reviewer_Name__c];
c.OwnerId=u.Id;
coilist.add(c);
}
}
insert coilist;
}
Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
All Answers
Try the code below:
Regards.
Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
Since there is a SOQL query within the for loop that iterates across all the Account objects that initiated this trigger, a query will be executed for each Account. And by default, a trigger gets a maximum of 20 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 20 Account records, the governor limit will throw a runtime exception.
Keep in mind that the same is true for DML operations as well. Meaning, try to avoid having DML operations (insert, update, delete) inside a for Loop since that will also unnecessarily exceed the governor limit pertaining to DML operations. In this example, a trigger only allows 20 DML operations so a governor limit will be exceeded after the 20th Contact is updated.
Here is the optimal way to 'bulkify' the code to efficiently query the Contacts in a single query and only perform a single update DML operation.
Now if this trigger is invoked with a single Account record or up to 200 Account records, only 1 SOQL query and 1 update statement is executed.
Source:Best Pratice (https://developer.salesforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops) to write trigger
Thanks a lot for the guidance but the code is not compiling as I am getting the error on line 11 as
Method does not exist or incorrect signature: [MAP<Id,Id>].add(Id, Id)
Infact Reviewer_Name__c is also ID field so I changed the Map<Id, String> to Map<Id, Id> . Can you guide me here. Stuck on this.
Please correct the following line:
Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.