function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
TheReportDoctorTheReportDoctor 

Update Triggers Going Dizzy

I am running out of ideas.

 

I have two objects using update triggers.

 

The first one updates items on the second one.  The second one fires off it's update trigger causing too many DML's.

 

When the first object fires off the update trigger I want to stop the second table from doing some of the functions in it's update trigger.

 

Does anyone have the million dollar answer?

 

Here is the code:

 

trigger OpportunityAfterUpdateInsert on Opportunity (after update, after insert) { String status; Integer record = 0; Integer countSteps = 0; for (Opportunity opp: Trigger.new) { String opportunityId = opp.Id; String productId = opp.Product__c; if ((opp.Stage_Of_Install__c =='7 - Pipeline' || opp.Stage_Of_Install__c =='10 - Disconnected' ) && opp.AccountId != null) { List<Provisioning__c> completedTask = [SELECT ID FROM Provisioning__c WHERE Opportunity__c = :opportunityId AND status__c = '3 - Completed']; if (!completedTask.isEmpty()) { opp.AddError('>>>You can not change the product if there are any completed tasks.'); } if (Trigger.isUpdate) { if (Trigger.new[record].Product__c != Trigger.old[record].Product__c) { opp.AddError('You cannot change products on an opportunity in 7 - Pipeline'); } String oldProductId = Trigger.old[record].Product__c; countSteps = [SELECT count() FROM Provisioning__c WHERE Opportunity__c = :opportunityId AND Product__c = :oldProductId ]; } if (countSteps == 0) { List<Provisioning__c> provisioning = [SELECT Account__c FROM Provisioning__c WHERE Opportunity__c = :opportunityId]; try { delete provisioning ; } catch (dmlException de) { for (Integer i = 0; i < de.getNumDml(); i++) { opp.AddError(de.getDmlMessage(i)); } } if (opp.Stage_Of_Install__c =='7 - Pipeline') { List<ProductProcesses__c> provisioningSteps= [SELECT Department__c , Interval__c , PredecessorStep__c , ProcessStep__c , SuccessorStep__c , WorkSteps__c FROM ProductProcesses__c WHERE Product__c = :productId ORDER BY ProcessStep__c]; if (!provisioningSteps.isEmpty()) { for (ProductProcesses__c productStep: provisioningSteps) { if (productStep.PredecessorStep__c == 0 ) { status = '1 - Active Step'; } else { status = '2 - Pending'; } provisioning.add(new Provisioning__c ( Account__c = opp.AccountId , Department__c = productStep.Department__c , Interval__c = productStep.Interval__c , Opportunity__c = opp.Id , PredecessorStep__c = productStep.PredecessorStep__c , ProcessStep__c = productStep.ProcessStep__c , Product__c = opp.Product__c , Status__c = status , SuccessorStep__c = productStep.SuccessorStep__c , WorkSteps__c = productStep.WorkSteps__c)); } try { insert provisioning; } catch (dmlException de) { for (Integer i = 0; i < de.getNumDml(); i++) { opp.AddError(de.getDmlMessage(i)); } } } } else { List<ProductDisconnect__c> disconnectSteps = [SELECT Department__c , Interval__c , PredecessorStep__c , ProcessStep__c , SuccessorStep__c , WorkSteps__c FROM ProductDisconnect__c WHERE Product__c = :productId ORDER BY ProcessStep__c]; if (!disconnectSteps.isEmpty()) { for (ProductDisconnect__c productStep: disconnectSteps ) { if (productStep.PredecessorStep__c == 0 ) { status = '1 - Active Step'; } else { status = '2 - Pending'; } provisioning.add(new Provisioning__c ( Account__c = opp.AccountId , Department__c = productStep.Department__c , Interval__c = productStep.Interval__c , Opportunity__c = opp.Id , PredecessorStep__c = productStep.PredecessorStep__c , ProcessStep__c = productStep.ProcessStep__c , Product__c = opp.Product__c , Status__c = status , SuccessorStep__c = productStep.SuccessorStep__c , WorkSteps__c = productStep.WorkSteps__c)); } try { insert provisioning; } catch (dmlException de) { for (Integer i = 0; i < de.getNumDml(); i++) { opp.AddError(de.getDmlMessage(i)); } } } } } provision.updateEstDates([SELECT Id , Opportunity__c FROM provisioning__c WHERE Opportunity__c = : opportunityId AND predecessorStep__c = 0 LIMIT 1]); } record ++; } }

 

 

