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:
Cons:
Calculation Attribute Rules:
Pros:
Cons:
What do you think? What did I miss?
Disclaimer: I have never worked with SQL triggers.
SQL triggers need additional software / SQL developers in the IT department. In my organization, server management software is not available for non-IT staff. So I would have to coordinate with IT staff to get what I want. AFAIK, nobody in our IT department knows enough about SQL to write the triggers. I need lots of automatic edits in my database, and during development, my requirements frequently change. In that case it's much easier to learn about Arcade and implement Attribute Rules myself than wait forever for IT to find the time.
With Attribute Rules, you can view, create, and edit the data structures, triggers, and data in the same application.
You can use Arcade to do other stuff (e.g. popups, labels, visualization, dashboards), so it's actually quite useful to learn.
With SQL triggers, you lose all geodatabase functionality. Things like Editor Tracking, Versioning, Archiving are all done automatically with Attribute Rules, while you have to implement that yourself with SQL Triggers. For example, if you edit a versioned table with SQL Triggers and want to emulate the versioning, you have to edit at least the two delta tables, maybe also some geodatabase system tables.
Attribute Rules tell you when something is wrong. If you made obvious errors, it won't even let you save the Rule. If you made less obvious errors that only emerge at runtime, you get an error message telling you where the error occured, which is of course extremely helpful for development. I don't know if that is the case with SQL Triggers.
Constraint Attribute Rules are a helpful tool. For example, you can reject edits that change a value to be out of some arbitrary bounds, or you can reject edits if the geometry falls inside/outside of a specific area. You can probably do something like that with SQL Triggers, too, but with Constraint Attribute Rules you can define a custom error message to tell the user what is wrong.
Validation Attribute Rules let you define some rules that are checked on demand, so that you can do manual quality assurance.
Deprecation: Of course this is always a possibility. But Arcade is a new feature that is under constant development and is meant as a language for the whole ArcGIS environment, so I doubt it is going away any time soon. Attribute Rules are a feature of the Geodatabase, whereas Python addons were a feature of the application, that probably also makes a (favorable) difference in regard to deprecation.
Locking: Yes, that can suck. In my case, I have only one coworker with write access, all others have only read access. And they don't see (and therefore don't lock) the actual tables, but database views. Another way is to disallow connections as geodatabase admin and then ask the database/server admin to close all existing connections.
Limited functionality: I think all the basic stuff is there, and they add new functionality quite frequently. Of course, it isn't as extensive as older languages (and probably never will, as it's developed for very specific purposes).
Performance: Rules that only affect the edited feature are quite fast. If you do huge chunks of edits at once, you will notice it. If you do single manual edits, probably not. More complex rules (filter tables, intersection, edit other tables) have a higher performance impact. Manual edits on some of my feature classes can take up to 3 seconds to evaluate the rules. But they are filtering 2-4 tables, do at least multiple intersections and send edit requests to multiple tables that trigger rules on those tables.
Accessing vertices: Polylines and Polygons are modelled as array of arrays of points (multipart features). You can access vertices like this:
// polyline
var geo = Geometry($feature)
var multipart_path = geo.paths // [ [Point] ]
var singlepart_path = geo.paths[0] // [Point]
// polygon
var geo = Geometry($feature)
var multipart_path = geo.rings // [ [Point] ]
var singlepart_path = geo.rings[0] // [Point]
for(var i in singlepart_path) {
var p = singlepart_path[i]
Console(p.X + "/" + p.Y)
}
All in all, I'm a big fan of Attribute Rules and Arcade. It has enabled me to do stuff that I couldn't do with Attribute Assistant and Python Addins. My IT department would hate me if I would come to them asking to change SQL triggers all the time, and I can use the Arcade knowledge I gained to create good-looking popups and do some light label and symbol customization. If you don't do constant automated edits of huge chunks of data, I'd go for Attribute Rules.
Very excellent, thorough post!
I'll add my two cents that, if I recall correctly, Attribute Rules are slated to come to AGOL / Portal hosted layers someday, which is an environment where users don't have access to the underlying DB.
Also, Attribute Rules give you access to other functions like FeatureSetByPortalItem, which let your expression access features and layers outside of the database. Even outside of your portal, if it's a layer hosted by some other org. Not that you always want your expression to rely on outside layers staying put, but having that ability is nice.
Hi @jcarlson, this is two years late but I am curious about the FeatureSetByPortalItem access in attribute rules. I tested out my first spatial calculation attribute rule in the database this week, and I tried referencing data as you would in AGOL using the FeatureSetByPortalItem - and it said the attribute rule profile does not include that function, but rather only FeatureSetByName within the map or datastore. Is this something that used to be possible but is no longer? Thank you!
Edit: Disregard
Attribute Rules do trigger Editor Tracking, the respective tracking fields are set. Depending on your setup, you maybe won't get the time of the actual insert/update, but the time these changes got saved.
One thing to add to list:
I've learned that a significant con to attribute rules is: We can't develop scripts in a proper IDE -- and test them right there in the IDE.
Whereas we don't have that problem with SQL Triggers. We can develop in a IDE like Oracle SQL Developer, Toad, etc...and it all works as expected.
That's not a total deal breaker. Lots of people use calculation attribute rules successfully -- even without an IDE. I just thought I'd add it to the list, since it's something that's causing me trouble at the moment.
Good points.
Regarding versioning:
I suspect that versioning with the option to move edits to base could solve some of the issues you mentioned.
Regarding error handling:
I believe it's possible to put error handling in Oracle PL/SQL triggers...and then that error message gets returned to the ArcGIS application (such as ArcGIS Pro).
IF v_upd_row = 0 THEN
raise_application_error ( -20001, 'a custom message that pops up in ArcGIS Pro');
END IF;
That error message would be displayed in a popup message in the application. It'd be the same kind of message that you'd get for any other database-related error.
Source: Prevent deletes in a versioned table at the database level
By the way, if you're a data owner, the data owner likely has CREATE TRIGGER privileges. So in a pinch, you could probably create triggers if you needed to. But it sounds like you don't.
You could install an SQL client or you could execute SQL statements using the ArcSDESQLExecute ArcPy class.
I just thought I'd mention that...for anyone else who might be reading this post.
Or you could execute SQL statements using the ArcSDESQLExecute ArcPy class
That's a good point. I find this class quite useful for
I tried using it for writing user tables (again, because using SQL is faster than the arcpy cursors), but it didn't trigger Editor Tracking and, far worse for me, it didn't trigger Attribute Rules. I didn't have versioning in place then, so I don't know how it interacts with that.
The documentation page you linked raises some interesting points which seem to apply to SQL in general, too:
- Do not alter enterprise geodatabase system tables using SQL. Corruption can occur if these system tables are edited directly using SQL.
- Edits on traditional versioned data performed using SQL should only be made through versioned views. Do not edit branch versioned data using SQL.
- [...]
- [...] Be aware, though, that SQL access to the geodatabase bypasses geodatabase functionality, such as topology, networks, terrains, or other class or workspace extensions and bypasses triggers and stored procedures used to maintain the relationships between tables needed for certain geodatabase functionality. Circumventing geodatabase functionality can corrupt the relationships between data in your geodatabase.
- Before attempting to access or modify any enterprise geodatabase objects, read all enterprise geodatabase documentation about using SQL against geodatabase objects in the DBMS.