Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

Question

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()

Answer 1

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.

Answer 2

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

Answer 3

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.