HOW TO

Use an Oracle trigger on a table to update an st_geometry attribute

Last Published: April 25, 2020

Summary

Database triggers provide the ability to generate or perform additional SQL during an event (such as an INSERT, UPDATE, or DELETE) on a table. Triggers can reference st_geometry attributes and perform feature validation or set attribute values.

Instructions provided describe how to create a trigger on a table containing x, y coordinate values to populate a st_geometry attribute each time a row is inserted.

Procedure

The steps demonstrate creating a table in SQL*Plus, thereby creating a trigger on the table, which sets an st_geometry value for each inserted row.

The scenario represents a table that records the position of ships using the x and y coordinate values and the current date. Each time a row is inserted into the table, the x and y coordinate values are used to generate an st_geometry (point) value.

  1. Create the table to record each ship's location, the ship's unique identifier, the moment when the location is recorded and the x, y coordinate location.
SQL> CREATE TABLE ship_location
  2   (objectid    NUMBER(38) NOT NULL,
  3    ship_id     NUMBER NOT NULL,
  4    moment      DATE NOT NULL,
  5    x_latitude  INTEGER NOT NULL,
  6    y_latitude  INTEGER NOT NULL,
  7    shape       sde.st_geometry);

Table created.
  1. Create the trigger on the table that sets the shape value for each row inserted into the table. The :NEW.shape represents the value of the st_geometry point based upon the x_latitude and y_latitude values. The value of 2 in the sde.st_geometry operator is the table's spatial reference.
SQL> CREATE OR REPLACE TRIGGER ship_loc_trig BEFORE INSERT ON
  2     ship_location FOR EACH ROW
  3    BEGIN
  4      
  5      :NEW.shape := sde.st_geometry('POINT('||:NEW.x_latitude||' '||:NEW.y_latitude||')',2);
  6
  7    EXCEPTION
  8      WHEN OTHERS THEN
  9      NULL;
 10    END;
 11    /
  1. Insert a row into the ship_location table and verify the shape attribute is being correctly updated.
SQL> INSERT INTO ship_location VALUES
  2  (1,99,SYSDATE,15474942,5533109,NULL);

1 row created.

SQL> SELECT sde.st_astext(shape)
  2   FROM ship_location;

SDE.ST_ASTEXT(SHAPE)
---------------------------------
POINT (15474942.000, 5533109.000)

Article ID:000010077

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options