Can update triggers detect the column in the update statement?

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.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/