Bug: Unable to use an Oracle BEFORE or AFTER trigger which reference st_geometry attributes with ArcGIS
When using an Oracle before or after insert trigger on a table with an st_geometry attribute, and the trigger body specification attempts to reference the geometry, and the insert is being performed by ArcGIS; the geometry is NULL.
The following example demonstrates an Oracle before insert trigger that references the geometry being inserted. The trigger specification obtains the st_geometry attribute being inserted (an intersection point) to validate if the geometry intersects another st_geometry attribute (centerlines). If there is no intersection, the trigger raises an error indicating that the insert is invalid and does not pass the required topological relation, which is that all intersection points must intersect a street centerline.
CREATE OR REPLACE TRIGGER intersection_validation
BEFORE INSERT ON intersections FOR EACH ROW
SELECT COUNT(*) INTO row_cnt FROM centerlines
WHERE sde.st_intersects(shape,:NEW.shape) = 1;
IF row_cnt = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'The intersection point does not
intersect a street centerline.');
The trigger will execute before each row is inserted into the intersections, but the geometry representing the ':NEW.shape' value is NULL. Therefore, in this example, the query in the trigger body will execute but will always return no rows, because the value for ':NEW.shape' is NULL.
The geometry is only NULL if ArcGIS is inserting the geometry. Non-ArcGIS applications, those using the various st_geometry constructor operators, will not encounter this bug, and the ':NEW.shape' geometry attribute will be valid.
In the case of ArcGIS inserting the geometry, all other properties of the st_geometry attribute will be valid and can be referenced in the trigger body specification (the entity, numpts, minx, miny, maxx, maxy, minz, maxz, minm, maxm, area, len, srid).
Using Oracle's Call Interface to bind a type's attribute values containing an Oracle LOB, such as the st_geometry points attribute, when performing an insert requires using the Oracle LOB locator. When performing the insert, the LOB locator address is provided by way of the RETURNING clause as part of the insert statement. The OCI application then uses the LOB locator address to set the value for the LOB attribute and Oracle writes the value to the database, but Oracle does not write the LOB attribute value until after performing the initial insert into the table and after any dependent triggers on the table are executed.
The following is an example of the SQL being executed by ArcGIS when inserting a row into a table containing an st_geometry attribute.
INSERT INTO TB.sewer SEWER (OBJECTID,SEG_ID,SYMBOL,PIPE_SIZE,SLOPE,SHAPE)
( :a1, :a2, :a3, :a4, :a5, :st1) RETURNING
SEWER.SHAPE.points INTO :points_out
The RETURNING clause is required for obtaining the st_geometry points LOB's locator for binding the value to be written to the database.
Using st_geometry's constructor operators such as, st_geometry, st_point, st_polygon, etc., will not encounter this Oracle limitation.
There is no workaround to the problem, simply do not reference or depend upon the st_geometry attribute to be valid when using a before or after insert trigger.