Thread: Problems with NEW.* in triggers

Hi folks -

I'm having a problem with what looks like it should be trivial.

For the function

create or replace function timestamp_fn() returns opaque as '       begin       NEW.moddate := coalesce(NEW.moddate,
now());      return NEW;       end
 
' language 'plpgsql';

on an after insert or update trigger, never sets moddate to now().

I had thought that if moddate isn't included in an insert or update,
that it would be null in the NEW context, so that this would fall 
through to the now() call. (I also tried this as below:

create or replace function timestamp_fn() returns opaque as '    begin    if NEW.moddate is not null then
returnNEW;    else            NEW.moddate := now();            return NEW;    end if;     end
 
' language 'plpgsql';

With the same results.)

Any thoughts on what I'm doing wrong??

-j


-- 
Jamie Lawrence                                        jal@jal.org
Never eat anything bigger than your head.

Jamie Lawrence <postgres@jal.org> writes:
> I had thought that if moddate isn't included in an insert or update,
> that it would be null in the NEW context,

No, it would be whatever the value to be assigned to the column would
be, if the trigger were not present.  In particular, during an UPDATE
it's going to contain the old value of the field.  In an INSERT it would
be whatever the column's default value is.

I am not sure what your intention is here.  If you want the trigger to
force the field to current time, it can certainly do that.  If you want
the user to control whether the field is updated, why do you need a
trigger at all?
        regards, tom lane

Jamie,

> Any thoughts on what I'm doing wrong??

Yes.  If you want to modify the new data, you need to use a BEFORE trigger.
AFTER triggers can't modify NEW, just read it.

--
-Josh BerkusAglio Database SolutionsSan Francisco

On Tue, 04 Nov 2003, Tom Lane wrote:

> Jamie Lawrence <postgres@jal.org> writes:
> > I had thought that if moddate isn't included in an insert or update,
> > that it would be null in the NEW context,
> 
> No, it would be whatever the value to be assigned to the column would
> be, if the trigger were not present.  In particular, during an UPDATE
> it's going to contain the old value of the field.  In an INSERT it would
> be whatever the column's default value is.

For an insert, the default is null in this case.

test=# create table trash (moddate timestamp, message text);
CREATE TABLE
test=# create or replace function timestamp_test() returns opaque as '
test'#         begin
test'#         NEW.moddate := coalesce(NEW.moddate, now());
test'#         return NEW;
test'#         end
test'# ' language 'plpgsql';
CREATE FUNCTION
test=# create trigger critter_timestamp_test after insert or update on critter for each row execute procedure
timestamp_fn();
CREATE TRIGGER
test=# insert into trash (message) values ('hi there');
INSERT 560920 1
test=# insert into trash (message) values ('hi there');
INSERT 560921 1
test=# select * from trash;moddate | message  
---------+----------        | hi there        | hi there
(2 rows)

test=# 

I don't understand why moddate isn't getting set to now() in the above.

(Point taken on updates... I was thinking about NEW in slightly
the wrong way for an after trigger.)


> I am not sure what your intention is here.  If you want the trigger to
> force the field to current time, it can certainly do that.  If you want
> the user to control whether the field is updated, why do you need a
> trigger at all?

Excellent question, sigh. I'm trying to bandaid a bad design choice
until the application can be changed.

Now that you have me thinking about it, an update rule is probably a
better idea.

Thanks for the help, I appreciate it -

-j


-- 
Jamie Lawrence                                        jal@jal.org
"Remember, half-measures can be very effective if all you deal with are
half-wits."  - Chris Klein

Jamie Lawrence <postgres@jal.org> writes:
> I don't understand why moddate isn't getting set to now() in the above.

Josh fingered the problem there --- you need a BEFORE trigger if you
want to affect the data that will be stored.  I had missed that little
mistake :-(
        regards, tom lane

On Tue, 04 Nov 2003, Tom Lane wrote:

> Jamie Lawrence <postgres@jal.org> writes:
> > I don't understand why moddate isn't getting set to now() in the above.
> 
> Josh fingered the problem there --- you need a BEFORE trigger if you
> want to affect the data that will be stored.  I had missed that little
> mistake :-(

Yep. Many thanks to both of you.

-j


-- 
Jamie Lawrence                                        jal@jal.org
If built in great numbers, motels will be used for nothing but illegal
purposes.   - J. Edgar Hoover