Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

>CREATE TRIGGER t_I_TestTable
>ON dbo.TestTable
>FOR INSERT
>AS
>
>UPDATE TestTable SET TestTable.LastUpdated = Getdate()
share|improve this question
    
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 Sep 13 '11 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 Sep 13 '11 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 Sep 13 '11 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 Sep 13 '11 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 Sep 13 '11 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            
BEGIN
    UPDATE TestTable SET TestTable.LastUpdated = Getdate()
END
GO

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

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

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:

ALTER TABLE TestTable ADD 
    CONSTRAINT DF_TestTable_LastUpdated DEFAULT (getdate()) FOR LastUpdated
GO 

This article has pretty good explanations and examples.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
With the Editor Tracking, how would I track column wise edits. eg geometry vs various attributes. –  Justin Jun 17 at 23:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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