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
PossiblePossible 

Bulkyfied Trigger!!!

Hi ,

 

I am trying to insert 200 records at a time in TestMethod for Trigger but it fails saying :-

 

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ConnectCompiere.ScheduleWorkFlow: execution of BeforeInsert caused by: System.Exception: ConnectCompiere:Too many SOQL queries: 21
Trigger.ConnectCompiere.ScheduleWorkFlow.getCompiereSetUpId.....

 

My Trigger :-

 

 

trigger ScheduleWorkFlow on Workflow__c (before insert, before update) {

//  This Apex trigger is designed to fire when the batch workflow scheduler 
//  checks the Trigger Batch Run checkbox or when changes are made to the Batch Run
//  record manually.
    Set<Id> wkIds = new Set<Id>();  
    for (Workflow__c batchRun : Trigger.new) {
    
         String setUpId = getCompiereSetUpId(batchRun);
    // Apex code.......... 
}public String getCompiereSetUpId(Workflow__c batchRunOld){
       //System.debug('in call getCompiereSetUpId');
        List<Rule__c> ruleList = [select Compiere_Setup__c from Rule__c where Workflow_Rule__c =: batchRunOld.id];
        String setUpId = '';
        for(Rule__c rule:ruleList){
            setUpId = rule.Compiere_Setup__c;
        }
        return setUpId;
    }}

 

 

TestMethod :-

 

 public static testMethod void TriggerTest(){
       List<WorkFlow__c> batchS = new List<WorkFlow__c>();
        for(Integer x=0; x<200;x++){
            WorkFlow__c batch = new WorkFlow__c();
            batch.Username__c = 'XXXXXX';           
            batchS.add(batch);
        }
       
        Test.startTest();
        insert batchS;
        Test.stopTest();
        
    }

 

its running ok for 20 insert , which i can understand why my problem is happening..Basically its bcoz of the SOQL limits , but can please some gimme some idea to finetune my code in trigger..

 

 

Thanks...

 

 

 

 

Richie DRichie D

What I have done in the past is create a list of the Ids you need (in your trigger.new) and do a statement using the in SOQL keyword. You maybe able to use trigger.new.keyset() so you have something like [select Compiere_Setup__c from Rule__c where Workflow_Rule__c in :trigger.new.keySet()];

 

If you do this you can crate a list of all Rule__c items you'll need. Then just loop around the Workflow__c items in trigger.new, create a list of of Rule__c items that match (rule.Workflow_Rule__c =: currentWorkflow__c) and use that in your loop.

 

R.
jeffdonthemicjeffdonthemic

Have a look at my blog post entitled, "Writing Bulk Triggers for Salesforce.com". There is methodology, code and unit tests for you to look at.

 

Here's the link: http://blog.jeffdouglas.com/2009/04/20/writing-bulk-triggers-for-salesforce/ 

 

HTH

 

Jeff Douglas

Informa Plc

http://blog.jeffdouglas.com

MycodexMycodex

Hi Jeff, I'm an avid reader of your blog. Its remarkable how much the platform can do. I'm trying to use your tutorial to bulkify one of my event triggers and I'm getting stuck. Currently, my trigger looks like this:

 

 

trigger SetEventContactFields on Event (before insert, before update) {
    for (Event ev : Trigger.new) {
        String cid = ev.WhoID;
        List<Contact> Contacts = [SELECT AccountID, MailingCity, MailingState FROM Contact WHERE Id = :cid];
        List<Account> Accounts = [SELECT Name FROM Account WHERE Id = :contacts.get(0).AccountId];
        ev.Address__c = contacts.get(0).MailingCity + ', ' + contacts.get(0).MailingState;
        ev.Broker_Name__c = Accounts.get(0).Name;
    }
}
 All it is basically doing is setting two fields Address__c and Broker_Name__c with the contact's address and account name. I need to reference both objects because the contact address can be different than the account address. Also, the account name does not seem to exist in the contact object.
 
In my initial attempt, I can get the contact address, but then I'm stuck on how to reference the account for that contact. 
 
 
trigger SetEventContactFields on Event (before insert, before update) {
    Set<Id> cid = new Set<Id>();
    for (Event ev : Trigger.new)
        cid.add(ev.WhoID);
    
    Map<Id, Contact> contacts = new Map<Id, Contact>([Select MailingCity, MailingState From Contact WHERE Id in :cid]);
    
    for (Event ev : Trigger.new)
        ev.Address__c = contacts.get(ev.WhoID).MailingCity + ', ' + contacts.get(ev.WhoID).MailingState;

}
 
I feel like everything I do will de-bulkify this rather quickly if I reference :contacts.get(0).AccountID in another map underneath the contact map. Maybe there is another way I can do this completely? Any help would be appreciated! Thanks.
 
 


