Database Triggers vs. Calculation Attribute Rules

[Bud]

 What are the pros & cons of database triggers vs. calculation attribute rules? 

Example: a trigger or calculation attribute rule that would modify the M-values of polylines after edits (in real-time).

SDE.St_Geometry; Oracle 18c; versioned with moving edits to base

Database Triggers:

Pros:

• Don't need to worry about users locking data -- which seems like it would be a problem with calculation attribute rules.
• Lots of functionality available in the DB: There are more St_Geometry functions than there are Arcade functions. And SQL has a lot more functionality in general than Arcade.
• Oracle trigger functionality will never be deprecated. Whereas there is a chance ArcGIS functionality like attribute rules/arcade will be deprecated at some point. I know it's hard to imagine that happening in the foreseeable future, but deprecation does seem to be something that happens in Esri applications. So it's not beyond the realm of possibility.
• Fast performance.

Cons:

• Only SQL developers (with SQL client software) can manage triggers. 
• We consider triggers to be customization...we try to avoid customization if we can.
• If the triggers use St_Geometry functions, then that means we are married to St_Geometry.
  (We've always dreamt of moving to SDO_GEOMETRY, since it has a lot more functionality, including linear referencing functions. And Oracle Spatial is now free. So if we wanted to switch to SDO_Geometry, we would need to consider how much it would impact any existing St_Geometry-based triggers.)
• No access to true curves information -- since it's hidden in the CAD BLOB. Although, Arcade doesn't seem to be much better.
• Accessing all of a feature's vertices is difficult. 

Calculation Attribute Rules:

Pros:

• Anyone who's a data owner can make attribute rules right in the application. No need to be a SQL developer. Although, basic JavaScript skills are still required.
• I wouldn't consider attribute rules to be customization, which is good. (There is a benefit to putting logic in the application, where it is visible and accessible to everyone.)
• Not specific to a certain database vendor or spatial data type.
• Easily prevent a field from being edited.
• I imagine Esri would be quick to provide support for a problem with attribute rules. And reluctant to provide support for DB triggers, especially on versioned data.
• Editing related FCs is relatively straightforward.
• "Rules can be targeted for specific subtypes in the rule definition. This is helpful when you want a rule to only apply to a subset of features in a dataset." Link
• We can use attribute rules to enforce domains

Cons:

• Can't manage attribute rules if data is locked. I imagine a table would be locked if even a single user has the table in their map.
  I'm a data owner, not a geodatabase administrator, so I think that could be a pain.
• Arcade has limited functionality -- for example, geometry has limited functions and properties. 
• There is a chance attribute rules/arcade could be deprecated at some point. We saw that happen with Python add-ins. People lost a lot of work when that happened.
• Performance might be relatively slow -- for batch scenarios.
• Note: Attribute rules require a GlobalID field. That's not really a problem, just worth noting.
• No direct access to true curves information.
• Polylines get automatically densified (similar to ST_Geometry).
• Arcade doesn’t handle nulls properly.
• We can't develop scripts in a proper IDE -- and test them right there in the IDE.
• Batch calculation attribute rules seem clunky. They have lots of very specific requirements: the FC must be in a feature service, the feature service must have validation capability, and the FC must be branch versioned.
  For my notes: Batch calculation rules are not to be confused with a scheduled job that calculates an entire field. Batch calculation rules are more about deferring slow edits until later.

---

What do you think? What did I miss?

[yockee]

I might add one more thing :

Cons of Arcade Attribute Calculation Rule is: if it uses $datastore in the script and the data is accessed via Feature Service hosted on Cloud (like AWS or AGP), it will generate cost on the cloud usage.

"if (id == null || id == "") {
return true;
}var fs = FeatureSetByName(
$datastore,
"db1.sch1.FC1",
["Oid", "objectid"],
false
);