Re: not null error in trigger on unrelated column

From: CSN <cool_screen_name90001(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: not null error in trigger on unrelated column
Date: 2005-11-22 08:43:48
Message-ID: 20051122084348.23597.qmail@web52902.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I removed the not null contraint on members.admin and "update items set active = false where
member_id=38" results in the count columns in members getting updated AND members.admin getting
set to NULL. Really bizarre.

I dropped the trigger function and the trigger, then recreated both, and now they both appear to
function properly. I don't remember seeing anything in the docs stating that this is necessary, so
I guess perhaps it's a bug?

csn

--- CSN <cool_screen_name90001(at)yahoo(dot)com> wrote:

> I have a members table and an items table. Every time items.member_id or items.active gets
> changed, members.items_submitted and members.items_approved gets updated by a trigger on items.
> I
> added an "admin" column to members, and now this happens:
>
> => update items set active = false where member_id=38;
> ERROR: null value in column "admin" violates not-null constraint
> CONTEXT: SQL statement "update members set items_approved=items_approved-1 where id= $1 "
> PL/pgSQL function "update_member_item_counts" line 54 at SQL statement
>
> The relevant part of the (after) trigger function on items is:
>
> ELSIF OLD.active is true and NEW.active is false then
> update members set
> items_approved=items_approved-1
> where id=NEW.member_id;
> END IF;
>
> Is it necessary to drop and recreate triggers and/or corresponding functions after changing a
> table's schema? I don't know how something is trying to set members.admin to null (table members
> has no triggers).
>
> thanks
> csn
> Postgresql 8.0.x
>
>
>
> __________________________________
> Yahoo! FareChase: Search multiple travel sites in one click.
> http://farechase.yahoo.com
>



__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

Browse pgsql-general by date

  From Date Subject
Next Message Norberto Meijome 2005-11-22 13:04:45 Re: Timestamp with Timezone
Previous Message A.j. Langereis 2005-11-22 08:32:43 Re: Difference in indexes