Mt Lindsay

Tuesday, January 15, 2019

Mnemonic for Salesforce Save Order of Execution

I came up with this mnemonic while studying for the Salesforce Platform Developer 1 Exam.

The steps are grouped together and based on this document.

Salesforce Apex Developer Guide- Triggers and Order of Execution

Below are the groups, and the corresponding mnemonic.

L - Load (Record is loaded or initialized/field values loaded into SObject)
V - Validation (System validation rules)
T - Triggers (Before triggers executed)
Lion Visits Tiger

V - Validation (System and custom validation rules)
D - Duplicates (Execute duplicate rules)
T - Triggers (After triggers executed (record is first saved, not committed to db))
Voicing Displeasure to Tiger

AS - Assignment (Execution of assignment rules)
AUTO - Auto-response (Execution of auto-response rules)
WF - Workflow rules (Execution of workflow rules (field update executes triggers)
ASsuming AUTOmatically they Would Fight

P - Processes (Execution of process builders and flows)
ESC - Escalation (Execution of escalation rules)
ENT - Entitlement (Execution of entitlement rules)
R - Rollup (Rollup summary fields updated) 
F - Formula (Cross-object formula fields are updated)
he Pounced, ESCalating the ENTanglement, Removing Fur

P - Parent (Parent and grandparent records are saved)
S - Sharing (Criteria-based sharing rules are evaluated)
C - Commit (DML operations committed to db)
P - Post-commit (Post-commit logic)
the Pride Shared the Conquest, Party on!

Hope you find it helpful!
Thanks, Tom

Sunday, June 5, 2016

How to Disable Database Triggers for a Single Session

The Problem:
Database triggers can come in very handy and be used to solve a myriad of problems.  However, at times, their effects may not be desired. As an example, say there is a before-update row-level trigger that prevents updates to a row when its status code equals ‘CLOSED’.  We all know that sooner or later, a customer will need to change a row that has a status of ‘CLOSED’. Of course the trigger can be disabled prior to running the update script, such as:

       
begin

  execute immediate 'alter trigger bu_my_table disable';

  update my_table
     set x = 32
   where x = 3.2
     and status_code = ‘CLOSED’
     and create_date = ‘14-jul-2014’;

  execute immediate 'alter trigger bu_my_table enable';

end;
       
 

The problem with this approach is that the trigger logic is absent for updates occurring in other sessions as well. This may be problematic, or depending on the logic that gets skipped, catastrophic. It could help to just run your script when the likelihood of other updates occurring is lower.  While that approach may reduce risk, it's still far less than ideal. 

There is a better, safer way! The remainder of this article will explain how you can effectively disable triggers for just the single session used by the script.

The Solution:
The solution takes advantage of the fact that package variables are stored in the user global area (UGA). Data stored in the UGA is specific to a user’s session and can only be seen by programs the run in that same session. Therefore, if an update script running in session XYZ stores a value in a package variable, then that variable’s value can be interrogated by a trigger also running in session XYZ to determine whether to run the logic in the trigger.  The process is described in detail below.
  • You have determined that a script needs to skip trigger logic while executing DML.
  • The script sets a package variable to a prescribed value prior to executing DML.
  • The script executes the DML, which causes the trigger to fire.
  • Before the trigger logic executes, the trigger inspects the package variable for the presence of the prescribed value.  
  • If the value is found in the package variable, the logic is skipped, otherwise the logic is executed.
To implement these steps, do the following:
  • Create a package with a PL/SQL table indexed by varchar2 to represent a list of programs. 
  • Add two procedures to the package; enable and disable.
    • disable adds a value to list represented by the PL/SQL table
    • enable removes a value from the list represented by the PL/SQL table
  • Add a function called is_enabled to the package
    • is_enabled checks for the presence of a value in the list of programs
  • Alter your trigger source code to call is_enabled.
  • Alter any future scripts that need to skip trigger logic by calling disable before executing DML, and then call enable afterward.
The source code for step 1 is below.

       

CREATE OR REPLACE PACKAGE program_control_pkg
IS
  PROCEDURE disable (in_program VARCHAR2);

  PROCEDURE enable (in_program VARCHAR2);

  FUNCTION is_enabled (in_program VARCHAR2) RETURN BOOLEAN;

END;  -- Package spec

CREATE OR REPLACE PACKAGE BODY program_control_pkg
IS
  type program_tab_typ is table of varchar2(1)
  index by varchar2(4000);

  program_tab program_tab_typ;

  procedure disable( in_program varchar2 ) 
  is
  begin

    program_tab( in_program ) := 'Y';

  end disable; 

  procedure enable( in_program varchar2 ) 
  is
  begin

    program_tab.delete( in_program );

  end enable;

  function is_enabled( in_program varchar2 ) return boolean 
  is
    v_dummy varchar2(1);
    answer boolean;

  begin

    answer := true;

    begin

       v_dummy := program_tab(in_program);
       answer := false;

    exception

      when no_data_found then
        answer := true;

    end;

    return answer;

  end;

end;
       
 

Next, any triggers that may need logic skipped are altered as follows.

       

create or replace trigger bu_my_table
before update on my_table
for each row

  update_not_allowed exception;

begin

  if program_control_pkg.is_enabled('bu_my_table') then

    if :old.status_code = ‘CLOSED’ and :new.status_code = ‘CLOSED’ then
      raise update_not_allowed;
    end if; 

  end if;
…

exception 
  when update_not_allowed then
  …

end;

       
 

Now, any script that updates my_table can skip trigger logic for only its session:

       

begin

  program_control_pkg.disable('bu_my_table’);

  update my_table
     set x = 32
   where x = 3.2
     and status_code = ‘CLOSED’
     and create_date = ‘14-jul-2014’;

  program_control_pkg.enable('bu_my_table');

end;
       
 

Credit goes to Steve Jun for the idea.  I was merely the code monkey responsible for implementation.

This article was first published in the Fall 2015 issue of RMOUG SQL>Update magazine.

View the magazine!