CODEWITHSUNDEEP
postgresqltriggers
Noon
Noon

Reputation: 1211

PostgreSQL Trigger and rows updated

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.

Upvotes: 9

Views: 18758

Answers (2)

A.H.
A.H.

Reputation: 66223

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.

Upvotes: 11

user330315
user330315

Reputation:

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.

Upvotes: 6

Related Questions