How to specify that a trigger should fire when column is NOT in SET-clause?

On Friday, December 25, 2020, Andreas Joseph Krogh <andreas@visena.com>
wrote:

Hi.

I need to set a value in a trigger if a column is explicitly NOT specified
in UPDATE's SET-clause.

Beat you can probably do is rely on the fact that if its not specified it
doesn’t change and do nothing within the trigger in that case.

David J.

On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:

Hi.
I need to set a value in a trigger if a column is explicitly NOT
specified in UPDATE's SET-clause.
Like for example having a "BEFORE UPDATE OF NOT"

create TRIGGER my_trigger
BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN (OLD.val <>NEW.val)
EXECUTE PROCEDURE do_stuff();

I want the trigger to be fired when the column "modified" is NOT
specified, is it possible?

It will always be specified, it may or may not be changed. As example:

Table "public.animals"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
cond | character varying(200) | | not null |
animal | character varying(200) | | not null |

CREATE OR REPLACE FUNCTION public.trg_test()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'New id is: %, Old id is: %', NEW.id, OLD.id;
RAISE NOTICE 'New cond is: %, Old cond is: %', NEW.cond, OLD.cond;
RAISE NOTICE 'New animal is: %, Old animal is: %', NEW.animal,
OLD.animal;
RETURN NEW;
END;
$function$
;

update animals set cond = 'skinny' where id = 4;
NOTICE: New id is: 4, Old id is: 4
NOTICE: New cond is: skinny, Old cond is: slim
NOTICE: New animal is: dog, Old animal is: dog
UPDATE 1

Or - is it possible to check for this in the trigger-function?

As David Johnson mentioned you can check whether the value for the
column is changed:

NEW.animal <> OLD.animal

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:

Or - is it possible to check for this in the trigger-function?

As David Johnson mentioned you can check whether the value for the
column is changed:

NEW.animal <> OLD.animal

Better to use IS DISTINCT FROM, to get sane behavior when one or
the other value is NULL.

regards, tom lane

På fredag 25. desember 2020 kl. 17:48:39, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:

Hi.
I need to set a value in a trigger if a column is explicitly NOT
specified in UPDATE's SET-clause.
Like for example having a "BEFORE UPDATE OF NOT"

create TRIGGER my_trigger
BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN (OLD.val

<>NEW.val)

EXECUTE PROCEDURE do_stuff();

I want the trigger to be fired when the column "modified" is NOT
specified, is it possible?

It will always be specified, it may or may not be changed. As example:

True, but what I'm after is using the value from the "modified" column, if
specified, else use CURRENT_TIMESTAMP

My use-case is this;

I have this table:
create table person ( id serial primary key, username varchar not null unique,
passwordvarchar not null, credentials_last_updated timestamp NOT NULL default
CURRENT_TIMESTAMP, created timestamp NOT NULL default CURRENT_TIMESTAMP,
modifiedtimestamp ); Then this trigger to update "credentials_last_updated"
whenever "password" is modified.create or replace FUNCTION
person_password_updated_tf() returns TRIGGER AS $$ BEGIN NEW.
credentials_last_updated= NEW.modified; -- OR CURRENT_TIMESTAMP if "modified"
isn't specified RETURN NEW; END; $$ LANGUAGE plpgsql; create TRIGGER
person_password_updated_tBEFORE UPDATE OF password ON onp_user FOR EACH ROW WHEN
(OLD.password <> NEW.password ) EXECUTE PROCEDURE person_password_updated_tf();

So, I want to set "credentials_last_updated to NEW.modified if "modified" is
specified, else toCURRENT_TIMESTAMP

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On 12/25/20 10:19 AM, Andreas Joseph Krogh wrote:

På fredag 25. desember 2020 kl. 17:48:39, skrev Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:

Hi.
I need to set a value in a trigger if a column is explicitly NOT
specified in UPDATE's SET-clause.
Like for example having a "BEFORE UPDATE OF NOT"

create TRIGGER my_trigger
      BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN

(OLD.val <>NEW.val)

EXECUTE PROCEDURE do_stuff();

I want the trigger to be fired when the column "modified" is NOT
specified, is it possible?

It will always be specified, it may or may not be changed. As example:

True, but what I'm after is using the value from the "modified" column,
if specified, else use CURRENT_TIMESTAMP
My use-case is this;
I have this table:

create table person (
id serial primary key,
username varchar not null unique,
password varchar not null,
credentials_last_updated timestamp NOT NULL default CURRENT_TIMESTAMP,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
modified timestamp );

Then this trigger to update "credentials_last_updated" whenever "password" is modified.

create or replace FUNCTION person_password_updated_tf()returns TRIGGER AS $$ BEGIN NEW.credentials_last_updated =NEW.modified;-- OR CURRENT_TIMESTAMP if "modified" isn't specified RETURN NEW;
END;
$$ LANGUAGE plpgsql;

create TRIGGER person_password_updated_tBEFORE UPDATE OF password ON onp_user FOR EACH ROW WHEN (OLD.password <>NEW.password )
EXECUTE PROCEDURE person_password_updated_tf();

So, I want to set "credentials_last_updated to NEW.modified if
"modified" is specified, else to CURRENT_TIMESTAMP

Because of this:

Is UPDATE the same as DELETE + INSERT in PostgreSQL?

https://www.cybertec-postgresql.com/en/is-update-the-same-as-delete-insert-in-postgresql/

you will always get a NEW.modified. There is no way(AFAIK) to determine
whether the value came from a SET or just came in as part of the NEW tuple.

All you can do is compare the OLD and NEW values of modified to see if
it changed, using the IS DISTINCT FROM from Tom's post to deal with NULL
values.

Not sure why modified is there anyway. Why not just use
credentials_last_updated and set it to CURRENT_TIMESTAMP whenever the
password is changed?

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com