Triggers on Insert or Update in SQL Server (Spatial)

spatialsql server

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

Best Answer

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

Related Solutions

Sql-server – SQL Server Trigger does not update all table records

Currently, your trigger is updating all rows. Modify it to use the inserted virtual table, something like this:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
ON [dbo].[Plot_Location]
AFTER INSERT
AS 
BEGIN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE EXISTS (
            SELECT 1 
            FROM inserted i 
            WHERE i.Invent_Plot_ID = Plot_Location.Invent_Plot_ID
        )
        AND Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID;
END

This may not be exactly correct; it's hard to tell exactly where to join the inserted virtual table with your other tables. Essentially, the idea is the inserted virtual table only contains rows that were either inserted or updated in the Plot_Location table.

You shouldn't check the @@ROWCOUNT system function at the start. It may not reflect the rowcount you think it does. Instead you should select the count of rows in the inserted table. Like this:

DECLARE @rc INT;
SELECT @rc = COUNT(1)
FROM inserted;

IF @rc > 0
BEGIN
    --body of trigger  goes here.
END

SQL Server – How to Auto Update Column

You need to update the defaults column by using an insert trigger on this table. The only problem with it would be that your trigger will fail on multirow-insert. There is a correction in the WHERE clause you are using. Following is the code you should use:

UPDATE FYPDB.dbo.Promotions 
SET defaults = 'N' 
WHERE ID NOT IN (SELECT ID FROM Inserted) AND membershipType IN (SELECT membershipType FROM Inserted) AND EXISTS (SELECT * FROM Inserted WHERE defaults = 'Y'); 
UPDATE FYPDB.dbo.Promotions 
SET defaults = 'Y' 
WHERE ID IN (SELECT ID FROM Inserted) AND NOT EXISTS (SELECT * FROM FYPDB.dbo.Promotions p WHERE p.defaults = 'Y' and p.membershipType in (SELECT membershipType FROM Inserted));

Related Question