English

How To: Use an Oracle trigger on a table to update an st_geometry attribute

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.

    Code:
    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.

  2. 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.

    Code:
    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 /

  3. Insert a row into the ship_location table and verify the shape attribute is being correctly updated.

    Code:
    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)