+ Start a Discussion
tyler_jenningstyler_jennings 

Overcoming Trigger Govenor Limitations for DML operations

I have a simple trigger that creates a tracking record every time the owning group changes (we call this escalation group).  The purpose is to have time tracking similar to how salesforces give you time in status for  service and support cases.  However, the trigger fails with an exception if there are more than 20 records in the new_entries set.   I can work around this, but my solutions  are  seriously kludgy.  Is there a better approach I'm missing?

-Tyler

Here is the code:

trigger recordStatusDates on Case (after update) {

  List<Case_Status_Log__c> new_entries = new List<Case_Status_Log__c>();

  for( integer i = 0; i < Trigger.new.size(); ++i) {

    if (Trigger.old[i].status != Trigger.new[i].status) {
      new_entries.add(Util.buildCaseStatusLog('Status', Trigger.new[i].id, Trigger.old[i].status, Trigger.old[i].status_changed__c, Trigger.new[i].status_changed__c));
    }
    if (Trigger.old[i].escalation_group__c != Trigger.new[i].escalation_group__c) {
      new_entries.add(Util.buildCaseStatusLog('Escalation_group',
          Trigger.new[i].id, Trigger.old[i].escalation_group__c,
          Trigger.old[i].escalation_group_changed__c, Trigger.new[i].escalation_group_changed__c));
    }
  }
  insert new_entries;
}

//Copied from Util
public static Case_Status_Log__c buildCaseStatusLog(String typeOf, Id id, String value, Datetime oldDate, Datetime newDate) {
      Case_Status_Log__c csl = new Case_Status_Log__c();
      csl.case__c = id;
      csl.value__c = value;
      csl.value_type__c = typeOf;
      csl.start_datetime__c = oldDate;
      csl.stop_datetime__c = newDate;
     
      if(oldDate != null && newDate != null) {
        csl.hours_in_state__c = util.hoursBetween(
                                   csl.start_datetime__c,
                                   csl.stop_datetime__c);
      }
      return csl;
  }
mtbclimbermtbclimber
Do you have a trigger on insert of Case_Status_Log__c?

JoeK.ax278JoeK.ax278
Andrew,

There is no trigger on Case_Status_Log__c

We appear to be hitting the limit of 100 on "Number of records processed as a result of the DML statements." In our trigger, we'll insert one new Case_Status_Log__c record inserted per case being changed in the appropriate fashion. We've only seen the exception on batch sizes > 100

I see where it's documented, but we've still been caught off guard to find that the record limit to change using DML is apparently lower than the trigger batch size on bulk processing. I've been imagining many scenarios in which the ratio of records in a trigger batch to records to be changed would be 1:1

Is there a recommendation for handling such cases in Apex Code?

-- JoeK
mtbclimbermtbclimber
I am not sure how that could be the case as the row-based limits scale with the number of rows in the request. So if the trigger above, recordStatusDates, sees 5 cases your row level limit for the request is 5 x 100 (500).

Do you have a testmethod that shows what you can't seem to do with this trigger? Can you post the actual exception or output of a test you are expecting to be passing?

If this is all the code there is then I am fairly vexed as to how you are hitting a governor limit since at worst your trigger will do one insert with 2 x the number of rows in the request from what I can tell.
JoeK.ax278JoeK.ax278
Andrew,

It is a huge relief to hear that the DML row limits scale with the number of rows in the operation! Whew. :-)

The problem we're seeing has happened only a few times, and we cannot yet recreate it. However, since the code seemed OK at first pass, we've so far been guessing that we'd missed or misunderstood something about the governor limits.

We'll triple check for any sporadic bad interactions with other triggers we might have missed and then focus on trying to recreate the problem in a controlled environment.

Thanks for you help!
-- JoeK
Vijay RautVijay Raut

Hi Joe,

I am also facing the Limit on the DML Rows when my query retirves more than 100 records.

But in the Apex document, They have mentioned that DML Rows limit scales with the batch size.

Even for 200 records batch, its giving me limit of 100 Rows only (Contradicting Apex document)?

So if you have found out any workarround for same, then please reply me.

Thanks in advance.

JoeK.ax278JoeK.ax278
Hello, Vijay,

We saw this error sporadically, were not able to recreate the problem, and haven't ever had the chance to create any controlled test cases. Can you post test code that consistently hits the limit? I'd love to give it a try in my own environment.

Think & Enjoy -- JoeK
Vijay RautVijay Raut

Hello Joe,

Thanks for the immediate reply.

Actually, I am testing my Account update / delete trigger, But as i delibarately want to check for more than 100 Records in a batch, I am updating / deleting account using Apex Data Loader with batch of 300 or so records. So that i am always hitting that limit.

Thanks.

GlennAtAppirioGlennAtAppirio
Above on this thread, Andrew (SDFC PM) says:

"...row-based limits scale with the number of rows in the request. So if the trigger above, recordStatusDates, sees 5 cases your row level limit for the request is 5 x 100 (500)."

