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
