pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to notice column changes in trigger
9+ messages / 5 participants
[nested] [flat]
* How to notice column changes in trigger
@ 2003-03-07 10:30 Andreas Pflug <Andreas.Pflug@web.de>
2003-03-07 17:41 ` Re: How to notice column changes in trigger Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Pflug @ 2003-03-07 10:30 UTC (permalink / raw)
To: pgsql-sql
How can I detect whether a column was changed by an update command
inside a trigger?
create table test(a int, b int, c int, primary key(a))
b and c should be updated inside an update trigger if not modified by
the statement itself
1) update test set a=0 -> trigger does its work
2) update test set a=0, b=1, c=2 -> trigger does nothing
3) update test set a=0, b=b, c=c -> trigger does nothing, but content of
a and b dont change either although touched
What I'm looking for is something like
IF NOT COLUMN_TOUCHED(b) THEN ...
For MSSQL, this would be coded as IF NOT UPDATE(b) ..
IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger code.
Any hints?
Andreas
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
2003-03-07 10:30 How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
@ 2003-03-07 17:41 ` Josh Berkus <josh@agliodbs.com>
2003-03-07 18:10 ` Re: How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
2003-03-31 15:49 ` Re: How to notice column changes in trigger Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 9+ messages in thread
From: Josh Berkus @ 2003-03-07 17:41 UTC (permalink / raw)
To: Andreas Pflug <Andreas.Pflug@web.de>; pgsql-sql
Andreas,
> 1) update test set a=0 -> trigger does its work
> 2) update test set a=0, b=1, c=2 -> trigger does nothing
> 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of
> a and b dont change either although touched
> IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger
> code.
I still don't get why you'd want to do this. Can you provide are real-world
example where there is a difference between setting B=B and not updating B?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
2003-03-07 10:30 How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
2003-03-07 17:41 ` Re: How to notice column changes in trigger Josh Berkus <josh@agliodbs.com>
@ 2003-03-07 18:10 ` Andreas Pflug <Andreas.Pflug@web.de>
1 sibling, 0 replies; 9+ messages in thread
From: Andreas Pflug @ 2003-03-07 18:10 UTC (permalink / raw)
To: pgsql-sql
Josh Berkus wrote:
>Andreas,
>
>
>
>>1) update test set a=0 -> trigger does its work
>>2) update test set a=0, b=1, c=2 -> trigger does nothing
>>3) update test set a=0, b=b, c=c -> trigger does nothing, but content of
>>a and b dont change either although touched
>>
>>
>
>
>
>>IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger
>>code.
>>
>>
>
>I still don't get why you'd want to do this. Can you provide are real-world
>example where there is a difference between setting B=B and not updating B?
>
>
>
I do.
It's about tracking changing user and timestamp. Normally, all inserts
and updates won't touch the b and c columns, and they are handled using
triggers and/or default values.But sometimes, I'd like to update some
columns (other than b or c) WITHOUT having changed b or c. This could
happen for some import tasks, for example. In this case, the
user/timestamp from the exporting database should be replicated, not
altered. In other cases, some maintenance procedure should calculate a
column different from b and c, which would lead to timestamp set to lets
say midnight, and the user to "serviceProcess" which would destroy the
original data. In this case, I'd set b and c to the original data.
That's the way I can handle this with MSSQL.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
2003-03-07 10:30 How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
2003-03-07 17:41 ` Re: How to notice column changes in trigger Josh Berkus <josh@agliodbs.com>
@ 2003-03-31 15:49 ` Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 9+ messages in thread
From: Jan Wieck @ 2003-03-31 15:49 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Andreas Pflug <Andreas.Pflug@web.de>; pgsql-sql
Josh Berkus wrote:
>
> Andreas,
>
> > 1) update test set a=0 -> trigger does its work
> > 2) update test set a=0, b=1, c=2 -> trigger does nothing
> > 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of
> > a and b dont change either although touched
>
> > IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger
> > code.
>
> I still don't get why you'd want to do this. Can you provide are real-world
> example where there is a difference between setting B=B and not updating B?
Setting b=b and not updating it is indeed identical ... at least after
the targetlist completion in PostgreSQL. But it is different from
b=<b's-old-value>, and the fact that we cannot distinguish between these
two (inside the trigger) prevents us from skipping foreign key checks if
your fk-values haven't been touched.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
@ 2003-03-07 12:04 Christoph Haller <ch@rodos.fzk.de>
2003-03-07 16:25 ` Re: How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 9+ messages in thread
From: Christoph Haller @ 2003-03-07 12:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas.Pflug@web.de
>
> How can I detect whether a column was changed by an update command
> inside a trigger?
>
> create table test(a int, b int, c int, primary key(a))
>
> b and c should be updated inside an update trigger if not modified by
> the statement itself
>
> 1) update test set a=0 -> trigger does its work
> 2) update test set a=0, b=1, c=2 -> trigger does nothing
> 3) update test set a=0, b=b, c=c -> trigger does nothing, but content
of
> a and b dont change either although touched
>
> What I'm looking for is something like
> IF NOT COLUMN_TOUCHED(b) THEN ...
> For MSSQL, this would be coded as IF NOT UPDATE(b) ..
>
> IF OLD.b=NEW.b will not work, case 3) will falsely execute the trigger
code.
>
Sorry, but I don't get it.
Does
> b and c should be updated inside an update trigger if not modified by
> the statement itself
mean b=b not modified, b=1 modified even if b was 1 before ?
So you want the trigger executed only if assignments to b and/or c do
not appear within the update command. Right?
Regards, Christoph
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
2003-03-07 12:04 Re: How to notice column changes in trigger Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-07 16:25 ` Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 0 replies; 9+ messages in thread
From: Andreas Pflug @ 2003-03-07 16:25 UTC (permalink / raw)
To: pgsql-sql
Christoph Haller wrote:
>So you want the trigger executed only if assignments to b and/or c do
>not appear within the update command. Right?
>
>Regards, Christoph
>
>
Right, that's what I want.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
@ 2003-03-10 14:34 Christoph Haller <ch@rodos.fzk.de>
2003-03-11 04:15 ` Re: How to notice column changes in trigger Aspire Something <sank89@sancharnet.in>
0 siblings, 1 reply; 9+ messages in thread
From: Christoph Haller @ 2003-03-10 14:34 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas.Pflug@web.de
>
> >So you want the trigger executed only if assignments to b and/or c do
> >not appear within the update command. Right?
> >
>
> Right, that's what I want.
>
I'm afraid I have no idea how to accomplish that.
Regards, Christoph
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
2003-03-10 14:34 Re: How to notice column changes in trigger Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-11 04:15 ` Aspire Something <sank89@sancharnet.in>
2003-03-11 15:36 ` Re: How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 1 reply; 9+ messages in thread
From: Aspire Something @ 2003-03-11 04:15 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; +Cc: Andreas.Pflug@web.de
Hello Christoph Haller ,
> >
> > >So you want the trigger executed only if assignments to b and/or c do
Do sime thing like this use if /then /esle s block for the problem
In the first if blick check that b and c is null by the declaration of
IS NULL after that you run the update command as required
The program code will show as
create function
CREATE FUNCTION BLAH_FUNCTION() RETURNS "trigger"
AS '
usual blah blah of declre and all ,
IF NEW.b IS NULL and NEW.C IS NULL THEN
ROCK BABY
ELSE
Do OTHERWISE
END IF;
more blah blah plpgsql';
CREATE TRIGGER BLAH
BEFORE UPDATE ON BLAH_TABLE
FOR EACH ROW
EXECUTE PROCEDURE BLAH_FUNCTION
Please revert back if this helps .
Regards
V Kashyap
> > >not appear within the update command. Right?
> > >
> >
> > Right, that's what I want.
> >
> I'm afraid I have no idea how to accomplish that.
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to notice column changes in trigger
2003-03-10 14:34 Re: How to notice column changes in trigger Christoph Haller <ch@rodos.fzk.de>
2003-03-11 04:15 ` Re: How to notice column changes in trigger Aspire Something <sank89@sancharnet.in>
@ 2003-03-11 15:36 ` Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 0 replies; 9+ messages in thread
From: Andreas Pflug @ 2003-03-11 15:36 UTC (permalink / raw)
To: pgsql-sql
Aspire Something wrote:
> Hello Christoph Haller ,
>
>
>
>>>>So you want the trigger executed only if assignments to b and/or c do
>
>
> Do sime thing like this use if /then /esle s block for the problem
>
> In the first if blick check that b and c is null by the declaration of
> IS NULL after that you run the update command as required
>
> The program code will show as
>
> create function
> CREATE FUNCTION BLAH_FUNCTION() RETURNS "trigger"
> AS '
> usual blah blah of declre and all ,
>
>
> IF NEW.b IS NULL and NEW.C IS NULL THEN
> ROCK BABY
> ELSE
> Do OTHERWISE
> END IF;
> more blah blah plpgsql';
>
>
Sorry,
this won't work at all.
The NEW will contain the new row contents, and that may well be non-null
for columns not mentioned in the update query. This kind of code will
perform well on inserts, but not on updates of well-populated rows.
Core SQL or PL/PGSQL statements won't do the job.
Regards,
Andreas
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-03-31 15:49 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 10:30 How to notice column changes in trigger Andreas Pflug <Andreas.Pflug@web.de>
2003-03-07 17:41 ` Josh Berkus <josh@agliodbs.com>
2003-03-07 18:10 ` Andreas Pflug <Andreas.Pflug@web.de>
2003-03-31 15:49 ` Jan Wieck <JanWieck@Yahoo.com>
2003-03-07 12:04 Re: How to notice column changes in trigger Christoph Haller <ch@rodos.fzk.de>
2003-03-07 16:25 ` Andreas Pflug <Andreas.Pflug@web.de>
2003-03-10 14:34 Re: How to notice column changes in trigger Christoph Haller <ch@rodos.fzk.de>
2003-03-11 04:15 ` Aspire Something <sank89@sancharnet.in>
2003-03-11 15:36 ` Andreas Pflug <Andreas.Pflug@web.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox