Skip site navigation (1) Skip section navigation (2)

Update Row Level Trigger: default value for update trigger

From: Theo Dickinson <Theo(dot)Dickinson(at)unv(dot)org>
To: "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Update Row Level Trigger: default value for update trigger
Date: 2004-05-14 15:17:26
Message-ID: 8A2F0F003B4A7647A8F25E2B5732BD6AC4A8CF@exchange.unv.org (view raw or flat)
Thread:
Lists: pgsql-novice
Within an update row level trigger how can I detect which field[s] has been
effected by an update ?
As I'm not concerned with the OLD field name values, comparisons against
them are not important.

I'm guessing its a simple operation, but for the moment the solution escapes
me! 
I've tried checking for null and empty string but the new.modified_by value
is always the old.modified_by.

This leads me to think I'm not understanding the trigger logic correctly! 
Does an update ALWAYS change EVERY field even if not every field is
specified in the SQL update statement.

Here is the logic I'm trying to implement within my trigger function.

IF ( new.modified_by IS NULL ) THEN
	/* if an update occurs and the modified_by field  is empty then use
the default value of 1 */
	mod_by = 1; 
ELSE
	mod_by = new.modified_by;
END IF;

Example1:
update my_table set modified_by=343, title='example1' where group_id = 10;
/* what I want to happen in this case is mod_by to be set to 343 */

Example2:
update my_table set title='example2' where group_id = 10;
/* what I want to happen in this case is mod_by to be set to 1 */

I hope this is explained well enough to allow for some help

If anybody can help I'd really appreciate it.



Responses

pgsql-novice by date

Next:From: Michael A WeberDate: 2004-05-15 00:11:35
Subject: Set search_path
Previous:From: Oliver FrommeDate: 2004-05-14 12:45:55
Subject: Re: Should I keep INDEX on the table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group