X

Best Practices from Oracle Development's A‑Team

Behind the Delete Trigger in Sales Cloud Application Composer

Cautionary Details on Delete Trigger Behavior with TCA Objects

Developers and technically-inclined users who have ever needed to extend Oracle Sales Cloud are probably familiar with Application Composer (known as App Composer to its friends) -- the built-in, browser-based collection of tools that makes it possible to extend Sales Cloud safely without requiring a level of system access that would be inconsistent with and unsafe for the cloud infrastructure. Likewise, many who have built App Composer extensions probably know about object triggers and how to add custom Groovy scripts to these events. Object trigger logic is a major part of most Sales Cloud extensions, especially when there is a need to communicate with external systems. With current Sales Cloud releases (Rel9 and later), harnessing these trigger events and arming them with Groovy scripts arguably has become one of the more effective strategies for developing point-to-point data synchronization extensions.  These extension strategies are also relevant when Oracle Integration Cloud Service (ICS) is part of the picture.

Existing documentation on trigger usage in App Composer is located in two places: the Groovy Scripting Reference  and the guide for Customizing Sales. But due to the scope of these documents and the extent of topics that require coverage, the authors were unable to provide detailed information on triggers.  These reference guides were never meant to offer best practice recommendations on the use of specific triggers for different purposes. Given this need -- that Sales Cloud extension developers need more guidance in order to be more proficient when using object triggers -- there are numerous areas requiring deeper technical investigation.  These topics can, and probably should, be covered in detail in the blog arena.

One area requiring additional clarification and vetting of options is a somewhat obscure anomaly in the behavior of delete triggers across different objects in Sales Cloud. By design, objects belonging to Oracle’s Trading Community Architecture (TCA) data model – for example Accounts, Addresses, Contacts, Households, and more – are never deleted physically from the database, at least not through the native Sales Cloud application UI. Therefore, delete triggers do not fire as expected for these objects. In other words, any piece of App Composer extension logic touching TCA objects that includes a delete trigger as one of its components will probably fail. For non-TCA objects, triggers specific to delete actions work as designed. This post will explore differences in delete trigger behavior between TCA and non-TCA data objects in Sales Cloud. The illustrative use case used for this post is a requirement to keep a rudimentary audit trail of deleted object activity in Sales Cloud, tracking the object deleted along with user and timestamp values.

Prerequisites for the Use Case

A custom object, “DeleteAuditLog”, will act as the container for storing the archived object, user, and timestamp details. It has the following attributes:

Field Name Field Type Standard/Custom Additional Info
CreatedBy Text Standard value used to determine who performed the delete
CreationDate Date Standard date of deletion
RecordName Text Standard
LastUpdateDate Date Standard
LastUpdatedBy Text Standard
Id Number Standard
ObjectId Number Custom holds id of object deleted
ObjectType Text Custom holds type of object deleted
ObjectDetail Text Custom holds details for object deleted

Granted, the data elements that will be saved to the custom object do not represent an extremely meaningful audit trail; the intent is not to implement a complete solution but rather to demonstrate the potential of what is possible with trigger scripts.

Although not absolutely required, a global function that takes care of the DeleteAuditLog record creation and assignment of attribute values eliminates duplicate code.  Having it in place as a global function is consistent with modular coding best practices. Here are the details for this global function:

triggers8

Trigger Overview

For readers who have not yet ventured into the world of App Composer triggers, a short introduction is in order. Creating Groovy scripts for event triggers is a way to extend Sales Cloud by telling the system to do something extra when object-related system events occur. That “something extra” can take a variety of forms: validating a field, populating a custom field, calling out to an external web service, creating new instances of objects (standard or custom), or reacting to a given value in an object field and doing some extra processing. Different sequences of triggers fire when objects are created, modified, or deleted. Some triggers fire and are shared across multiple UI actions; others are single-purpose.

There are up to fifteen different object trigger events exposed in App Composer. Not all of these fifteen trigger events are exposed across all objects, however.  For example, the “Before Commit to the Database” trigger is not exposed for objects belonging to the Sales application container. To access and extend trigger events, navigate to the object of interest in the left navigation frame after selecting the correct application container, and then expand the object accordion, which will expose the “Server Scripts” link.

triggers1

Clicking the Server Scripts link populates the App Composer content window with the Server Scripts navigator for the selected object, one component of which is the tab for Triggers. (There are additional tabs in the content window for Validation Rules and Object Functions.) Selecting the Trigger tab exposes areas for Object Triggers and Field Triggers. New, edit, and delete actions for triggers are available through icons are through the Action drop-down menus for Object Triggers and Field Triggers.

triggers2

The majority of triggers are related to database events: before/after insert, before/after update, before/after delete, before/after commit, before/after rollback, and after changes are posted to the database. There are several remaining triggers related to ADF page life cycle events: after object create, before modify, before invalidate, and before remove.

For investigative purposes, it can be revealing to create Groovy scripts for these trigger events in order to reveal firing order and other trigger behaviors; in fact, that was the strategy used here to clarify trigger behavior across different types of objects. Thus, a typical trigger script that does nothing other than to log the trigger event might consist of the following two lines:

println 'Entering AfterCreateTrigger ' + adf.util.AddMilliTimestamp() println 'Exiting AfterCreateTrigger ' + adf.util.AddMilliTimestamp()

(NOTE: AddMilliTimestamp is a global function that displays time formatted with an added milliseconds component.)

