From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to specify that a trigger should fire when column is NOT in SET-clause? |
Date: | 2020-12-25 18:19:38 |
Message-ID: | VisenaEmail.28.aa3c591dff4a2d38.1769b1655f9@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-12-25 19:13:48 | Re: How to specify that a trigger should fire when column is NOT in SET-clause? |
Previous Message | Tom Lane | 2020-12-25 16:57:21 | Re: How to specify that a trigger should fire when column is NOT in SET-clause? |