I am looking at creating a trigger on a live SQL Server spatial table that will update a column when a user either:
- Creates a new feature
- Updates the geometry of a feature in a GIS application
Unfortunately I have only been able to work out updating a column when a new feature is created, or updating a column when the geometry column is changed, but not both within one trigger.
Ideal world - attribute
Updated populates current date for the particular feature that has been created or modified.
This is what I have so far that works only when creating a new feature:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER *[triggername]* ON *[table]* AFTER INSERT,UPDATE AS BEGIN DECLARE @ID VARCHAR(8) SELECT @ID = [ID] FROM *[table]* UPDATE *[table]* SET Updated = GETDATE() WHERE [ID] = @ID END GO
This works when the geometry is changed
CREATE TRIGGER [triggername] ON [table] FOR INSERT, UPDATE AS BEGIN UPDATE [table] SET [table].Updated = Getdate() FROM [table] INNER JOIN deleted d ON [table].id = d.id END GO