How can I create a trigger to capture when a field is changed from one value to another if my data is versioned? I am not able to run an UPDATE trigger and grab ':new' and ':old' values because with versioning the change in the field value is just an addition to the A#### table. I would like to be able to compare the old and new value of this field when it is changed to make sure they are not the same.
- ArcGIS Pro
- Versioned Database (I am working with and have access to a child checked out from DEFAULT as well as a grandchild (child of child of DEFAULT)
- Oracle w/ SDE
I was able to create a trigger that captures the creation of a new feature in a specified feature class and stores it in a history_tracking_table (the start of tracking a record). It grabs a unique ID, the initial tracking state (which is NULL), the user who made the change, and the change date are captured as well. I have done this and it's functional but maybe not pretty. See code below:
create or replace trigger insert_obs_history_2 AFTER INSERT ON A261570 FOR EACH ROW DECLARE username varchar(20); prev_Match_Recs number; BEGIN SELECT COUNT(*) INTO prev_Match_Recs FROM HISTORY_TRACKING_STATE WHERE HISTORY_ACTION = 'INSERTED' AND HISTORY_REPORTID = :new.O_REPORTID; IF prev_Match_Recs = 0 AND :new.O_REPORTID IS NOT NULL AND :new.SUBMISSION_STATE_ID IS NULL AND :old.SUBMISSION_STATE_ID IS NULL THEN SELECT USER INTO username FROM dual; INSERT INTO HISTORY_TRACKING_STATE VALUES ( :new.O_REPORTID, :new.SUBMISSION_STATE_ID, username, SYSTIMESTAMP, 'INSERTED' ); END IF; END;
How can I do something similar to capture updates to the SUBMISSION_STATE_ID field as it changes over the records lifetimes while working in a versioned environment where 'updates' are inserts into the delta tables?