Triggers for delta tables

Question

I have been facing an issue to capture the delta updates on SDE.

We have written triggers on A and D tables to capture the delta updates(to capture the version name based on state id's), however I couldn't able to get the very first delete of any feature from a new version that has been created on Default.

After debugging, I have observed that, after the very first delete of a feature, the State Id in versions table is not being updated however the State Id in SDE_States is updated. So, the state id in versions table and the state id in SDE_States table is different.

I would like to say that the same query is working fine from second deletes and so on.... Having issue with getting the version name for only the first delete.

For your reference, please do find the query below.

Select @ObjectId=SDE_DELETES_ROW_ID, @SdeStateId=DELETED_AT, @SdeCurrentStateId = SDE_STATE_ID FROM inserted;

SELECT @Version= Name FROM sde.SDE_versions v,sde.SDE_states s INNER LOOP JOIN 
    sde.SDE_state_lineages l ON l.lineage_name = s.lineage_name WHERE s.state_id = v.state_id and lineage_id = @SdeStateId and s.state_id = @SdeStateId

IF @version IS NULL
SELECT @Version= Name FROM sde.SDE_versions v,sde.SDE_states s INNER LOOP JOIN 
    sde.SDE_state_lineages l ON l.lineage_name = s.lineage_name WHERE s.state_id = v.state_id and lineage_id = @SdeCurrentStateId

Answer 1

When you make a versioned edit, the state of the version is not updated until you save your edits. Internally, ArcObjects manages what state you are currently connected to. This is how ArcObjects manages the long transaction and undo/redo stacks.

If you want to know what version a specific state is child to, you will have to search up the state tree until you find a state that is also a version. Of course, that state could actually be referenced by many versions.

What exactly are you trying to accomplish? Are you trying to log all changes made to all versions?