+ Start a Discussion
Pat McQueenPat McQueen 

Bulkifying a Trigger Question ... on OpportunityLineItem

Hello,

 

I am creating a trigger on OpportunityLineItem which will create a revenue schedule.  I am not using the out of box revenue scheduling as it does not create a schedule that fits my business needs. I created the trigger below which works fine, but I can't seem to make it work in bulk.  I put the test methods below as well.  The test methods work with a bulk insert of 5 rows but anything more gives me an exception. (when bi is greater than 5 in the test method it fails)  Any ideas why I can't make this more bulk enabled?  Thanks - Pat

 

Exception for larger bulk inserts

 

20090413174655.805:Class.ProductScheduleTriggerTest.myUnitTest: line 105, column 9: DML Operation executed in 240 ms System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ProductScheduleTriggerBulk: execution of AfterInsert caused by: System.Exception: Too many DML rows: 156 Trigger.ProductScheduleTriggerBulk: line 100, column 9 Class.ProductScheduleTriggerTest.myUnitTest: line 105, column 9

 

 

 

Trigger

 

// // This trigger requires two custom fields and a validation rule. // trigger ProductScheduleTriggerBulk on OpportunityLineItem (after insert) { // // Get prepped by retrieving the base information needed // Date currentDate; Decimal numberPayments; Decimal paymentAmount; Decimal totalPaid; List<OpportunityLineItemSchedule> newScheduleObjects = new List<OpportunityLineItemSchedule>(); // For every OpportunityLineItem record, add its associated pricebook entry // to a set so there are no duplicates. Set<Id> pbeIds = new Set<Id>(); for (OpportunityLineItem oli : Trigger.new) pbeIds.add(oli.pricebookentryid); // Query the PricebookEntries for their associated info and place the results // in a map. Map<Id, PricebookEntry> entries = new Map<Id, PricebookEntry>( [select product2.Auto_Schedule_Revenue__c, product2.Num_Payments__c from pricebookentry where id in :pbeIds]); // For every OpportunityLineItem record, add its associated oppty // to a set so there are no duplicates. Set<Id> opptyIds = new Set<Id>(); for (OpportunityLineItem oli : Trigger.new) opptyIds.add(oli.OpportunityId); // Query the Opportunities for their associated info and place the results // in a map. Map<Id, Opportunity> Opptys = new Map<Id, Opportunity>( [select Id, CloseDate from Opportunity where id in :opptyIds]); // Iterate through the changes for (OpportunityLineItem item : trigger.new) { if(entries.get(item.pricebookEntryID).product2.Auto_Schedule_Revenue__c == true) { //OK, we have an item that needs to be Auto Scheduled //Calculate the payment amount paymentAmount = item.TotalPrice; numberPayments = (entries.get(item.pricebookEntryId).product2.Num_Payments__c); paymentAmount = paymentAmount.divide(numberPayments,2); //System.debug('* * * * Base Monthly Amount = ' + paymentAmount); // Determine which date to use as the start date. if (item.ServiceDate == NULL) { currentDate = Opptys.get(item.OpportunityId).CloseDate; } else { currentDate = item.ServiceDate; } totalPaid = 0; // Loop though the payments for (Integer i = 1;i < numberPayments;i++) { OpportunityLineItemSchedule s = new OpportunityLineItemSchedule(); s.Revenue = paymentAmount; s.ScheduleDate = currentDate; s.OpportunityLineItemId = item.id; s.Type = 'Revenue'; newScheduleObjects.add(s); totalPaid = totalPaid + paymentAmount; System.debug('********Date ' + currentDate + ' Amount ' + paymentAmount); currentDate = currentDate.addMonths(1); } //Now Calulate the last payment! paymentAmount = item.TotalPrice - totalPaid; OpportunityLineItemSchedule s = new OpportunityLineItemSchedule(); s.Revenue = paymentAmount; s.ScheduleDate = currentDate; s.OpportunityLineItemId = item.id; s.Type = 'Revenue'; newScheduleObjects.add(s); System.debug('********** LAST PAYMENT **********'); System.debug('********Date ' + currentDate + ' Amount ' + paymentAmount); } // End If Auto_Schedule_Revenue } // End For OpportunityLineItem try { insert(newScheduleObjects); } catch (System.DmlException e) { for (Integer ei = 0; ei < e.getNumDml(); ei++) { System.debug(e.getDmlMessage(ei)); } // // There shuld be something here to alert the user what failed! // } // End Catch } // End Trigger ProducScheduleTriggerBulk

 

 

 