After Groovy trigger scripts are in place for the object trigger events in focus, it then becomes possible to test multiple actions (e.g. object creates, updates, deletes) across different objects in the Sales Cloud user interface. This results in debug-style statements getting written to server logs, which can then be examined in the App Composer Run Time Messages applet to discover end-to-end trigger behavior for various UI actions. The logs for commonly-performed UI actions on Sales Cloud objects follow below. (Run Time Messages content was exported to spreadsheet format to allow selecting the subset of messages applicable to each UI action).

Object create followed by Save and Close:

triggers3

Object modify followed by Save and Close:

triggers4

Object (non-TCA) delete followed by User Verification of the Delete Action:

triggers5

Normal Delete Trigger Behavior

From the above listings, delete triggers work pretty much as expected, at least for non-TCA objects. BeforeRemove, BeforeInvalidate, and BeforeModify events occur before the database-related events – Before/After Delete and AfterCommit – fire. Given this sequence of events, if the goal of the Sales Cloud extension is to log details of the delete event, then it probably makes the most sense to target the unique trigger that fires as soon as the object deletion becomes a known sure thing but before the transaction commit in order to get the log record created in the same database transaction. In this case, therefore, focusing on the AfterDelete event should be optimal; it only fires for the delete action and it occurs, by definition, after the delete event occurs in the database.

There is a behavior unique to the delete action and its chain of triggers, however, that makes implementation of this straightforward approach a bit more complicated.  After the BeforeModify trigger event, which occurs fairly early in the event chain, getting a handle to the to-be-deleted record becomes impossible.  If a need exists, therefore, to read any of the record's attribute values, it has to be done during or before the BeforeModify event.  After that event the system treats the record as deleted so effectively it is no longer available.

Because the audit log requirement requires reading the value of an object id and then writing that to the audit trail record, hooking into the BeforeModify event is required.  But because the BeforeModify trigger is no longer unique to the delete UI action, the script would somehow have to include a check to make sure that the trigger is a part of the delete chain of events and not being fired for a normal update action.  There does not seem to be a way to perform this check using any native field values, so one option might be to push field values onto the session stack in the BeforeModify trigger, and then pull them off the session stack in the AfterDelete trigger.

Script for the BeforeModify trigger event:

println 'Entering BeforeModify ' + adf.util.AddMilliTimestamp() adf.userSession.userData.put('ObjectId', SalesAccountPartyId) adf.userSession.userData.put('ObjectDetail', 'Name: ' + Name + ', Org: ' + OrganizationName) println 'Exiting BeforeModify ' + adf.util.AddMilliTimestamp()

Script for the AfterDelete trigger event:

println 'Entering AfterDelete ' + adf.util.AddMilliTimestamp() println 'Creating Delete Audit Log Record' def objType = 'Sales Lead' def objId = adf.userSession.userData.ObjectId def objDtl = adf.userSession.userData.ObjectDetail def logResult = adf.util.CreateDeleteAuditLog(objType, objId, objDtl) ?   'Delete Log Record created OK' : 'Delete Log Record create failure' println logResult println 'Exiting AfterDelete ' + adf.util.AddMilliTimestamp()

TCA Objects and the Delete Trigger

Implementing a similar trigger for a TCA object (e.g. Account) leads to a far different outcome. The failure to log the Account UI delete event becomes clear when the debug Run Time Messages associated with the event are examined.

The delete action on the Account object results in the following series of triggers getting fired:

triggers6

The above listing of fired triggers is representative of what takes place when a TCA object record is "deleted" in the UI.  By design, soft deletes occur instead of physical deletes, so the sequence of trigger events looks more like the objects are being modified than deleted. And actually, record updates are indeed what occur.  For TCA objects, instead of being physically deleted they are marked as inactive by changing the value of the PartyStatus (or similar) field to ‘I’. This value tells the system to treat records as if they no longer exist in the database.

Therefore, hooking into delete trigger events for TCA objects will never have the desired effect.  What can be done about the audit log use case?  Now that this behavior is known for TCA objects, and knowing that the value of the PartyStatus (or equivalent) field can be used to check for the delete UI action, all of the audit log logic can be contained in the BeforeModify trigger event.  There is no need to push and pull values off of the session.  Hooking into the BeforeModify trigger event remains viable for TCA objects even though the chain of triggers is far different.

Here is a sample script, which shares some pieces of the delete script for Sales Lead above, for the Account (TCA) object:

println 'Entering BeforeModifyTrigger ' + adf.util.AddMilliTimestamp() // check for delete activity if (isAttributeChanged('PartyStatus') && PartyStatus == 'I') {   println 'Creating Delete Audit Log Record'   def objType = 'Account'   def objId = PartyId   def objDtl = OrganizationName   def logResult = adf.util.CreateDeleteAuditLog(objType, objId, objDtl) ?     'Delete Log Record created OK' : 'Delete Log Record create failure'   println logResult } else {   println 'Record Change other than a delete attempt' } println 'Exiting BeforeModifyTrigger ' + adf.util.AddMilliTimestamp()

Short-Term Workarounds and Long-Term Prognosis

Multiple customer service requests related to the delete trigger anomaly for TCA objects have been filed across various versions of Sales Cloud, and this activity has resulted in at least one KnowledgeBase article (Unable To Restrict Deletion Of Contacts Via Groovy (Doc ID 2044073.1) getting published about the behavior. A workaround entirely consistent with what was presented here for TCA objects is discussed in the article. For the longer term, enhancement request #21557055 has been filed and approved for a future release of Sales Cloud.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content