topic Re: ArcSDE 9.3 for Oracle: Triggers Missing in Data Management Questions

ArcSDE 9.3 for Oracle: Triggers Missing

AminaBayram — Mon, 27 Sep 2010 19:36:49 GMT

Hi,

I am new with ArcSDE for Oracle.
We have upgraded from ArcSDE 9.2 to ArcSDE 9.3.
I have noticed a change in the way everything is define on the Oracle side. There are no more F table and S tables to define the geometry and the spatial index.
In ArcSDE 9.2 we had triggers that are used for the DELETE, UPDATE and INSERT operations.
In the ArcSDE 9.3 these tiggers don't exist which cause space problems when we update layers.
How do we get these triggers?
Is there a postinstallation solution for this?
Can these triggers be created automatically? if so how?

Any help would be appreciated
Thank you

Re: ArcSDE 9.3 for Oracle: Triggers Missing

VinceAngelo — Mon, 27 Sep 2010 20:13:15 GMT

There are several storage methods possible with ArcSDE, not all of which are available
on all database platforms. The only significant change between 9.2 and 9.3 was a change
to the default storage type on Oracle 9i/10g from SDEBINARY to ST_GEOMETRY.

You have always had the option of specifying storage location (via DBTUNE file, which
every site should modify for their environment). Default storage type is also a property
of the DBTUNE file. There are multiple copies of this documentation online via a Resource
Center search (UR search window); the most recent of which is avaliable here.

Placing custom triggers on ArcSDE-managed tables is unsupported, and moderately risky.
You'd be much better off using the DBTUNE mechanism for storage management.

- V

Re: ArcSDE 9.3 for Oracle: Triggers Missing

AminaBayram — Tue, 28 Sep 2010 16:34:42 GMT

Vince,
Thanks for the information. Indeed we are using the default storage type (ST_GEOMETRY) on our new 9.3.database (that was our intention).

However new layers created on this server have no triggers on the oracle business tables to propagate deletes, updates etc to the corresponding S**_IDX$ tables. On our old 9.2 server, all business tables would automatically have a trigger of the flowing form added:

CREATE OR REPLACE TRIGGER SDE_USER.SPCOL_DEL_CASCADE_536
           AFTER DELETE OR UPDATE OF SHAPE ON SDE_USER.GRID_DLS_LSD83
           FOR EACH ROW
DECLARE
             -- ArcSDE 9.2 --
             inv_spatial_col1 EXCEPTION;
           BEGIN
           IF DELETING THEN
           DELETE FROM SDE_USER.F536 WHERE SDE_USER.F536.fid = : old.SHAPE;
    DELETE FROM SDE_USER.S536 WHERE SDE_USER.S536.sp_fid = : old.SHAPE
;
           END IF;
          IF UPDATING AND (:new.SHAPE IS NULL AND : old.SHAPE IS NOT NULL) THEN
          DELETE FROM SDE_USER.F536 WHERE SDE_USER.F536.fid = : old.SHAPE;
    DELETE FROM SDE_USER.S536 WHERE SDE_USER.S536.sp_fid = : old.SHAPE
;
          END IF;

           IF UPDATING AND (:new.SHAPE != : old.SHAPE AND : old.SHAPE IS NOT NULL) THEN
             RAISE inv_spatial_col1;
           END IF;

          EXCEPTION
            WHEN inv_spatial_col1 THEN
            raise_application_error (-20013,'Invalid SDE spatial column UPDATE. Cannot update spatial column value '||TO_CHAR(:new.SHAPE)||' to non-NULL value.');END;
/

Our question is why equivalent triggers are not (automatically �?? this is not a case of us wanting to create custom triggers) added?

Thanks,
Amina

Re: ArcSDE 9.3 for Oracle: Triggers Missing

VinceAngelo — Tue, 28 Sep 2010 17:18:33 GMT

ST_GEOMETRY does not have F or S tables, so it's not just the triggers that are missing.
There is an S table analog, but the F table is implemented as a LOB object type in the
business table (see the online doc for ST_GEOMETRY storage architecture details).

- V

Re: ArcSDE 9.3 for Oracle: Triggers Missing

AminaBayram — Tue, 28 Sep 2010 17:42:22 GMT

Thanks Vince,

The problem is that we have no triggers on the business tables.

I need to know if there is supposed to be a trigger on the business table that will update the Sxx_IDX$ table ? If so, we may need to re-create the database (rerun sdesetup)

Re: ArcSDE 9.3 for Oracle: Triggers Missing

VinceAngelo — Tue, 28 Sep 2010 22:06:30 GMT

ST_GEOMETRY doesn't have triggers like SDEBINARY and SDELOB, though I'm sure there's an
equivalent within the object behavior (one that's not subject to external tampering).

- V