Postgresql – Trigger on PostGIS line layer occurs twice

plpgsqlpostgresqltrigger

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:

    1. One with etat = 'MODIFICATION COURANTE' (resembles the existing feature in the base table, including the updated attribute)
    2. 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. 
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();

Best Answer

You are using an AFTER trigger - thus any action will have already taken place and the UPDATE will actually run twice: once the for the "original" statement and then again from within your trigger.

It seems to be, you actually want a BEFORE trigger. And there you just assign values to the NEW row, rather than UPDATEing the table itself.

So I think what you are looking for is something like this: 

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';

     -- continue with the INSERT
     RETURN NEW;

  -- UPDATE
  ELSEIF (TG_OP = 'UPDATE') THEN
     -- this IF is equivalent to your WHERE statement 
     IF new.deleted IS NULL THEN 
       new.deleted := current_timestamp;
       new.deleted_by := current_user;
       new.modified := true; 
       new.eta := 'MODIFICATION ARCHIVEE';
     END IF;

     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; -- continue with the UPDATE
  -- DELETE
  ELSEIF (TG_OP = 'DELETE') THEN

     -- this is correct: it will turn the DELETE into an UPDATE
     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; -- this will prevent the actual DELETE in t
  END IF;
END;
$new_test_track_history_tracker$ 
LANGUAGE plpgsql;

And then create a BEFORE trigger using that trigger function:


--ADD TRIGGER
DROP TRIGGER IF EXISTS trg_test_track_history_tracker ON test."Conduite_test";
CREATE TRIGGER trg_test_track_history_tracker 
  BEFORE 
       INSERT OR UPDATE OR DELETE ON test."Conduite_test"
  FOR EACH ROW EXECUTE PROCEDURE test.test_track_history_tracker();
Related Question