PostgreSQL Trigger and Updated Rows

Question

I am trying to update a table according to this trigger:

CREATE TRIGGER alert AFTER UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE update_cars(); 

Trigger function:

 CREATE FUNCTION update_cars() RETURNS 'TRIGGER' AS $BODY$ BEGIN IF (TG_OP = 'UPDATE') THEN UPDATE hello_cars SET status = new.status WHERE OLD.ID = NEW.ID; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; 

The trigger is working fine. When the cars table is updated, the hello_cars table hello_cars updated, but the status column on each row is updated and contains the same new status! It must be updated in accordance with the vehicle identifier.
I think my problem is able to: WHERE OLD.ID = NEW.ID; but I can’t say what is wrong.

Thanks in advance.

Answer 1

OLD and NEW are aliases for strings that trigger a trigger. Therefore, when you execute an operator like

 UPDATE cars SET status='xyz' WHERE cars.id = 42; 

then the trigger function will execute

 UPDATE hello_cars SET status='xyz' WHERE 42 = 42 

Part 42=42 always true. Therefore, each line in hello_cars updated.

Do you really want something like

  [...]WHERE hello_cars.id = OLD.ID 

or a little shorter

  [...]WHERE id = OLD.ID 

But you also need to think about what happens if the initial update changes cars.id In this case, OLD.ID not equal to NEW.ID What should happen in the hello_cars table in this case? But this is another question.

Answer 2

OLD.ID and NEW.ID refer to the values ​​in the updated row of the cars table, and therefore (if you do not change the identifier in cars ), true will always be evaluated and, therefore, all rows in hello_cars are updated.

I guess you probably want to:

 UPDATE hello_cars SET status = new.status WHERE id = new.id; 

This assumes that there is an id column in the hello_cars table that matches the id in cars .