sql - Exclude updated record from trigger function

Question

I have a table with a column named priority. I created a trigger function that fires after update. I want the function to change the values of the rest of the rows' priority column. For example I have 10 rows and each row has a single value ranging from 1-10, I then want to change the priority of row 10 to 1, then add 1 to the rest of the rows.

I've tried to change the query in many ways and add more/less logic to the function, but I am stuck.

CREATE FUNCTION reorder_priority() RETURNS TRIGGER AS $$
BEGIN
    CASE TG_OP
        WHEN 'UPDATE' THEN
            UPDATE
                link
            SET
                priority = priority + 1
            WHERE
                link.priority >= NEW.priority AND
                NOT link.priority > OLD.priority;
    END CASE;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

This function was able to do this, but it seems to trigger twice, adding 1 to every single row again, leaving me with 10 rows, but priority ranging from 2-11.

标签： sqlpostgresql

Answer 1

I figured it out..

Just had to change the comparison to exclude updating the value of OLD.priority, return NEW, and also change the trigger to run BEFORE and not AFTER

BEGIN
    CASE TG_OP
        WHEN 'UPDATE' THEN
            UPDATE
                link
            SET
                priority = priority + 1
            WHERE
                link.priority >= NEW.priority AND
                NOT link.priority >= OLD.priority;
    END CASE;

    RETURN NEW;
END;