How to know if an edit to A Table has been posted

Question

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.

PramodHarithsa1 · Answer

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;

MartinAmeskamp · Answer

Hi Pramod,

without having a complete answer, here are some things to consider:

* If you're working with triggers and want to insert records into some other table from the trigger, you probably know about autonomous transactions in the trigger. You don't say which RDBMS you're using, for Oracle, there's a pragma autonomous_transaction, SQL Server doesn't seem to have this directly, but there's linked servers which might help.
* The STATE_ID of a record in an A table may change a couple of times before the records gets posted to DEFAULT and conversely, a record may get posted to DEFAULT without the STATE_ID changing, so I'm not really sure how to detect the post through a trigger on the A table...
* If you want to know whether a given record in an A table is part of DEFAULT, you would at least have to check whether that state is part of the SDE.DEFAULT lineage, and whether there are D table entries that neutralize the A record.

Altogether, I should think that application functionality (ArcObjects, GP, ...) will be an easier and safer way to manage secondary databases...

Good luck, Martin

PramodHarithsa1 · Answer

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?