I work with Postgresql 13, Postgis (and Qgis 3.22.) My need is to set up within my Postgis database (used for maps production), triggers for automation of data historization/versioning. The idea: For instance within a table 'BOREHOLE' (geometry: points), different updates of the table are made over time.
The version change occurs in the event that: • new objects are added or deleted in the table (INSERT / DELETE) • objects are updated by new versions of objects canceling and replacing existing objects (UPDATE). Sometimes, these UPDATE only concern the geometry column (change of location). Sometimes other fields/attributes (type of borehole, technical referent, name of the campaign for the borehole, start date of construction, name of the municipality where the borehole is located, etc.).
=> These INSERT/DELETE/UPDATE must have the effect, within the "archive" schema (schema dedicated to the storage of historical/versioned tables/objects), the creation of a new table "BOREHOLE_V1", " BOREHOLE _V2", " BOREHOLE _V3",etc.
Requirements: • The objective is NOT to perform a version upgrade at each modification (UPDATE) in the table, but to identify structuring phases. In other words, the idea is that the administrator can decide, trigger when he deems relevant, the trigger/history-versioning process.
• "Table versioning" way is recommended": the versioning tables (ARCHIVE schema) will be made up of modified elements AND also unmodified objects from the "BOREHOLE" table. This choice was done in particular to facilitate the restoration of versioned objects at the level of archived qgis projects. Advantage: possibility of referring to a specific phase of the Instant T project. Disadvantage: duplication of objects even unmodified within the archive table.
The administrator could activate a historization action when it seems relevant to him. For example at the end of an Qgis editing session.
Could someone guide me, direct me to the methods offered by PostgreSQL/Postgis (or even Qgis) likely to satisfy my needs? A big thank-you. ;-) Hoping to have been sufficiently clear.
