ArcSDE 10 with a MS SQL 2005 backend.
I am trying to see the most recently modified geometries using SQL so that I can use it to update another GIS database running PostGIS. Has anyone tried this before? I am using jTDS and oracle SQL developer to connect to the MS SQL database, if that helps.
Rolling your own methodology with timestamps and triggers can work as long as you are not using versioning. Otherwise, it complicates things.
If you are using versioning, you can figure out the features that have changed with various APIs.
For example, you can use the ArcSDE versioning API, but you will be prone to error and will corrupt your data if you don't know how versioning works. Two give you an idea, during the reconcile process (i.e when merging two versions), the reconciling code figures out which state ids (numbers) correspond to the named versions involved (e.g "SDE.DEFAULT, SDE.MY_VERSION_NAME, whatever) and uses the ArcSDE API to get a list of everything that has changed in their versioning branch between them and the common ancestor. ESRI has sample code of how to do that purely with the SDE API.
The other (IMHO, much better) option, is to skip all that code, and using the ArcObjects Difference Cursor. I may be biased towards this option since I wrote that cursor some years ago, but it is the only way that you are guaranteed that you will get full GeoDatabase behavior.
UPDATE: For a better understanding of versioning and the version state tree, you can take a look at my answer for a related versioning question.
Unless you add your own workflow and datestamp edits. You will have to rely on sde system tables to access the modified data. Probably creating a view with joins on the add, deletes tables.
Sorry I cant tell you how to do that.
Here is a possible starting point though.
create trigger
