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

På fredag 25. desember 2020 kl. 17:48:39, skrev Adrian Klaver <
adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>