4

I'm trying to use a trigger to update a versioned dataset in an SDE environment. Basically, I wanted to implement a system of unique ids called GISID. Initially, when a feature is created (inserted), this GISID is generated by taking the ObjectID. We don't want to use ObjectID as our uniqueID because if a row with the an ObjectID is deleted, that object ID cannot be used in the dataset again. We have instances where some assets are tied to a GISID and it's essential the GISID stays uniform, even if we have to recreate that feature/record. Anyway, I have a trigger that works great in an ArcMap Edit session. PL/SQL Code here:

CREATE OR REPLACE TRIGGER "OwnerName"."A5461_GISID" 
  BEFORE INSERT OR UPDATE ON OwnerName.A5461 
  FOR EACH ROW 
DECLARE 
    gisidcntbasetbl NUMBER; --variable for count of same GISID in the basetable
    gisidcntatbl    NUMBER; -- variable for count of same GISID in the "A" table

BEGIN
    IF :new.gisid IS NULL 
        OR :new.gisid = 0 THEN 
      :new.gisid := :new.objectid; 
      -- If statement to check if the new GISID  
      -- being added is null or zero. If it is, then we give it the object ID. 
    ELSE 
      --Get count of GISID occurances in the base table
      SELECT Count (*) 
      INTO   gisidcntbasetbl 
      FROM   ES_WASTERECEPTACLES 
      WHERE  ES_WASTERECEPTACLES.gisid = :new.gisid; 

      --Get count of GISID occurances in the A table
      SELECT Count (*) 
      INTO   gisidcntatbl 
      FROM   A5461 
      WHERE  A5461.gisid = :new.gisid; 

     --If counts are greater than 0 then we set it to the new objectID. 
      IF gisidcntbasetbl > 0 
          OR gisidcntatbl > 0 THEN 
        :new.gisid := :new.objectid; 
      END IF; 
    END IF; 
END;

However, the problem arises when I go to create a feature using a web editing session using a ArcGIS Server Feature Service. Would anyone have any ideas about this? Possible pointers. The data gets written to the table accordingly, but the GISID trigger doesn't seem to run.

Update: I was able to configure the trigger the following way to get it to work with the feature service. However, I've been told by many parties that triggers on A tables is a bad idea. So I have since abandoned this method. We are using versioned views on feature classes to generate the unique IDs after hours.

TRIGGER "Ownername"."A5671_GISID" 
  BEFORE INSERT OR UPDATE ON Ownername.A5671 
  FOR EACH ROW 
DECLARE 
    gisidcntbasetbl NUMBER; 
    gisidcntatbl    NUMBER;
    tblMutating Exception;
    Pragma Exception_init(tblMutating, -04091); 
BEGIN
      SELECT Count (*) 
      INTO   gisidcntbasetbl 
      FROM   ES_WASTERECEPTACLES 
      WHERE  ES_WASTERECEPTACLES.gisid = :new.gisid; 

      SELECT Count (*) 
      INTO   gisidcntatbl 
      FROM   ES_WASTERECEPTACLES_VW 
      WHERE  ES_WASTERECEPTACLES_VW.gisid = :new.gisid;

    IF :new.gisid IS NULL OR :new.gisid = 0 THEN 
      :new.gisid := :new.objectid; 
    ELSE 
      IF gisidcntbasetbl > 0 OR gisidcntatbl > 0 THEN 
        :new.gisid := :new.objectid; 
      END IF;

    END IF;
EXCEPTION
  WHEN tblMutating THEN
    IF :new.gisid IS NULL OR :new.gisid = 0 THEN 
      :new.gisid := :new.objectid; 
    ELSE 
      IF gisidcntbasetbl > 0 OR gisidcntatbl > 0 THEN 
        :new.gisid := :new.objectid; 
      END IF;

    END IF;
END;
  • Can you add an example of rest call? You must specify the parameter gdbVersion. If this is not specified, edits are made to published map's version. – lele3p Sep 20 '16 at 7:51
  • 2
    I think the feature service writes directly to the default version, otherwise you should manually post the version to move the data on the default version. – lele3p Sep 20 '16 at 7:58
  • At least based on the requirements you listed, why can't you use GlobalIDs instead of custom-maintained triggers on the versioned view/ A & D tables? desktop.arcgis.com/en/arcmap/latest/tools/… and support.esri.com/technical-article/000011677 – ORA-55378 Sep 22 '16 at 19:18
2
+50

Ok. I replicated the environment:

ArcSDE 10.1 Insert / Update Trigger on Adds (A) Table (Oracle 11g) and Feature Services on ArcGIS Server 10.1.

If the feature service is published on the default version, the trigger not run.
Otherwise if the feature service is published on the child version, the trigger run.

  • Wow, that's a great find. Unfortunately, we need to serve the data from the default version because we have desktop editors that add data as well. – Ruchira Welikala Sep 22 '16 at 13:58
  • Why no use the GlobalID? It is managed by ArcSDE and is unique among all the feature class and version. – lele3p Sep 22 '16 at 15:27
  • 1
    we use GISIDs to keep track of GIS features in our asset management software. Once an ID is created, it can't change if we copy the data into a new feature class or something like that. That's why we're not using OBJECTID because once it's used in a feature class, it can never be used again. There might be times where an asset is removed from a feature class, and then get added back later on with the same assetID/GISID. Do GlobalIDs behave the same way as objectIDS? If not, then it could be a viable option. – Ruchira Welikala Sep 22 '16 at 16:06
  • I don't understand "if we copy the data into a new feature class or something like that". If you create one asset in the feature class A and one in the feature class B, the ObjectID can be the same, the GlobalID not. The GlobalID is unique for the entire database. – lele3p Sep 22 '16 at 16:35
  • It can be the same, but it can also not be. If the one of the feature classes is beyond the objectID of the feature being copied (in its sequence) then that object ID cannot be repeated for that feature class. So, say you created 10 features in a FC. ObjectID goes from 1 to 10. Then you delete all of those features. Then you copy a feature from another FC that has the ObjectID of 5 (in its source), when you copy it into the destination fc, it will change the objectID to 11 (because that's the next objectID in the sequence). – Ruchira Welikala Sep 23 '16 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.