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
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;
:new.COORD_X := v_aux_xcoord;
:new.COORD_Y := v_aux_ycoord;
:new.ATRIBUTE := v_aux_intersect_value;
EXCEPTION
WHEN OTHERS THEN
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
