1

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.

  • Have you tried simply inserting a value into a temporary table in your function and commenting out the UPDATE... just to see if you receive the NEW.gid value? – hot2use Aug 2 '18 at 13:46
  • 1
    user is a reserved work in PostgreSQL, your unquoted use of it in the function does not refer to the column, but rather the name of the currently logged on user. Either quote it with double quotes, or better yet don't use that as the name of a column at all. – jjanes Aug 2 '18 at 13:50
2

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.

  • Thanks but contributor is still replaced by the user name for all rows...(and I am aware that user is synonym for current_user :)) – Leehan Aug 2 '18 at 14:04
  • @Leehan: The WHERE condition clearly says WHERE table2.gid = NEW.gid. So either all your rows in table2 have the same gid or you are updating multiple rows in table1 (by accident). Or you have additional triggers on table1 or table2? Or the wrong schema name is not just in your question and you are calling a different function than you think you do. – Erwin Brandstetter Aug 2 '18 at 14:06
  • Obviously, gid in table2 are unique because gid is the primary key. For the multiple rows updated, I have others triggers ont table1, maybe there is a "conflict". – Leehan Aug 2 '18 at 14:11
  • 1
    @Leehan: Some other trigger may be the culprit then. What you displayed for trigger trg_maj_table2 needed fixing, but it can't explain the problem you describe. – Erwin Brandstetter Aug 2 '18 at 14:13
  • When I insert a row in table1, a first trigger create a row in table2 as well. If the trigger we are talking about (the second) is disabled then I keep all contributors(=users). IF I enable the second trigger and make the same insert, all contributors are replaced in table2. But if I disable the trigger, make the insert and enable the trigger, there it works well : I can update a row in table1 and contributor in table2 is updated, just this one. – Leehan Aug 2 '18 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.