2

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

  1. Creates a new feature

  2. Updates the geometry of a feature.

Unfortunately I have only been able to work out updating a field when a new feature is created, or updating a field for every record when the geometry of a record is changed.

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
2

Here is a basic trigger that will get fired when a record gets updated:

CREATE TRIGGER dbo.trg_MY_TABLE_OnUpdate
   ON dbo.MY_TABLE
AFTER UPDATE
AS 
Begin
   Update dbo.MY_TABLE
      Set   DATE_CHANGED = CURRENT_TIMESTAMP,
            USER_CHANGED = SYSTEM_USER
      Where MI_PRINX In (Select MI_PRINX From inserted);
End
GO

and a similar one for a record being inserted

CREATE TRIGGER dbo.trg_MY_TABLE_OnInsert
   ON dbo. MY_TABLE
AFTER INSERT
AS 
Begin
   Update dbo.MY_TABLE
      Set   DATE_CREATED = CURRENT_TIMESTAMP,
            USER_CREATED = SYSTEM_USER
      Where MI_PRINX In (Select MI_PRINX From inserted);
End
GO

In the samples above I'm not checking whether the spatial object was edited or not. You can use the UPDATE() function to check whether a certain column has been changed, see more here: https://msdn.microsoft.com/en-us/library/ms187326.aspx

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.