1

I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field.

My current solution is like this:

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            IF ((OLD.first, OLD.middle, OLD.last, OLD.address, OLD.phone, OLD.notes)
                IS DISTINCT FROM
                (NEW.first, NEW.middle, NEW.last, NEW.address, NEW.phone, NEW.notes)) THEN
                    RETURN NEW;
            ELSE
                    RAISE NOTICE 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS verify ON test;
CREATE TRIGGER verify BEFORE INSERT OR UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE verify();

It works, but - if I add a field to the table, I would have to update the trigger, and that seems convoluted.

Is it possible to simplify the condition?

1 Answer 1

1

Make a copy of old, update the timestamp and compare it with new.

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    DECLARE
        rec record;
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            rec=old;
            rec.timestamp = NEW.timestamp;
            IF rec IS DISTINCT FROM NEW THEN
                    RETURN NEW;
            ELSE
                    RAISE WARNING 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;

(edit: thanks @ Granny Aching)

If you don't need OLD later in the trigger you can modify it without harm.

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            OLD.timestamp = NEW.timestamp;
            IF  OLD IS DISTINCT FROM NEW THEN
                    RETURN NEW;
            ELSE
                    RAISE WARNING 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;
4
  • Thanks! Do I have to make a copy, or can I just modify OLD? Mar 5, 2019 at 16:16
  • BTW, I tried to upvote your answer, but it doesn't let me. Mar 5, 2019 at 16:18
  • 1
    I tried with modifying OLD, and it works. I really like the fact that I can use the same function for all my tables. Thanks!!! Mar 8, 2019 at 18:17
  • I finally have enough reputation to upvote, so I upvoted your answer. It was very helpful. I'm learning so much! Mar 22, 2019 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.