Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 876
Noon Avatar asked Mar 31 '12 10:03

Noon


People also ask

Which type of trigger should run once regardless of number of rows affected?

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

How do triggers work in PostgreSQL?

A PostgreSQL trigger is a function called automatically whenever an event such as an insert, update, or deletion occurs. A PostgreSQL trigger can be defined to fire in the following cases: Before attempting any operation on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted).

What is instead of trigger in PostgreSQL?

On views, triggers can be defined to execute instead of INSERT , UPDATE , or DELETE operations. Such INSTEAD OF triggers are fired once for each row that needs to be modified in the view.


2 Answers

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.

like image 82
A.H. Avatar answered Sep 30 '22 08:09

A.H.


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.

like image 38
a_horse_with_no_name Avatar answered Sep 30 '22 07:09

a_horse_with_no_name