[postgis-users] Trigger on PostGIS line layer occurs twice

[Quynh Nhu Mai]

Using this
<http://twiav-tt.blogspot.com/2013/04/postgis-trigger-function-keeping-track.html>
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:
1) One with field 'etat' = 'MODIFICATION COURANTE' (resembles the existing
feature in the base table, including the updated attribute)
2) Two with field '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.

--CREATE TRIGGERCREATE 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 TRIGGERDROP 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();
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190703/55bdceac/attachment.html>