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

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

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

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

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@xxxxxxxxxx

www.visena.com