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

arcgis-servereditingfeature-serviceoracle-spatialtrigger

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;

Best Answer

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.

Related Question