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!
