På fredag 25. desember 2020 kl. 17:48:39, skrev Adrian Klaver < adrian.kla...@aklaver.com <mailto:adrian.kla...@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, 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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