And later on the thread, Vijay says:

"...in the Apex document, They have mentioned that DML Rows limit scales with the batch size."

However, my testing doesn't seem to indicate this, and I can't find this documented anywhere. 

I am finding an absolute limit of 100 records that can be updated via a DML statement per trigger execution.  My simplified test is the Case trigger below, which creates an arbitrary custom object for each Case being updated:

Code:
trigger DMLTest on Case (after update) { 
  List<Spider__c> newSpiders = new List<Spider__c>();
  for(integer i = 0; i < Trigger.new.size(); i++) {
    newSpiders.add(new Spider__c(name='Trigger Test' + i));
  }
  insert newSpiders;
}

If I then create 101 new Cases and then do a bulk Status change to all of them via the GUI (by checking the boxes next to each in a View), I get this error:

Validation Errors While Saving Record(s) There were custom validation error(s) encountered while saving the affected record(s). The first validation error encountered was "Apex trigger DMLTest caused an unexpected exception, contact your administrator: DMLTest: execution of AfterUpdate caused by: System.Exception: Too many DML rows: 101: Trigger.DMLTest: line 10, column 3".

By Andrew's logic above, my DML limit for updating 101 Cases in bulk should be 100 * 101 = 10,100.  However, it seems the limit is an absolute 100.

The only semi-graceful way I can see around this is to add a check, so at least you can present your own error, instead of allowing an unhandled exception to bubble up:

Code:
trigger DMLTest on Case (after update) { 
  List<Spider__c> newSpiders = new List<Spider__c>();
  for(integer i = 0; i < Trigger.new.size(); i++) {
    if (Trigger.new.size() > Limits.getLimitDMLRows()) {
     Trigger.new[i].addError('You cannot perform a bulk update of more than ' + Limits.getLimitDMLRows() + ' Cases at a time.');
    } else {
      newSpiders.add(new Spider__c(name='Trigger Test' + i));
    }
  }
  insert newSpiders;
}

 I hope I'm overlooking the obvious, but this seems like an unfortunate limitation that could restrict the utility of triggers for creating related objects.

Vijay RautVijay Raut

Hi Glenn,

Apex limit scales up with the Batch size, only when you are performing opeartion using dataloader, excel connector, etc.

But if you are doing updates on SFDC UI, it assume whole batch as a single record (Even you are updating more than one record using mass updates or any other way), hence our Apex limits not scales up. This behavior creates problem.

Thanks

V. R.

mtbclimbermtbclimber
The UI operations are not operating in bulk behind the scenes currently.  The good news is that this has been addressed in the Spring '08 release which is coming very soon.

Sorry for the inconsistency here.

The row-level limits are absolutely intended to scale with the # of rows in the top level operation.

You should also be able to verify this with a simple test method - make sure to utilize Test.startTest() to switch governor limit context from test to trigger so you can affirm the governor limit compliance of your scenario.  Again, the UI is not currently consistent with what you should see in an apex testMethod or as has been pointed out, through an API operation.

Regards,
Andrew
Always ThinkinAlways Thinkin
I noticed in the Apex documentation under the Reference > DML Operations > Insert Operation the Rules and Guideslines stated that:

"You can pass a maximum of 200 sObject records to a single insert method."

Now I'll always trust actual tests like Glenn's over documentation, but I'm wondering whether that statement implies some other action or if it's just plain wrong information in the text.
Vivek ViswanathVivek Viswanath
Hi,

I think I have a similar Issue

trigger Account_Set_Owner_OnLoad_Temp on Account (before update)
{
   
//Select a.RecordTypeId, a.Primary_Group_SM__c, a.Primary_Corporate_SM__c, a.OwnerId From Account a   
    //Primary_Group_SM__c
    List<RecordType> acctRecordtypeId = new List<RecordType>([Select SobjectType, Name, Id From RecordType where SobjectType ='Account' and Name = 'Group' ]);
    private set <Id> acctIds = new set <Id>();
    for ( Account acct : Trigger.new )
    {
        acctIds.add(acct.Id);
        if( acct.RecordTypeId == acctRecordtypeId[0].Id && acct.Primary_Group_SM__c != null )
        {
            acct.OwnerId = acct.Primary_Group_SM__c;
        }
        else
        {
            if(acct.Primary_Corporate_SM__c != null)
            {
                acct.OwnerId = acct.Primary_Corporate_SM__c;
            }
        }
    }
   
    List<Contact> contactList = new List<Contact>([Select Id From Contact where AccountId in :acctIds]);
   
if(!contactList.isempty())
{
     update contactList;
 }

}

This is  a simple code and just updates contacts for the account as I have an on update trigger on contact that just gets the account owner and sets it to the contact owner. However I run into an issue with account more than say 50 contacts at times even less than that. I read through the posts and saw that Spring 08 has a fix for this however I dont see any. I am not talking of 2000 but around 200 contacts here.

Regards

Vivek




Message Edited by Vivek Viswanath on 08-14-2008 10:17 AM