Re: update returns 1, but no changes have been made - Mailing list pgsql-general

The big problem I see is:

return old;

If you return old from an UPDATE, no changes are made (it takes the old
values and puts them into the database rather than your new values). Of
course you need to return old from a DELETE (new doesn't exist in a delete
trigger), so I would make a conditional:

IF TG_OP=''UPDATE'' THEN
  RETURN NEW;
ELSE IF TG_OP=''DELETE'' THEN
  RETURN OLD;
END IF;

Hope this helps,

Greg

----- Original Message -----
From: "James Thornton" <thornton@cs.baylor.edu>
To: "Gregory Wood" <gregw@com-stock.com>
Cc: "PostgreSQL-General" <pgsql-general@postgresql.org>
Sent: Tuesday, December 04, 2001 6:57 AM
Subject: Re: [GENERAL] update returns 1, but no changes have been made


> 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
>