[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: How to specify that a trigger should fire when column is NOT in SET-clause?
From:       Adrian Klaver <adrian.klaver () aklaver ! com>
Date:       2020-12-25 19:13:48
Message-ID: f919b311-bd8a-dd60-3c16-b05fe4341cbc () aklaver ! com
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]