Postgresql – Trigger to cascade updates to a second table

postgresqltriggerupdate

In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified.

Function:

CREATE OR REPLACE FUNCTION schema.maj_tbl2()
RETURNS trigger AS
$BODY$

begin
UPDATE schema.table2
SET (contributor, code_tranche) = (user, table1.code_tranche) from schema.table1
where NEW.gid = table2.gid;
return NEW;
END;

$BODY$

Trigger:

CREATE TRIGGER trg_maj_table2
BEFORE UPDATE
ON schema.table1
FOR EACH ROW
EXECUTE PROCEDURE schema.maj_tbl2();

The 2 fields in table2 are modified the same way but for the entire table with the sames values. I can't update just the row in table2 concerned by the update in table1. I feel something is wrong with the where condition but can't fix it.

Best Answer

While this trigger function is expensively incorrect and needs to be fixed, it can't explain the problem you describe. There must be some other cause, not evident from your question, yet. Some other broken trigger on table1 or table2?

This trigger updated the same row in table2 once for every row in table1 and the final value of table2.code_tranche was an arbitrary pick from table1.

Remove from schema.table1:

UPDATE schema.table2
SET   (contributor, code_tranche) = (user, NEW.code_tranche) from schema.table1
WHERE  table2.gid = NEW.gid;

And I assume you are aware that user is s synonym for current_user, returning the user name of current execution context. If it's supposed to be a column name, you have to use NEW."user" instead. (But never use reserved words as identifiers to begin with - like jjanes commented.)

Typically, this kind of trigger indicates a problem with your db design. Try to remove redundancy. Storing information in one table should suffice.

Related Question