pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: James Thornton <thornton@cs.baylor.edu>
To: Gregory Wood <gregw@com-stock.com>
Cc: PostgreSQL-General <pgsql-general@postgresql.org>
Subject: Re: update returns 1, but no changes have been made
Date: Tue, 04 Dec 2001 05:57:42 -0600
Message-ID: <3C0CBA36.8B0207AF@cs.baylor.edu> (raw)
References: <3C08A694.86E4153B@cs.baylor.edu>
	<004301c17e83$365a07d0$7889ffcc@comstock.com>

Gregory Wood wrote:
> 
> Do you perhaps have any triggers or rules on the table in question? If one
> of those is intercepting your UPDATE then the changes may not actually get
> to the database...

Yes -- there is a trigger on this table that updates an audit table, and
the audit table *IS* being updated, albeit with the same old values
every time. This is my first time writing triggers in Postgres so that's
probably where the error is.

Please let me know if you see anything. Here's the table and the
trigger:

create table wpp_product_question (
      product_question_id int primary key default
nextval('wpp_product_question_seq'::text),
      product_type_id     int not null references wpp_product_type,
      product_faq_id      int references wpp_product_faq,
      product_question    varchar(4000) not null,
      sort_order          int,          
      -- notes only the editor will see
      editor_notes        varchar(4000),
      approved_p          char(1) check (approved_p in ('f','t'))
default 'f',
      last_modified      datetime not null,
      last_modifying_user int not null references users,
      modified_ip_address varchar(50) not null,
      unique (product_type_id,product_question) 

);

create table wpp_product_question_audit as
       select * from wpp_product_question where 1 = 0;

alter table wpp_product_question_audit add delete_p char(1) check
(delete_p in ('f','t')) default 'f';

drop function wpp_product_question_audit_fn();
create function wpp_product_question_audit_fn() returns opaque
as '
declare
begin
        insert into wpp_product_question_audit (
        product_question_id, product_type_id, product_faq_id,
product_question,
        sort_order, editor_notes, approved_p, last_modified,
last_modifying_user, 
        modified_ip_address
        ) values (
        OLD.product_question_id, OLD.product_type_id,
OLD.product_faq_id, OLD.product_question,
        OLD.sort_order, OLD.editor_notes, OLD.approved_p,
OLD.last_modified, OLD.last_modifying_user, 
        OLD.modified_ip_address
        );

return old;

end;
' language 'plpgsql';

drop trigger wpp_product_question_audit_tr on wpp_product_question;
create trigger wpp_product_question_audit_tr
before update or delete on wpp_product_question
for each row execute procedure wpp_product_question_audit_fn();

Thanks.

JT



view thread (5+ messages)  latest in thread

Message-ID: <3C0CBA36.8B0207AF@cs.baylor.edu>
Permalink:  ../3C0CBA36.8B0207AF@cs.baylor.edu/
Also on:    postgresql.org/message-id/3C0CBA36.8B0207AF@cs.baylor.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: thornton@cs.baylor.edu, gregw@com-stock.com, pgsql-general@postgresql.org
  Subject: Re: update returns 1, but no changes have been made
  In-Reply-To: <3C0CBA36.8B0207AF@cs.baylor.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox