by David Crowther
Question:
How can I tell which records in my SQL GIS Table have been updated since a specific date?
Answer:
You can use a Trigger!
In this example you may wish to keep a Date field in your Planning Apps table updated with the date that each record in your table was last updated. This could help you provide Change Only Update (COU) files to clients/3rd parties, where you only send them those records that have changes since your last supply.
Follow the steps below to do this yourself…..
Step 1 – Create a Last Updated ‘Date’ field
In Design mode in SSMS add your new field, making the type ‘Date’.
Step 2 – Create the Trigger
Right click on the Table and choose New Trigger.
Ener the SQL to create the trigger… with the values marked in bold italic being variables based on your database, table, and field names.
USE [DATABASE]
GO
/****** Object: Trigger [dbo].[trigger_change_lastupdatedby] Script Date: 15/07/2024 09:57:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trigger_change_lastupdatedby]
ON [dbo].[lcc_planning_apps]
AFTER UPDATE
AS
UPDATE dbo.lcc_planning_apps
SET last_update = CURRENT_TIMESTAMP
WHERE ogr_fid IN (SELECT DISTINCT ogr_fid FROM INSERTED);
GO
ALTER TABLE [dbo].[lcc_planning_apps] ENABLE TRIGGER [trigger_change_lastupdatedby]
GO
… this creates a tigger called trigger_change_lastupdatedby, which activates on an UPDATE to the lcc_planning_apps table.
When an update occurs, e.g. an attribute is changed or the geometry is modified, the last_update field is updated with the Current TIME_STAMP.
Note – by using the Where statement within the trigger – this ensures that the trigger identifies the unique Id (ogr_fid) of the record that has been updated, so only that record has its last_update field updated.
Step 3 – Make a change to the data
Using your GIS, in this case the Arkance webGIS MapThat, we will make a modification to one of the Planning Apps e.g. change its geometry.
… once you save the changes, the trigger on the table will then auto update the last_update field with the CURRENT_TIMESTAMP,.. in this case today’s date.
You can modify the table design and make the last_update a datetime field….
…. and make another change to the Planning Apps in your GIS….
.. and the last_update field is now updated to show the Date and Time that the record was last updated!
Step 4 – Select your Data
Using SSMS and a simple SQL query as below, you can then use the last_update field to identify all the records that have last been updated since a specific Date and Time!
Triggers are a great way to make automatic updates allowing you to manage your GIS datasets more easily. Here are some other useful triggers that you could use:
- Case When on Insert - https://www.cadlinecommunity.co.uk/hc/en-us/articles/9964559589533-SQL-CASE-WHEN-on-INSERT-Only
- Case When on Insert (Multiple Fields) – https://www.cadlinecommunity.co.uk/hc/en-us/articles/9965328743069-SQL-CASE-WHEN-on-Multiple-Fields
- Update Points in Polygon - https://www.cadlinecommunity.co.uk/hc/en-us/articles/9990415672605-SQL-Update-Points-using-Polygons
- Update to UK Date - https://www.cadlinecommunity.co.uk/hc/en-us/articles/12450683042717-SQL-Update-Date-to-UK-Format