"mv.gonza...@cir.es" <mv.gonza...@cir.es> wrote: > On table_B appears: > id | situation | when > --------+-----------+--------------------- > 23 | Started | 2013-03-08 12:33:35 > 23 | Started | 2013-03-08 12:33:37 > > I don't understand why after the second update, the insert done on table_B > is incorrect, and has the same values that the first one. > > I've been looking for some kind of explanation like cache or something, but > I couldn't find anything.
It helps to provide a self-containted test case like this: test=# CREATE TABLE table_a (id int PRIMARY KEY, situation text NOT NULL); CREATE TABLE test=# INSERT INTO table_a VALUES (23, 'Unknown'); INSERT 0 1 test=# CREATE TABLE table_b (id int NOT NULL, sitation text NOT NULL, "when" timestamptz NOT NULL); CREATE TABLE test=# CREATE FUNCTION log_func() test-# RETURNS TRIGGER test-# LANGUAGE plpgsql test-# AS $$ test$# BEGIN test$# set datestyle to 'sql'; test$# set datestyle to 'european'; test$# IF (TG_OP = 'UPDATE' and (OLD.situation!=NEW.situation)) THEN test$# INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW()); test$# END IF; test$# RETURN NULL; test$# END; test$# $$; CREATE FUNCTION test=# CREATE TRIGGER log_trig test-# AFTER UPDATE ON table_a test-# FOR EACH ROW EXECUTE PROCEDURE log_func(); CREATE TRIGGER test=# update table_A set situation='Started' where id='23'; UPDATE 1 test=# select * from table_a; id | situation ----+----------- 23 | Started (1 row) test=# select * from table_b; id | sitation | when ----+----------+-------------------------------- 23 | Started | 08/03/2013 13:01:08.169213 CST (1 row) test=# update table_A set situation='Waiting' where id='23'; UPDATE 1 test=# select * from table_a; id | situation ----+----------- 23 | Waiting (1 row) test=# select * from table_b; id | sitation | when ----+----------+-------------------------------- 23 | Started | 08/03/2013 13:01:08.169213 CST 23 | Waiting | 08/03/2013 13:01:08.179754 CST (2 rows) How about showing us yours? -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
