SQL script for streaming creation of triggers in the geo database

Below it is the contents of the script make_triggers_for_objectid.sql, designed to create triggers that obtain a new value for field of the identifier for record OBJECTID, for all tables with spatial data registered in the geo database ArcSDE in the database Oracle.

-- ==============================================================================

--  Creation of triggers for generating OBJECTID values of new records,

--  added to tables of geo database ArcSDE not from applications ArcGIS

-- ==============================================================================

-- for obtaining OBJECTID uses the regular ArcSDE function - GDB_UTIL.NEXT_ROWID

-- ==============================================================================

DECLARE

 v_username  ST_GEOMETRY_COLUMNS.OWNER%TYPE; -- variable for owner name

 v_tablename GDB_ITEMS.NAME%TYPE;            -- variable for table name

 v_tablename_short GDB_ITEMS.NAME%TYPE;      -- variable for table name

 v_shapename ST_GEOMETRY_COLUMNS.OWNER%TYPE; -- variable for the name of the metric field

 v_srid      ST_GEOMETRY_COLUMNS.SRID%TYPE;  -- variable for the value of the field SRID

 v_srid_str  VARCHAR2(32);

 v_sql       VARCHAR2(4096);

 l NUMBER;

 CURSOR c1

 IS -- cursor to enumerate tables with geometry and PK (primary key)

   -- ========================================================================

      SELECT  

        USER as TableOwner,

        REPLACE(t.NAME, USER||'.', '') as TableName,

        c.COLUMN_NAME as ShapeName,

        c.SRID as SRID

      FROM GDB_ITEMRELATIONSHIPS r

      INNER JOIN GDB_ITEMS t ON t.UUID=r.DESTID

      INNER JOIN GDB_ITEMS l ON l.UUID=r.ORIGINID

      INNER JOIN GDB_ITEMRELATIONSHIPTYPES rt ON rt.UUID=r.TYPE AND rt.NAME='DatasetInFeatureDataset'

      LEFT OUTER JOIN ST_GEOMETRY_COLUMNS c ON UPPER(c.OWNER||'.'||c.TABLE_NAME)=UPPER(t.NAME) AND UPPER(c.GEOMETRY_TYPE) like 'ST_GEOMETRY%'

      WHERE UPPER(t.NAME) like UPPER(USER||'.%')

      ORDER BY rt.NAME;

 -- ========================================================================

BEGIN

 DBMS_OUTPUT.ENABLE();

 DBMS_OUTPUT.PUT_LINE( 'Begin...' );

 OPEN c1;

 LOOP

     v_sql := '';

     FETCH c1 INTO v_username, v_tablename, v_shapename, v_srid;

     EXIT WHEN c1%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE( v_tablename || ' :');

     DBMS_OUTPUT.PUT_LINE( '  - append trigger for new objectid');

     l := LENGTH(v_tablename);

     -- the maximum length of the trigger name is 30 characters

     IF (l <= 21)

       THEN v_tablename_short := v_tablename;

       ELSE v_tablename_short := SUBSTR(v_tablename, 1, 19) || '_' || SUBSTR(v_tablename, l, 1);

     END IF;  

     -- SELECT SUBSTR(v_tablename, 1, 21) INTO v_tablename_short FROM DUAL;

     -- adding a trigger to get a new value OBJECTID

     v_sql := 'CREATE OR REPLACE TRIGGER TR_' || v_tablename_short || '_NEWID ' ||

              'BEFORE INSERT OR UPDATE ON ' || v_username || '.' || v_tablename || ' ' ||

              'FOR EACH ROW ' ||

              'BEGIN ' ||

              'IF :NEW.OBJECTID IS NULL ' ||

              'THEN SELECT GDB_UTIL.NEXT_ROWID(''' || v_username || ''',''' || v_tablename || ''') ' ||

              'INTO :NEW.OBJECTID FROM DUAL; ' ||

              'END IF;  ' ||

              'END; ';

     BEGIN        

        EXECUTE IMMEDIATE v_sql; -- start

     EXCEPTION

        WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( '         - ERROR!');

     END;

 END LOOP;

 CLOSE c1;

END;

/