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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Leehan
  • 207
  • 2
  • 15
  • 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? – John K. N. Aug 02 '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 02 '18 at 13:50

1 Answers1

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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 02 '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 02 '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 02 '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 02 '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 02 '18 at 14:36
  • @Leehan: I suggest you start a new question about the first trigger then. With all relevant details, incl. table definitions and all triggers on table1 and table2. Maybe link to this one for context. – Erwin Brandstetter Aug 02 '18 at 14:41