Trigger on Add table in versioned SDE

PramodHarithsa1 — Sun, 12 Dec 2021 10:43:52 GMT

Hi All,

I was looking for a way to capture data edits on versioned database to update a non-gis system. (DB Oracle 11G)

What I have done so far?

I have created after insert or update trigger on the Add table of the featureclass,

it checks if the edit is posted,

if posted - then the updates are inserted into a temp table (from where its transferred to the other system)

create or replace TRIGGER "ADMIN"."TEST_TRG" AFTER INSERT OR UPDATE OF STATE
ON A3401
FOR EACH ROW 
DECLARE
isPost NUMBER;
BEGIN
SELECT COUNT(*) INTO isPost FROM SDE.STATES WHERE SDE.STATES.STATE_ID=:new.SDE_STATE_ID AND SDE.STATES.LINEAGE_NAME=SDE.STATES.STATE_ID;
IF INSERTING AND (isPost > 0) AND (:new.STATE=0 OR :new.STATE is NULL) THEN
INSERT INTO TEST_TRIGGER (OBJECTID,LOCATION,ID,STATE,TYPE) 
          VALUES ( :new.OBJECTID,:new.LOCATION,:new.ID,'NEW','UPDATED');
ELSE IF UPDATING('STATE') AND (:new.STATE=0) AND (:old.STATE=1) AND (isPost > 0) )  THEN  
INSERT INTO TEST_TRIGGER (OBJECTID,LOCATION,ID,STATE,TYPE)
          VALUES ( :new.OBJECTID,:new.LOCATION,:new.ID,'NEW','CREATED');
ELSE 
null;
END IF;
END IF;
END;

The problem I have here is, I want to capture the update only if they are done on specific columns. When the edits are posted, its always an insert and I won't be able to figure out what its previous value was. I tried writing a before insert trigger and compare with the value from Multi-versioned view but that runs into mutating table problem. [Note: The above script is not optimized, I am just trying to check the possibility of capturing the edit]

Possibly I am doing something wrong here or there is a better way to achieve this. Request help!

topic Trigger on Add table in versioned SDE in Geodatabase Questions

Trigger on Add table in versioned SDE