Обсуждение: BEFORE UPDATE trigger doesn't change column value

Hi,

Sorry for this newbie-question, 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?

Thanks, Clemens

Clemens Eisserer <linuxhippy@gmail.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

On 5/4/13 at 6:59 AM, Clemens Eisserer wrote:

>Sorry for this newbie-question, 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.
>
You could also add 'NOT NULL' to the declaration of synced so
this column can never be set to NULL and this would further
ensure the trigger function has nothing to do.

By way of sanity testing, do you get any rows when doing
something like:
     SELECT * FROM relevant_table WHERE synced IS NULL;

>Any ideas what could be wrong here?
>
If the above does not apply and at the risk of being too obvious
(specifically not wishing to cause offence):

Has the trigger itself been declared?
refer: <http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html>

Is the function ever getting called?
refer: <http://www.postgresql.org/docs/9.2/static/plpgsql-errors-and-messages.html>

Regards
Gavan Schneider

Hi,

Clemens Eisserer <linuxhippy@gmail.com> wrote:

> Here is what I am trying to achieve: Set "synced" to false at any
> update, except when it has been set explicitly to true.
> This does not seem to be possible, without checking the value SET
> by UPDATE?

Right; since there is no way to check whether a 'true' value there
was explicitly set or just carrying over from the old version of
the row without being set, you need some other mechanism for
handling this.  You could, for exampe, add a "force_sync" column
which could be tested in a trigger.  Your BEFORE UPDATE trigger
could leave the "synced" value in NEW alone if force_sync was
false, and set "synced" to false otherwise.  It could then set
NEW.force_sync to false, to leave you ready for the next update.

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

Kevin Grittner <kgrittn@ymail.com> wrote:

> Your BEFORE UPDATE trigger could leave the "synced" value in NEW
> alone if force_sync was false, and set "synced" to false
> otherwise.  It could then set NEW.force_sync to false, to leave you
> ready for the next update.

Sorry, that's the wrong way around.  I should have said:

Your BEFORE UPDATE trigger could leave the "synced" value in NEW
alone if force_sync was **true**, and set "synced" to false
otherwise.  It could then set NEW.force_sync to false, to leave you
ready for the next update.

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

Hi Kevin,