[BUGS] BUG #7924: Trigger update function don't take correct values

[mv . gonzalez]

The following bug has been logged on the website:

Bug reference:  7924
Logged by:  Maria
Email address:  mv.gonza...@cir.es
PostgreSQL version: Unsupported/Unknown
Operating system:   RedHat
Description:

Hi!

I have a trigger that when you update a value on a table, makes an insert
into another table.

The trigger looks like the following:

BEGIN
set datestyle to 'sql';
set datestyle to 'european';
IF (TG_OP = 'UPDATE' and (OLD.situation!=NEW.situation)) THEN
INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW());
END IF;
RETURN NULL;
END;

For exemple, If I do the next querys:
-- update table_A set situation='Started' where id='23';
On table_A appears:
  id| situation 
+---
  23| Started

On table_B appears:
  id| situation | when
+---+-
  23| Started   | 2013-03-08 12:33:35


-- update table_A set situation='Waiting' where id='23';
On table_A appears:
  id| situation 
+---
  23| Waiting

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.

Thanks in advance.

Maria



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

Re: [BUGS] BUG #7924: Trigger update function don't take correct values

[Kevin Grittner]

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