I need to build a sql server db trigger to update a date field with the current date when a record is inserted or updated, I think. I will be using the date field as a way to recognize updates/inserts programmatically. I went to sql server mngt studio and navigated to the database in the tree view >> opened up the programmability folder >> opened up the Trigger folder and right clicked >> Create new trigger >> I then copied the below sql into the window and hit F5. It messaged back to me that the command was successful but when I tried to open the Triggers folder I didn't see it there. When I tested in arcmap inserting a record I did not get the date updated to my date field.

What am I doing wrong and are there special considerations for creating Insert/Update/Delete triggers against SDE? Thanks...the sql is below.

>ON dbo.TestTable
>UPDATE TestTable SET TestTable.LastUpdated = Getdate()
  • 1
    Is your data versioned in SDE? If it is, then the original source table won't get updated until the version is reconciled back to DEFAULT. Also, your trigger will update ALL rows with the same date, not just the new row.
    – mwalker
    Commented Sep 13, 2011 at 18:55
  • How would this be done? I have the scenario where the edits will come through a AGS Rest Feature Service. I need to get a list of records updated. At the desktop, I have a add-in that populates the info for me into sde..that I downloaded..but I don't see how that could be used over the web. Thanks for any help and your answers.
    – Justin
    Commented Sep 13, 2011 at 19:20
  • A good place to start is the Esri support site, to read up on what versioning is and what it does: help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/…
    – mwalker
    Commented Sep 13, 2011 at 21:02
  • Your desktop add-in won't be available via the REST feature service. For that kind of thing, you'll have to investigate a SOE (Server Object Extension).
    – mwalker
    Commented Sep 13, 2011 at 21:03
  • @mwalker - I am fairly comfortable in terms of what versioning is from the standpoint of ArcCatalog/ArcMap. On the gdb_<business tables> side in mngt studio..I looked into these to see if there was a way to pluck out which records have been added to the delta tables but I didn't find anything. I have a python script which does a featurecompare to look for shape changes prgrommatically but this just uses my edit date received from the desktop add-in..I need the same ability to be able identify records that have changed via the web..so that I can do some comparisons of the parent records.
    – Justin
    Commented Sep 13, 2011 at 21:50

3 Answers 3


I don't know about SDE versioning but the database can be easily used to track changes.

Your trigger as it is will only be triggered only on insert statements and will update the LastUpdated column for all rows in the table:

CREATE TRIGGER t_I_TestTable on dbo.TestTable FOR INSERT AS            
    UPDATE TestTable SET TestTable.LastUpdated = Getdate()

By reading your question I think what you want is something like this:

CREATE TRIGGER t_I_TestTable on dbo.TestTable FOR UPDATE AS            
    UPDATE TestTable SET TestTable.LastUpdated = Getdate()
         FROM TestTable INNER JOIN deleted d
         on TestTable.id = d.id 

The trigger above will update the LastUpdated column to the current date whenever a row is updated (assuming you have an ID column). The deleted table is a virtual table made available by sqlserver to the trigger. Handling insert statements can be done with a default value instead of a trigger like the following:

    CONSTRAINT DF_TestTable_LastUpdated DEFAULT (getdate()) FOR LastUpdated

This article has pretty good explanations and examples.


Just enable Editor Tracking in you geodatabase. It will add four fields to you feature class

  • created_user
  • created date
  • last_edited_user
  • last_edited_date

This way you don't need to worry about triggers and versioning and you can manage it from within ArcCatalog

  • With the Editor Tracking, how would I track column wise edits. eg geometry vs various attributes.
    – Justin
    Commented Jun 17, 2014 at 23:37

If you are using versioning the test table is not the place to put the trigger. If anywhere this should be on the Adds table. the adds table is named A### where the ### is a number defined by "select registration_id from sde.sde_Table_registry where table_name = 'TestTable'". However be careful with this approach, whilst the trigger you are proposing is fairly benign do not look at updating other tables from a trigger on the A table since until that edit is saved you cannot be sure that it will finally become part of the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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