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.');
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