Alex Rivera | Logout

PostgreSQL Trigger and rows updated

Asked 2012-03-31T10:36:51.070
9

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 works fine. When the cars table is updated, the hello_cars table is updated but the status column in each row is updated and contains same new status! It must be updated according to a car ID.
I think my problem is in condition: WHERE OLD.ID = NEW.ID; but I can't tell what's wrong.

Thanks in advance.

postgresql triggers
Edit
Report

1 Answer

6

OLD.ID and NEW.ID are referencing values in the updated row of the table cars and thus (unless you change the ID in cars) will always evaluate to true and therefor all rows in hello_cars are updated.

I think you probably want:

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

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

answered 2012-03-31T11:15:49.757

Your Answer