Re: BEFORE UPDATE trigger doesn't change column value

Clemens Eisserer <linuxhippy(at)gmail(dot)com> wrote:

> I am trying for quite some time now to get the following
> trigger-function to work properly:
>
> CREATE OR REPLACE FUNCTION update_synced_column()
>   RETURNS trigger AS
> $BODY$
> BEGIN
>    IF NEW.synced IS NULL THEN
>     NEW.synced :=  false;
>    END IF;
>    RETURN NEW;
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
>
> The idea is basically to always update the "synced" column to
> false, unless a value has been provided manually in the
> UPDATE-clause.
> Synced is defined as BOOLEAN DEFAULT FALSE;
> I execute this function in a BEFORE UPDATE trigger FOR EACH ROW,
> however it doesn't seem to have any effect.
>
> Any ideas what could be wrong here?

NEW reflects what the row will look like after the UPDATE.  There
is no way to tell which columns were specified in the SET clause of
the UPDATE; a column which is omitted from that clause will look
exactly the same as a column which is set to the value it already
had.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company