X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BBDD3DBB98 for ; Thu, 24 Nov 2005 03:38:48 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69349-02 for ; Thu, 24 Nov 2005 07:38:48 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195]) by svr1.postgresql.org (Postfix) with ESMTP id 339C5DBC02 for ; Thu, 24 Nov 2005 03:38:45 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i28so1034895wra for ; Wed, 23 Nov 2005 23:38:47 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=F6j4S1p+KwCX5KVlR/pRcq2hScpQdBp1s9O/pxNXfD85Hy57eXu2OK6ougIKWnw4Enu83EtTzB91z8svxGJ07BrrrhmwxysFliF8Mv1fGiqsZ5LecnIq/JkJk5RYXlZ4gND2NnOwYBBj/+rtNA4iknIiFC8HaJNnbapOVaItRyk= Received: by 10.54.66.3 with SMTP id o3mr4734677wra; Wed, 23 Nov 2005 23:38:47 -0800 (PST) Received: by 10.54.117.8 with HTTP; Wed, 23 Nov 2005 23:38:47 -0800 (PST) Message-ID: <9e4684ce0511232338t342ba64ena5a3a14c0cfaa555@mail.gmail.com> Date: Thu, 24 Nov 2005 08:38:47 +0100 From: hubert depesz lubaczewski To: Michael Fuhr Subject: Re: freezing a particular field in a table Cc: fmiddleton@verizon.net, pgsql-admin@postgresql.org In-Reply-To: <20051123175522.GA3828@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8649_24448674.1132817927248" References: <4383A9B7.7060008@verizon.net> <9e4684ce0511230128y77557be3k2507d6eb1575c1e4@mail.gmail.com> <20051123175522.GA3828@winnie.fuhr.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/306 X-Sequence-Number: 19679 ------=_Part_8649_24448674.1132817927248 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 11/23/05, Michael Fuhr wrote: > > IF NEW.some_field IS DISTINCT FROM OLD.some_field THEN > IS DISTINCT FROM is like <> except that it treats NULL as an ordinary > value. thanks - i didn't know about this operator. > CREATE TRIGGER impossible_to_change BEFORE UPDATE ON some_table FOR EACH > ROW > > EXECUTE PROCEDURE impossible_to_change(); > If the table has other triggers and you don't want them to modify > the value either, then the check should probably go in an AFTER > trigger. > i would rather use before anyway. the problem with after is that in aftrer - all you can do is to raise exception. while - if you would like to value to stick - you would ahve to change the code *and* change trigger declaration. putting it in before solves the problem. of course there might be problem with another triggers, but that's another story. depesz ------=_Part_8649_24448674.1132817927248 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 11/23/05, Michael Fuhr <mike@fuhr.org> wrote:
  IF NEW.some_field IS DISTINCT FROM OLD.some_field THEN
IS DISTINC= T FROM is like <> except that it treats NULL as an ordinary
value.=


thanks - i didn't know about this operator.
 

&= gt; CREATE TRIGGER impossible_to_change BEFORE UPDATE ON some_table FOR EAC= H ROW
> EXECUTE PROCEDURE impossible_to_change();
If the table has othe= r triggers and you don't want them to modify
the value either, then the = check should probably go in an AFTER
trigger.

i would rather use before anyway.
the problem with after is that in aftrer - all you can do is to raise exception. while - if you would like to value to stick - you would ahve to change the code *and* change trigger declaration. putting it in before solves the problem.
of course there might be problem with another triggers, but that's another = story.

depesz

------=_Part_8649_24448674.1132817927248--