Trigger on 2 tables : issue with INSERT and UPDATE

Question

This is related to my previous question:
Trigger to cascade updates to a second table.

In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields in metadonnees (formerly table2). Merged like this now:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
RETURNS trigger AS
$BODY$
begin

IF (TG_OP = 'INSERT') THEN
    insert into activite.metadonnees (gid, date_publication, contributor, code_tranche)
    VALUES (new.gid, current_date, current_user, NEW.code_tranche)
    ;
    return new ;

elseif (TG_OP = 'DELETE') THEN
DELETE from activite.metadonnees
where gid = old.gid ;
return old ;

elsif (TG_OP = 'UPDATE') THEN 
UPDATE activite.metadonnees 
SET (contributor, code_tranche) = (current_user, NEW.code_tranche)
where metadonnees.gid = new.gid
;
return new ;
end if ;

end;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;


CREATE TRIGGER trg_tbl_emprise_metadonnees
AFTER INSERT OR DELETE OR UPDATE
ON activite.emprise
FOR EACH ROW
EXECUTE PROCEDURE activite.tbl_emprise_metadonnees();

Table definitions:

CREATE TABLE activite.emprise
(gid integer NOT NULL DEFAULT PRIMARY KEY nextval('activite.emprise_gid_seq'::regclass),
surface numeric(9,2),
annee integer,
ro character varying(50),
numope character varying(12),
typope character varying(25),
geom geometry(MultiPolygon,2154),
typemp character varying(30),
nomope character varying(80),
numoa character varying(20),
numprescr character varying(25),
tranche integer DEFAULT 1,
code_tranche character varying(15),
producteur character varying(15),
notice_rapport character varying(50)

CREATE TABLE activite.metadonnees
(gid integer NOT NULL PRIMARY KEY,
date_publication date,
"date_création" date,
"généalogie" character varying(250),
"résolution_spatiale" character varying(5), 
responsable character varying(10),
restrictions character varying(100),
source character varying(15),
creator character varying(50),
publisher character varying(80),
identifier character varying(50),
title character varying(80),
subject character varying,
code_tranche character varying(15),
contributor character varying,
dates character varying,
type_donnees character,
format character varying,  
language character varying, 
coverage character varying

If I use AFTER INSERT OR DELETE OR UPDATE trigger then when a record is inserted into emprise, the field contributor is overwritten by the current_user value for all records.

If the trigger is limited to AFTER INSERT OR DELETE then after insertion everything goes well: the field contributor is not replaced for all records (as desired), just the record concerned in emprise.

In both cases, the contributor and code_tranche are updated if, after insertion, code_tranche value is changed in table emprise. I will conclude that UPDATE is the cause of this dysfunction but I am unable to find why.

Answer 1

Your trigger function should work and cannot cause the described problem on its own. Moreover, this does not make any sense at all:

If the ON UPDATE trigger is active then when a record is inserted into emprise, the field contributor is overwritten by the current_user value for all records.

UPDATE triggers are not fired on INSERT. Maybe just wrong terms?

And you mentioned primary keys before, but I don't see them here. Anything else "simplified"? You need to be precise to pin down the problem ...

Only a couple of simplifications, optimizations and a minor fix:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
  RETURNS trigger AS
$func$
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN
      INSERT INTO activite.metadonnees (gid, date_publication, contributor, code_tranche)
      VALUES (NEW.gid, current_date, current_user, NEW.code_tranche);

   WHEN 'DELETE' THEN
      DELETE FROM activite.metadonnees
      WHERE  gid = OLD.gid 

   WHEN 'UPDATE' THEN 
      UPDATE activite.metadonnees 
      SET   (gid, contributor, code_tranche) = (NEW.gid, current_user, NEW.code_tranche)
      WHERE  metadonnees.gid = OLD.gid
      AND   (gid, contributor, code_tranche)  -- only update if anything changes
            IS DISTINCT FROM (NEW.gid, current_user, NEW.code_tranche);
   ELSE
      -- do nothing (should not occur)
   END CASE;

   RETURN NULL;  -- for AFTER trigger
END
$func$  LANGUAGE plpgsql;

Note how I base the UPDATE on OLD.gid. It would be slightly incorrect to base it on NEW.gid and possibly miss changes to that column.

You can simplify RETURN because, quoting the manual:

The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

And CASE is a bit shorter and faster.

The root of your problem is still burried elsewhere.

Aside from that, a clean solution would be to remove redundant storage (if at all possible). Use a FK constraint with ON UPDATE CASCADE ON DELETE CASCADE. Code examples:

• Cascade deletes from multiple tables
• How to implement a many-to-many relationship in PostgreSQL?

And store contributor and code_tranche in table emprise once per entity. Then you need no trigger on UPDATE or DELETE, just on INSERT. Or even merge the two tables (which seem to be in 1:1 relation). Then you need neither triggers nor FK constraints. NULL storage is cheap - in case you are worried about many null columns in the one table.

• Do nullable columns occupy additional space in PostgreSQL?