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

Question

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;

• lele3p: 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: 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.
• ORA-55378: 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

Answer 1

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.

• Ruchira Welikala: 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.
• lele3p: Why no use the GlobalID? It is managed by ArcSDE and is unique among all the feature class and version.
• Ruchira Welikala: 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.
• lele3p: 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.
• Ruchira Welikala: 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).