Features can be archived following alterations to geometry or attributes by creating an archive table and a set of triggers in the SQL Server or PostGIS database using SQL Server or PostGIS management tools.
Creating archive tables
It is recommended that an archive schema is created in the database. An archive table should be created in the archive schema for each live table where versioning is required. The archive table should include all fields from the live table plus the following additional fields to provide a data audit trail:
| Field |
Type |
Description |
| ID |
Identity |
Unique identifier for the record |
| archive_action |
Char(6) |
Insert, Update or Delete |
| archive_user |
Char(50) |
Login used to modify table |
| archive_datetime |
datetime |
Date and time of modification |
Creating triggers
Three triggers should be created for the live table to archive records after Insert, Update and Delete actions.
For each trigger, all fields from the live table plus the audit trail fields are inserted into the archive table.
Live tables that were imported into the database by GISquirrel will contain GISquirrel audit fields. The asq_lockedby field is used by GISquirrel to handle multi-user editing via GISquirrel. This fields is updated at the start and end of each Edit session. The Update trigger must therefore exclude any records where only the asq_lockedby field was updated and archive the remaining rows. The feature ID for each record should be inserted into a temporary table where either the geometry or other attributes have changed. This temporary table is then used to limit the rows that are inserted into the archive table.
Template Scripts
A set of template SQL scripts to create an archive table and its associated triggers in SQL Server are available on the Download page. Each template contains comments to indicate the elements that must be configured before the script is run.
Modified 7/30/2014 by Claire Lush