Обсуждение: Can update triggers detect the column in the update statement?

Say I have:

create t (c1 int not null, c2 int);

Is it possible to create an update trigger on t such updates will only
be allowed if the update statement explicitly sets c1, even if the new
value is the same?


(I'm trying to write a change log system where the users are
application users, not database users.)

Ben Chobot <bench@silentmedia.com> writes:
> Say I have:
> create t (c1 int not null, c2 int);

> Is it possible to create an update trigger on t such updates will only
> be allowed if the update statement explicitly sets c1, even if the new
> value is the same?

No.  A trigger can only tell whether the old and new values are the same
or not, not why they are that way.

            regards, tom lane

On Oct 6, 2008, at 5:53 PM, Tom Lane wrote:

> Ben Chobot <bench@silentmedia.com> writes:
>> Say I have:
>> create t (c1 int not null, c2 int);
>
>> Is it possible to create an update trigger on t such updates will
>> only
>> be allowed if the update statement explicitly sets c1, even if the
>> new
>> value is the same?
>
> No.  A trigger can only tell whether the old and new values are the
> same
> or not, not why they are that way.

OK, well, that makes sense.

Is there a way I could do this with rules instead? I was thinking to
maybe have a rule to set c1 to be null on update to t, and then let
the normal update happen, and then let the not null constraint kick in
if it needs to. But when I tried to implement this idea with my
limited understanding of rules, it led to infinite recursion. :(

hi ben

trigger happens only a with dml update (you have to change something in the DB for the trigger to do its work)

Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> From: bench@silentmedia.com
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Can update triggers detect the column in the update statement?
> Date: Mon, 6 Oct 2008 17:24:37 -0700
>
> Say I have:
>
> create t (c1 int not null, c2 int);
>
> Is it possible to create an update trigger on t such updates will only
> be allowed if the update statement explicitly sets c1, even if the new
> value is the same?
>
>
> (I'm trying to write a change log system where the users are
> application users, not database users.)
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Stay up to date on your PC, the Web, and your mobile phone with Windows Live. See Now