Re: BEFORE UPDATE trigger doesn't change column value

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