topic Re: Use Oracle UPDATE TRIGGER instead of attribute assistant in Geodatabase Questions

Use Oracle UPDATE TRIGGER instead of attribute assistant

Bart-JanSchoenmakers — Mon, 19 Mar 2018 16:36:52 GMT

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?

Thanks

Re: Use Oracle UPDATE TRIGGER instead of attribute assistant

Bart-JanSchoenmakers — Sun, 12 Dec 2021 10:40:20 GMT

Hi,

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_xcoord NUMBER(20,2);
   v_aux_ycoord NUMBER(20,2);
   v_aux_SHAPE sde.st_geometry;   
   v_aux_intersect_value varchar(12);
BEGIN
   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;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;

END TRG_FEATCLASS_TABLE_NAME;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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.

Bart

Re: Use Oracle UPDATE TRIGGER instead of attribute assistant

MichaelVolz — Fri, 27 Apr 2018 14:04:23 GMT

Are you editing SDE Oracle data in a versioned environment using the Attribute Assistant?

Re: Use Oracle UPDATE TRIGGER instead of attribute assistant

Bart-JanSchoenmakers — Fri, 27 Apr 2018 14:39:54 GMT

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.

Re: Use Oracle UPDATE TRIGGER instead of attribute assistant

MichaelVolz — Fri, 27 Apr 2018 17:30:36 GMT

Have you looked into using Attribute Rules in Pro (replacement for AA) instead of implementing Oracle triggers?

Re: Use Oracle UPDATE TRIGGER instead of attribute assistant

Bart-JanSchoenmakers — Mon, 30 Apr 2018 15:01:57 GMT

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.