Received: from ccis01.baylor.edu (ccis01.baylor.edu [129.62.1.18]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB6KR2m97704 for ; Thu, 6 Dec 2001 15:27:02 -0500 (EST) (envelope-from thornton@cs.baylor.edu) Received: from cs.baylor.edu (ppp-208-189-124-23.dialup.wacotx.swbell.net [208.189.124.23]) by ccis01.baylor.edu (8.9.1/8.9.1) with ESMTP id OAA1249841; Thu, 6 Dec 2001 14:26:54 -0600 (CST) Message-ID: <3C0CBA36.8B0207AF@cs.baylor.edu> Date: Tue, 04 Dec 2001 05:57:42 -0600 From: James Thornton X-Mailer: Mozilla 4.76C-SGI [en] (X11; I; IRIX 6.5 IP32) X-Accept-Language: en MIME-Version: 1.0 To: Gregory Wood CC: PostgreSQL-General Subject: Re: update returns 1, but no changes have been made References: <3C08A694.86E4153B@cs.baylor.edu> <004301c17e83$365a07d0$7889ffcc@comstock.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200112/291 X-Sequence-Number: 18393 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