Hi All,
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.
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.
Hi Pramod,
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?
Hi Pramod,
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.