How to set a value in trigger only if it wasn't already set?

by dvtan   Last Updated August 14, 2019 13:06 PM

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values in the tuple instead of the values in the update-statement?):

create temp table test (
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.updated IS NOT NULL THEN
        NEW.updated := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER asdf BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

How can I make it only set NEW.updated := now(); only when updated is not explicitly set in an update-statement? So that the trigger doesn't overwrite me when I want to set the updated column manually.



Related Questions


conditional trigger function for history table

Updated June 21, 2016 08:02 AM