Is there a way we can find from the SDE_STATE_ID of A Table whether the edit has been posted to database. (I am currently interested only in Adds)
I was trying to look through the multi-version views to keep an eye on this, but I can't as the views are looking back into the same A table and it would result in a mutation table error.
It may be helpful to look at the Version Changes command. You can use this to compare changes between versions. If a version has a change compared to it's parent version, the edit was not posted.
Thanks Jake Skinner for the reply.
I am actually setting up a trigger on A table to pass data into a different system (non GIS) and I want to consider entries only when they are posted. So all I get to see here is the user who is doing the editing and the SDE_STATE_ID. Are there any tables I can refer using these two values to confirm whether the edit has been posted much like the way its shown in version changes window.
without having a complete answer, here are some things to consider:
Altogether, I should think that application functionality (ArcObjects, GP, ...) will be an easier and safer way to manage secondary databases...
Good luck, Martin
Hi Martin Ameskamp ,
The DB is Oracle 11g, I had tried my luck with pragma autonomous_transaction earlier, though it solves the mutation table issue when checking the SDE.DEFAULT lineage after insert, it really doesn't help as the post operation is not complete yet. I would probably need an "On Commit" trigger (Which doesn't exist!)
I could get this working on some of the ArcFM features by comparing the STATE_ID with that existing in Versions table, but it doesn't seem to work on non-ArcFM features where the edits have not been commited when the After INSERT/UPDATE Trigger is invoked.
I had used the below query to get the STATE_ID currently part of the SDE.DEFAULT lineage and compare it with the STATE_ID of the edit to check if the edit was posted. (Not sure if there is an optimised way of doing this)
SELECT SDE_STATE_ID FROM ADMIN.AREA b,
(SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM ADMIN.D2370 WHERE SDE_STATE_ID = 0 AND SDE.version_util.in_current_lineage (DELETED_AT) > 0) d WHERE b.OBJECTID = d.SDE_DELETES_ROW_ID(+) AND d.SDE_STATE_ID IS NULL AND SDE.version_util.get_lineage_list > 0 UNION ALL SELECT a.SDE_STATE_ID FROM ADMIN.A2370 a,(SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM ADMIN.D2370 WHERE SDE.version_util.in_current_lineage (DELETED_AT) > 0) d WHERE a.OBJECTID = d.SDE_DELETES_ROW_ID(+) AND a.SDE_STATE_ID = d.SDE_STATE_ID(+) AND SDE.version_util.in_current_lineage (a.SDE_STATE_ID) > 0 AND d.SDE_STATE_ID IS NULL AND a.OBJECTID=:new.OBJECTID;
Hi Jake Skinner ,
I was able to get the record from VERSIONS table using the below query,
select count(*) INTO isPost from sde.versions where STATE_ID=:new.SDE_STATE_ID;
But there seems to be a delay between the AFTER A table update/insert and the VERSIONS table modification so I am not able to capture the post event. Is there any better way to do this?