Test Method

 

/** * This class tests the trigger named ProductScheduleTrigger. */ @isTest private class ProductScheduleTriggerTest { static testMethod void myUnitTest() { //Data Prep //Create Account, Opportunity, Product, etc. Account acct1 = new Account(name='test Account One1'); insert acct1; //Create Opportunity on Account Opportunity Oppty1 = new Opportunity(name='test Oppty One1'); Oppty1.StageName = 'Test'; Oppty1.CloseDate = Date.today(); insert Oppty1; // Create Products Product2 testprod1 = new Product2 (name='test product one1'); testprod1.productcode = 'test pd code1one'; testprod1.Num_Payments__c = 0; testprod1.CanUseRevenueSchedule = True; testprod1.Auto_Schedule_Revenue__c = False; insert testprod1; Product2 testprod2 = new Product2 (name='test product two2'); testprod2.productcode = 'test pd code2two'; testprod2.Num_Payments__c = 12; testprod2.CanUseRevenueSchedule = True; testprod2.Auto_Schedule_Revenue__c = True; insert testprod2; // Ger Pricebook Pricebook2 testpb = [select id from Pricebook2 where IsStandard = true]; // Add to pricebook PricebookEntry testpbe1 = new PricebookEntry (); testpbe1.pricebook2id = testpb.id; testpbe1.product2id = testprod1.id; testpbe1.IsActive = True; testpbe1.UnitPrice = 250; testpbe1.UseStandardPrice = false; insert testpbe1; PricebookEntry testpbe2 = new PricebookEntry (); testpbe2.pricebook2id = testpb.id; testpbe2.product2id = testprod2.id; testpbe2.IsActive = True; testpbe2.UnitPrice = 250; testpbe2.UseStandardPrice = false; insert testpbe2; //And now you want execute the startTest method to set the context //of the following apex methods as separate from the previous data //preparation or DML statements. test.starttest(); // add the line item which should call the trigger // with this line item it should fail out quickly // As Auto Schedule is false OpportunityLineItem oli1 = new OpportunityLineItem(); oli1.Quantity = 1; oli1.TotalPrice = 1; oli1.PricebookEntryId = testpbe1.id; oli1.OpportunityId = oppty1.id; insert oli1; System.assertEquals(0, [select count() from OpportunityLineItemSchedule where OpportunityLineItemId = :oli1.id]); // add the line item which should call the trigger // Auto Schedule is true so it should build the schedule. OpportunityLineItem oli2 = new OpportunityLineItem(); oli2.Quantity = 1; oli2.TotalPrice = 1; oli2.PricebookEntryId = testpbe2.id; oli2.OpportunityId = oppty1.id; insert oli2; System.assertEquals(12, [select count() from OpportunityLineItemSchedule where OpportunityLineItemId = :oli2.id]); // add the line item which should call the trigger // Auto Schedule is true so it should build the schedule. // This uses a date on OpptyLineItem to try another code path OpportunityLineItem oli3 = new OpportunityLineItem(); oli3.Quantity = 1; oli3.TotalPrice = 1; oli3.ServiceDate = date.today(); oli3.PriceBookEntryId = testpbe2.id; oli3.OpportunityId = oppty1.id; insert oli3; System.assertEquals(12, [select count() from OpportunityLineItemSchedule where OpportunityLineItemId = :oli3.id]); // Adding multiple line items in bulk which should call the trigger. // Auto Schedule is true so it should build the schedule. // This trigger does not work in large bulk amounts as it adds multiple rows. List<OpportunityLineItem> bulkoli = new List<OpportunityLineItem>(); for(integer bi=0; bi<5; bi++) { bulkoli.add( new OpportunityLineItem(Quantity = 1, TotalPrice = 1, PriceBookEntryId = testpbe2.id, OpportunityId = Oppty1.id) ); } insert bulkoli; System.assertEquals(12*5, [select count() from OpportunityLineItemSchedule where OpportunityLineItemId in :bulkoli]); test.stoptest(); } }

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Ron HessRon Hess

Hi Pat,

 Your code looks fine, i was able to build a test that threw 300 new line items at it and it worked correctly.

 

the only thing i changed was to break out the loop into it's own test method so that the startTest() could begin right before the insert of the lines.

 

otherwise you are using startTest() to isolate test code from setup code, but then running several tests between startTest() and stopTest().

 

here is the test method that i came up with :

 

 

/**
* This class tests the trigger named ProductScheduleTrigger.
*/
@isTest
private class ProductScheduleTriggerTest {

static testMethod void myUnitTest() {

//Data Prep
//Create Account, Opportunity, Product, etc.
Account acct1 = new Account(name='test Account One1');
insert acct1;
//Create Opportunity on Account
Opportunity Oppty1 = new Opportunity(name='test Oppty One1');
Oppty1.StageName = 'Test';
Oppty1.CloseDate = Date.today();
insert Oppty1;

// Create Products
Product2 testprod1 = new Product2 (name='test product one1');
testprod1.productcode = 'test pd code1one';
testprod1.Num_Payments__c = 0;
testprod1.CanUseRevenueSchedule = True;
testprod1.Auto_Schedule_Revenue__c = False;
insert testprod1;
Product2 testprod2 = new Product2 (name='test product two2');
testprod2.productcode = 'test pd code2two';
testprod2.Num_Payments__c = 12;
testprod2.CanUseRevenueSchedule = True;
testprod2.Auto_Schedule_Revenue__c = True;
insert testprod2;
// Ger Pricebook
Pricebook2 testpb = [select id from Pricebook2 where IsStandard = true];
// Add to pricebook
PricebookEntry testpbe1 = new PricebookEntry ();
testpbe1.pricebook2id = testpb.id;
testpbe1.product2id = testprod1.id;
testpbe1.IsActive = True;
testpbe1.UnitPrice = 250;
testpbe1.UseStandardPrice = false;
insert testpbe1;
PricebookEntry testpbe2 = new PricebookEntry ();
testpbe2.pricebook2id = testpb.id;
testpbe2.product2id = testprod2.id;
testpbe2.IsActive = True;
testpbe2.UnitPrice = 250;
testpbe2.UseStandardPrice = false;
insert testpbe2;


//And now you want execute the startTest method to set the context
//of the following apex methods as separate from the previous data
//preparation or DML statements.


// Adding multiple line items in bulk which should call the trigger.
// Auto Schedule is true so it should build the schedule.
// This trigger does not work in large bulk amounts as it adds multiple rows.

test.starttest();
List<OpportunityLineItem> bulkoli = new List<OpportunityLineItem>();
integer todo = 300;
for(integer bi=0; bi<todo; bi++) {
bulkoli.add( new OpportunityLineItem(Quantity = 1, TotalPrice = 1,
PriceBookEntryId = testpbe2.id, OpportunityId = Oppty1.id) );
}

insert bulkoli;


System.assertEquals(12*todo, [select count() from OpportunityLineItemSchedule
where OpportunityLineItemId in :bulkoli]);

test.stoptest();
}
}

 

 the results sho wthat the code is well bulked:

 

Cumulative resource usage:

Resource usage for namespace: (default)
Number of SOQL queries: 1 out of 100
Number of query rows: 1 out of 500
Number of SOSL queries: 0 out of 20
Number of DML statements: 7 out of 100
Number of DML rows: 306 out of 500 ******* CLOSE TO LIMIT
Number of script statements: 338 out of 200000
Maximum heap size: 0 out of 500000
Number of callouts: 0 out of 10
Number of Email Invocations: 0 out of 10

...

 

 

I also see this message in the debug log: 

 

20090414004705.860:Class.ProductScheduleTriggerTest.myUnitTest: line 68, column 9:

Changing testing limit context based on DML operation of size: 300

 

 

 

So, when testing the context limit is raised based on the number of records i inserted (300)


 

I suggest you break your test method into several, one method to setup data, then one for each test you will perform, that way you can use starttest() and stoptest() around each portion of code under test. 

It really only impacts the last one but it will keep the tests in their own box so to speak.

 

 

 

Message Edited by Ron Hess on 04-13-2009 06:24 PM