Обсуждение: How to specify that a trigger should fire when column is NOT in SET-clause?

Hi.

 

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

 

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>
> <https://www.visena.com>


-- 
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

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:

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_t BEFORE UPDATE OF password ON onp_user
    FOR EACH ROW WHEN (OLD.password <> NEW.password )
EXECUTE PROCEDURE person_password_updated_tf();

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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>
> <https://www.visena.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com