[GENERAL] PostgreSQL Trigger and rows updated

2012-03-31 Thread Albert 
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 cars table updated, the hello_cars table
updated but status column in each row is updated and contains same new
status ! it must be updated according to car ID.
i think my problem is in condition : WHERE OLD.ID = NEW.ID;
but can't tell what's wrong exactly

Thanks in advanced

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PostgreSQL-Trigger-and-rows-updated-tp5608591p5608591.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL Trigger and rows updated

2012-03-31 Thread Daniel Verite 
Albert wrote:

 UPDATE hello_cars SET status = new.status 
 WHERE OLD.ID = NEW.ID;

[...]

 the trigger works fine. when cars table updated, the hello_cars table
 updated but status column in each row is updated and contains same new
 status ! it must be updated according to car ID.
 i think my problem is in condition : WHERE OLD.ID = NEW.ID;
 but can't tell what's wrong exactly

In this context, OLD.ID and NEW.ID are just variables, and they happen to
have the same value, unless ID is modified.
So the update quoted above is equivalent to:
 UPDATE hello_cars SET status=new.status WHERE value=value
which indeed updates all the rows, not what you want.

The fix would be:
 UPDATE hello_cars SET status=new.status WHERE car_id=NEW.ID;

Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL Trigger and rows updated

2012-03-31 Thread Albert 
Thanks you so much!
it works great now

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PostgreSQL-Trigger-and-rows-updated-tp5608591p5609895.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general