I'm trying to understand how to make sure a delete is a proper delete and not a modification, and vice versa. I'm using ArcGIS 10.2 and Enterprise geodatabase on MS SQL Server.

See below SQL:

select delete_table.*, add_table.* from 
  db.schema.a175 add_table
  full join
  db.schema.d175 delete_table
      delete_table.SDE_DELETES_ROW_ID = add_table.OBJECTID
      and add_table.sde_state_id = delete_table.sde_state_id
 where objectReference = 'Asset1'

and this returns something like

enter image description here

In this query, Asset1 is meant to have 2 objects related to it. But the user made two objects and realised one was wrong, deleted it and added another object. So now the Add table has three objects and the Delete table shows one.

But reading this output, I would have guessed objectid 8908810 is modified because there is also an entry in the Add table, and if this was a Deleted object, then the joined Add record would be all nulls.

Using the Add and Delete tables, how do I distinguish between modified and deleted objects?


TLDR: You can't tell by just counting the A and D table records; you also need to know if the object was in the business (base) table as well.

Objects from the base table which have not been modified before deletion have only an entry in the deletes table (Base, Delete)

Objects which have never been posted to the base table would have the same number of adds and deletes if deleted (Add, Delete), and one more add than delete if modified (Add, Delete, Add)

Base table objects which have been modified have the same number of adds as deletes (Base, Delete, Add)

Deleted objects have an ending state in the state tree that corresponds to the delete event (until a compress eliminates them, or a post manifests the delete in the base table).

Just to make things more complicated, the same object could be deleted or modified in multiple versions (which would manifest as a conflict), so you may need to follow the state lineage to determine which states are appropriate to the editing session result.

| improve this answer | |
  • Thank you! That makes it clear. But, just trying to imagine the last paragraph. Generally if an object is deleted, the sde_state_id in the delete table for that object will be >= the sde_state_id shown in base (which is 0) and add table. Would that be correct? – Joe May 16 at 12:21
  • The "delete event" you mentioned, is that the DELETED_AT value in the delete table? And that value will be > the sde_state_id shown in base (which is 0) and add table, if it's deleted? – Joe May 16 at 12:29
  • 1
    Due to the way state ids are allocated in multi-user environments, it's not safe to assume that the highest state is the last applied one, and there could be conflicts between differing versions. – Vince May 16 at 12:37
  • So the best way to go about it, assuming everything has been reconciled and posted, is to view the DEFAULT version instead. Because in my case I'm trying to interrogate the delta tables because the db hasn't been compressed yet. – Joe May 17 at 0:25
  • You can use a versioned view to interrogate table contents, even without reconcile and post, if they date back to something as ancient at 10.2. I know they were featured in the EGMP exam at 10.4 and 10.6, but that far back is a blur. Note that this is drifting into a different question. – Vince May 17 at 0:32

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.