Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, as all of the triggers occur twice.
I first realized this because the history table inserts lines for the 'INSERT' and UPDATE
logic twice (not for DELETE
, surprisingly). The notices print to the console twice for INSERT
, UPDATE
, and DELETE
.
For example:
When creating a line in the base table, the history table creates two objects with the same attributes except for the hid (history table id).
When updating the attribute of an existing line, in the history table, three objects are created:
- One with
etat = 'MODIFICATION COURANTE'
(resembles the existing feature in the base table, including the updated attribute) - Two with
etat = 'MODIFICATION ARCHIVEE'
-- one with the old attribute value and one with the new attribute value. At this point, I am expecting only one object containing the old attribute. The object containing the new attribute is out if place.
- One with
CREATE or REPLACE FUNCTION test.test_track_history_tracker() RETURNS trigger AS
$new_test_track_history_tracker$
BEGIN
-- INSERT
IF (TG_OP = 'INSERT') THEN
INSERT INTO test."Conduite_test_history"
(diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
VALUES
(NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
current_user, FALSE, 'CREATION', NEW.geom);
raise notice 'Insert happened';
RETURN NEW;
-- UPDATE
ELSEIF (TG_OP = 'UPDATE') THEN
UPDATE test."Conduite_test_history"
SET deleted = current_timestamp, deleted_by = current_user, modified = TRUE, etat = 'MODIFICATION ARCHIVEE'
WHERE deleted IS NULL and gid_org = OLD.gid;
INSERT INTO test."Conduite_test_history"
(diametre, type, materiau, origininfo, dtreno, dtpose, reparateur, dt_dmd, id_sig, gid_org, created, created_by, modified, etat, geom)
VALUES
(NEW.diametre, NEW.type, NEW.materiau, NEW.origininfo, NEW.dtreno, NEW.dtpose, NEW.reparateur, NEW.dt_dmd, NEW.id_sig, NEW.gid, current_timestamp,
current_user, FALSE, 'MODIFICATION COURANTE', NEW.geom);
raise notice 'Update happened';
RETURN NEW;
-- DELETE
ELSEIF (TG_OP = 'DELETE') THEN
UPDATE test."Conduite_test_history"
SET deleted = current_timestamp,
deleted_by = current_user,
etat = 'SUPPRESSION'
WHERE deleted is NULL and gid_org = OLD.gid;
raise notice 'Delete happened';
RETURN NULL;
END IF;
END;
$new_test_track_history_tracker$
LANGUAGE plpgsql;
--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_test_track_history_tracker ON test."Conduite_test";
CREATE TRIGGER trg_test_track_history_tracker
AFTER INSERT OR UPDATE OR DELETE ON test."Conduite_test"
FOR EACH ROW EXECUTE PROCEDURE test.test_track_history_tracker();