[GIS] Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

enterprise-geodatabasesqlsql servertrigger

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

Best Answer

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.

Related Question