1

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.

Using:

  • 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?

  • I believe the answer to my question is: The best way to capture data changes that are happening in a versioned database is to create the triggers on Versioned Views. – MrChancey Mar 6 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.