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!