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 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.
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).
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.
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.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With