trigger ProvisioningAfterUpdate on Provisioning__c (after update) { for(Integer l = 0; l < Trigger.new.size(); l++) { String currentId = Trigger.new[l].Id; String opportunityID = Trigger.new[l].Opportunity__c; Double currentPredecessorStep = Trigger.new[l].PredecessorStep__c; Double currentProcessStep = Trigger.new[l].ProcessStep__c; Double currentSuccessorStep = Trigger.new[l].SuccessorStep__c; if (Trigger.new[l].status__c == '1 - Active Step') { if (Trigger.old[l].status__c == '3 - Completed' && Trigger.new[l].SuccessorStep__c != 99 ) { // Uncomplete Integer parallelSteps = [SELECT count() FROM Provisioning__c WHERE Opportunity__c = :opportunityID AND processStep__c != :currentProcessStep AND successorStep__c =: currentSuccessorStep AND status__c IN ('1 - Active Step')]; Integer completedSteps = [SELECT count() FROM Provisioning__c WHERE Opportunity__c = :opportunityID AND (processStep__c = :currentSuccessorStep OR predecessorStep__c = :currentProcessStep) AND status__c = '3 - Completed']; if (completedSteps != 0 ) { //Return Completed date Provisioning__c editDate = [SELECT Completed_Date__c FROM Provisioning__c WHERE Id = :currentId]; editDate.Completed_Date__c = Trigger.old[l].Completed_Date__c; try { update editDate; } catch (dmlException de) { for(Integer err = 0; err < de.getNumDml(); err++) { Trigger.new[l].AddError(de.getDmlMessage(err)); } } Trigger.new[l].AddError('Cannot revert the completed step when successor is completed.'); } else { if (parallelSteps == 0 && completedSteps == 0) { List<Provisioning__c> Steps = [SELECT status__c FROM Provisioning__c WHERE Opportunity__c = :opportunityID AND (processStep__c = :currentSuccessorStep OR predecessorStep__c = :currentProcessStep) AND status__c = '1 - Active Step']; if (Steps.isEmpty()) { // Return Completed date Provisioning__c editDate = [SELECT Completed_Date__c FROM Provisioning__c WHERE Id = :currentId]; editDate.Completed_Date__c = Trigger.old[l].Completed_Date__c; try { update editDate; } catch (dmlException de) { for(Integer err = 0; err < de.getNumDml(); err++) { Trigger.new[l].AddError(de.getDmlMessage(err)); } } Trigger.new[l].AddError('Broken uncompleting links in the provisioning plan.'); } else { for(Provisioning__c Step: Steps) { Step.status__c = '2 - Pending'; } try { update Steps; } catch (dmlException de){ for (Integer err = 0; err < de.getNumDml(); err++) { Trigger.new[l].AddError(de.getDmlMessage(err)); } } } } } } } else if (Trigger.new[l].status__c == '3 - Completed') { if (Trigger.old[l].status__c != '1 - Active Step') { Trigger.new[l].AddError('Only 1 - Active Steps can be changed to 3 - Completed. (After Update)'); } else if (Trigger.new[l].SuccessorStep__c != 99) { // Review and update next active steps Integer parallelSteps = [SELECT count() FROM Provisioning__c WHERE Opportunity__c = :opportunityID AND successorStep__c =: currentSuccessorStep AND status__c != '3 - Completed']; if (parallelSteps == 0 ) { List<Provisioning__c> Steps = [SELECT status__c FROM Provisioning__c WHERE Opportunity__c = :opportunityID AND (processStep__c = :currentSuccessorStep OR predecessorStep__c = :currentProcessStep)]; if (Steps.isEmpty()) { Trigger.new[l].AddError('Broken completing links in the provisioning plan. '); } else { for (Provisioning__c Step: Steps) { Step.status__c = '1 - Active Step'; } try { update Steps; } catch (dmlException de){ for (Integer err = 0; err < de.getNumDml(); err++) { Trigger.new[l].AddError(de.getDmlMessage(err)); } } } } } } else if (Trigger.new[l].status__c == '4 - Delete Step') { if (Trigger.old[l].status__c != '2 - Pending') { Trigger.new[l].AddError('Only 2 - Pending steps can be changed to 4 - Delete Step.'); } else { // Just delete the step and the delete trigger will take care of the rest List<Provisioning__c> deleteRecord = [SELECT ID FROM Provisioning__c WHERE Id =:currentId ]; try { delete deleteRecord; } catch (dmlException de) { for (Integer err = 0; err < de.getNumDml(); err++) { Trigger.new[l].AddError(de.getDmlMessage(err)); } } } } else if (Trigger.new[l].status__c == '2 - Pending') { } else { Trigger.new[l].AddError('Invalid Status selected (After Update).'); } if (Trigger.new[l].status__c != '4 - Delete Step' && Trigger.old[l].Est_Start_Date__c != Trigger.new[l].Est_Start_Date__c ) { provision.updateEstDates([SELECT Id , Opportunity__c FROM provisioning__c WHERE Id =:currentId]); } } }

 

 

 

public class provision { public static void updateEstDates (provisioning__c currentRecord ){ String opportunityID = currentRecord.Opportunity__c; String provisioningId = currentRecord.Id; Date maxLastEndDate; List<provisioning__c> steps = [SELECT processStep__c , predecessorStep__c , successorStep__c , Est_Start_Date__c , Est_End_Date__c , Interval__c FROM provisioning__c WHERE Opportunity__c = :opportunityID ORDER BY predecessorStep__c]; for( provisioning__c eachStep: steps) { // track the max last date from those who share my successor maxLastEndDate= null; if (eachStep.predecessorStep__c == 0 && maxLastEndDate == null) { maxLastEndDate = system.today(); } else { for(provisioning__c previousStep: steps) { if (previousStep.processStep__c == eachStep.predecessorStep__c || previousStep.successorStep__c == eachStep.processStep__c) { if (maxLastEndDate < previousStep.Est_Start_Date__c.addDays(previousStep.Interval__c.intValue())|| maxLastEndDate == null) { maxLastEndDate= previousStep.Est_Start_Date__c.addDays(previousStep.Interval__c.intValue()); } } } } if (eachStep.ID != provisioningId || (eachStep.ID == provisioningId && eachStep.Est_Start_Date__c == null)) { if (maxLastEndDate != null) { eachStep.Est_Start_Date__c = maxLastEndDate; } else { eachStep.Est_Start_Date__c = system.today(); } // User's should not change Est_End_Dates. Update the Intervals only!! if (eachStep.Interval__c == null) { eachStep.Interval__c = 0 ; } } } try { update steps; } catch (dmlException de) { for (Integer i = 0; i < de.getNumDml(); i++) { Trigger.new[0].AddError(de.getDmlMessage(i)); } } } }

 

BritishBoyinDCBritishBoyinDC

I think your code needs some re-working...

 

Move away from having the select statements in the Loops, and instead use Sub queries with Maps. Not sure how Opportunity and Provisioning are related but I would think you can do something like [Select Id, Name, (SELECT ID FROM Provisioning__c WHERE status__c = '3 - Completed') from Opportunity]

 

If you put that in a map, with the Opportunity Id as the key, you can then access the provisioning data for each Opportunity in the loop instead of having to query for them each time by setting the results of the sub query into a list for that Opportunity/Provisioning data set. Although you can't execute a count() for items in that list, you can run some simple script to work that out, which is far more efficient that executing a SOQL statement for each Opportunity in the trigger.

 

By doing this, you can then add your requests for updates and deletions for Provisionings to a single list and execute a single DML statement at the end of the loop, which in turn should help in not hitting the DML limits.

 

It's a different way of thinking about it, and quite tricky to get the hang of at first if you haven't used Maps before, but do a search of the discussions boards - I've posted some other solutions based on the same principle...

 

 

TheReportDoctorTheReportDoctor
I am not sure what the difference is between maps and list.  They are both vars.  I have done what can be done to keep the SOQL and DML out of the loops.  That has been worked over a million times.  The part that is causing us trouble is the call to the class provisioning.updateEstDates.  It is called from both update triggers.  When it is called from the Opportunity I don't want it to fire Provisioning's update trigger.  There is an update DML for Provisioning in the Opportunity update trigger.  That is where the issue exist.  The rest of the code has been tested to death and works fine.
BritishBoyinDCBritishBoyinDC

Fair enough...

 

So I would just create a class that stores a Boolean which you can set in Opportunity trigger, and the check in the second class when it is fired..if set to true, don't fire the updates the...