We have an Geodatabase implemented in Oracle and are using the Attribute Assistant to update some attributes, with values based on other layers, when editing our data.
The use of the attribute assistant is a bit tricky since people sometimes forget to include the necessary tables in their project and since half a year are we also use ArcGIS Pro, which doesn’t have an attribute assistant yet.
It seems to me that the same functionality can also be implemented as an Oracle Update Trigger. When using the SQL ST_Geometry functions it must be possible obtaining the attributes of related layers (sde.st_intersects).
Before I start to test and develop a trigger I would like to know if some people use Oracle triggers as an alternative for the Attribute Assistant? If so what are the difficulties and limitations?
I managed to implement an Oracle Trigger using the different SDE GEOMETRY functions.
Here follows some of the code for anyone who would like to do the same:
create or replace TRIGGER TRG_FEATCLASS_TABLE_NAME
BEFORE UPDATE ON FEATCLASS_TABLE_NAME
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE -- VARIABLE
v_aux_SHAPE := :new.SHAPE;
v_aux_xcoord := sde.st_geometry_operators.st_x_f (v_aux_SHAPE);
v_aux_ycoord := sde.st_geometry_operators.st_y_f (v_aux_SHAPE);
SELECT SOME_ATRIBUTE INTO v_aux_intersect_value FROM REFERENCE_FEATCLASS WHERE sde.st_relation_operators.st_intersects_f(v_aux_SHAPE,REFERENCE_FEATCLASS.SHAPE) = 1;
-- Update fields in Feat Class
:new.COORD_X := v_aux_xcoord;
:new.COORD_Y := v_aux_ycoord;
:new.ATRIBUTE := v_aux_intersect_value;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
In this example the geographic coordinates are written to 2 fields and values are obtained from an external featureclass. The trigger is activated after the save. It works well in ArcGIs Pro, in ArcMap 10.5 the st_intersects gives problems
More information about using PLSQL in combination with ST_GEOMETRY in this link: https://support.esri.com/en/technical-article/000010459
Since this trigger in a production environment might slow the edit process, we are thinking about using a simpler trigger which marks all geographically edited features and use the same kind of code in a cursor which runs regularly.
Yes we are using the attribute assistant. But since not everyone has this extension active and we are changing to ArcGIS Pro we would like to have a database trigger.
We already use a trigger for new points.
No we didn't yet. When we started working with attribute assistant, ArcGIS Pro didn't have this option yet.
Our experience in using the attribute assistant is that not all users always use the extension. Having a server side solution gives more guarantees.