MycodexMycodex
I hate when I do this. I played around with the trigger some more and managed to find the relationship so I could combine the query I used for contacts and accounts. Here is what I have created and it seems to work. Now to create the test code and see if its really bulkified.
 
 
trigger SetEventContactFields on Event (before insert, before update) {
    Set<Id> cid = new Set<Id>();
    for (Event ev : Trigger.new) {
        if(ev.WhoID != null){
            cid.add(ev.WhoID);
        }
    }
    
    Map<Id, Contact> contacts = new Map<Id, Contact>([Select c.MailingCity, c.MailingState, c.Account.Name From Contact c WHERE Id in :cid]);
        
    for (Event ev : Trigger.new) {
        ev.Address__c = contacts.get(ev.WhoID).MailingCity + ', ' + contacts.get(ev.WhoID).MailingState;
        ev.Broker_Name__c = contacts.get(ev.WhoID).Account.Name;
    }
}
 
 
 


jeffdonthemicjeffdonthemic
Great! Glad you got it worked out. Have fun testing.
 
 
Vote for my Idea: Enhanced Apex Testing Functionality (JUnit) Link 
 
 
Jeff Douglas
Informa Plc
http://blog.jeffdouglas.com 


PossiblePossible
Ok i somehow rectified my triggers and they now work for bulk records insert/update for testing..Many thanks to u guyz and the blog from jeff is really great...m looking foward to use em in my next assignments..
I am using the triggers only for scheduling so m really not making any thing special in trigger so i just put some limits in triggers statements..Now i have another problem which happens to be in test method...
 
 
 public static testMethod List<WorkFlow__c> insertWorkFlowTest(){
       List<WorkFlow__c> batchS = new List<WorkFlow__c>();
        for(Integer x=0; x<200;x++){
            WorkFlow__c batch = new WorkFlow__c();
            batch.Username__c = 'XXXXX';           
            batch.Schedule__c = true;
            batch.Frequency__c = '1';
            batch.Script_Type__c = 'Hours';
            batch.Next_Run_Date__c = System.now();
            batchS.add(batch);
        }        
        insert batchS;
        return batchS;        
    }   
    
     public static testMethod List<Rule__c> randomTriggerAssociationTest(){
       List<WorkFlow__c> batchList = insertWorkFlowTest();       
       BatchScript__c batchScript = createBatchObj();
       List<Rule__c> ruleList = new List<Rule__c>();
       for(Workflow__c batch:batchList){     
            Rule__c rule = new Rule__c(
            Compiere_Setup__c = batchScript.id,
            Workflow_Rule__c = batch.id);
            ruleList.add(rule);    
        }
        insert ruleList;
        return ruleList;
      }  
      
      public static testMethod void randomTriggerUpdateTest(){
       List<WorkFlow__c> batchList = insertWorkFlowTest(); 
       List<Rule__c> ruleList = randomTriggerAssociationTest();
       List<WorkFlow__c> batchUpdateList = new List<WorkFlow__c>(); 
       for(Workflow__c batch:batchList){
           batch.Running__c = true;
           batchUpdateList.add(batch);
       }
       Test.startTest();                
       update batchUpdateList;
       Test.stopTest();     
      }   
 
when i keep my testrecord to 200 its show exception :--
System.Exception: ConnectCompiere:Too many DML rows: 604
Class.ConnectCompiere.TestTriggerClass.randomTriggerAssociationTest: line 411, column 9
Class.ConnectCompiere.TestTriggerClass.randomTriggerUpdateTest: line 418, column 33
 
But it work fine for 100 rows...
So please suggest me if i am making anything wrong...And i dnt think my trigger can be executed 100 times at once ..it can be maximum excuted only once/twice at times and it will be by time based workflow rules alwayz..so do i have to really give 200 records or more in test methods.. 
 
Thanks .... 


jeffdonthemicjeffdonthemic
There are no requirements to test your trigger with 200 records if you don't want it to run for bulk processing. You might want to add the following code to your trigger so that it only runs for something line one or two records in the trigger:
 
 
if (Trigger.new.size() < SOME_MAX_NUMBER) {

   // ONLY PROCESS YOUR RECORDS

}
 
HTH
 
Jeff Douglas
Informa Plc
http://blog.jeffdouglas.com 
 


PossiblePossible
awesome...so is that restriction gonna hamper my scheduling ever or is it vulnerable to any security review process??
1) I meant after giving that restriction i hope still mytrigger is bulkified??
2) And the (Tigger.new.size > restriction) this is for a single run..right??
 
Thanks.... 
Message Edited by Possible on  05-09-2009 10:51 PM


jeffdonthemicjeffdonthemic
We have a number of triggers for Contacts. Some we want to only fire when a user manually saves a record (no bulk processing) and some to fire whenever they are created/updated (manual transaction, imports, integrations with external sources, etc).  If we only want them fire when when a user manually saves a record then we'll set trigger.new.size() = 1. For all other triggers we write them for bulk processing.
 
HTH
 
Jeff Douglas
Informa Plc
http://blog.jeffdouglas.com