PostgreSQL Trigger and rows updated

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 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.

A.H. · Accepted Answer · 2012-03-31 11:20:08Z

11

OLD and NEW are aliases to the rows which fired the trigger. So when you execute a statement 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

The part 42=42 is always true. So each row in hello_cars is updated.

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 is not equal NEW.ID. What should happen in the table hello_cars in this case? But that's another question.

answered Mar 31, 2012 at 11:20

A.H.

66.8k16 gold badges98 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.H. Over a year ago

@Shadin: You're welcome. Please see the FAQ/How To Ask how to accept the answer which helped you most.

user330315 · Accepted Answer · 2012-03-31 11:15:49Z

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 Mar 31, 2012 at 11:15

user330315

Collectives™ on Stack Overflow

PostgreSQL Trigger and rows updated

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related