1

I am looking at creating a trigger on a live SQL Server spatial table that will update a column when a user either:

  1. Creates a new feature
  2. 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
4

Isn't this what you're looking for ?

CREATE TRIGGER [triggername] ON [table] 
AFTER INSERT, UPDATE 
AS            
BEGIN
    UPDATE [table] 
    SET [table].Updated = Getdate() 
    WHERE ID IN (SELECT ID FROM INSERTED)
END
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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