CODEWITHSUNDEEP
sqlpostgresql
Ejonas GGgg
Ejonas GGgg

Reputation: 476

Trigger function that reorders a unique column not applied

I am using Postgres 9.5, it seems I am missing something very easy but I can't apply my trigger function.

I have a table list_items that contains lists and and corresponding items with their positions, i.e.:

-------------------------
id | list | item | pos (for position) 
--------------------------
--------------------------

list and item are just foreign keys to the ids corresponding to their own tables, id is just a serial primary key.

the position pos is unique for an item corresponding to some list (i.e. UNIQUE(list, item, pos))

The user may change or delete the position for some item in a list, I need to have a trigger that reorders the positions of all other affected items in case of an UPDATE or DELETE.

For example, if a list L has 6 items having pos from 1 to 6, If I delete item whose pos = 3, I need to reorder 'pos' for items in L from 4 - 6 to become 3 - 5, Also if I change the pos for exmaple from 5 to 3 using UPDATE, I need to reorder items 3, 4 to become 4, 5.

Here are my Trigger functions

CREATE OR REPLACE FUNCTION reorder_delete() RETURNS trigger AS $$
DECLARE rw RECORD;
BEGIN
    FOR rw IN SELECT * FROM list_items WHERE list = OLD.list AND pos > OLD.pos
    LOOP
        UPDATE list_items SET pos = rw.pos - 1 WHERE id = rw.id;
    END LOOP;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER do_reorder_delete
    AFTER DELETE ON list_items
    FOR EACH ROW
    EXECUTE PROCEDURE reorder_delete();


CREATE OR REPLACE FUNCTION reorder_update() RETURNS trigger AS $$
DECLARE rw RECORD;
BEGIN
    FOR rw IN SELECT * FROM list_items WHERE list = NEW.list AND pos >= NEW.pos ORDER BY pos DESC
    LOOP
        UPDATE list_items SET pos = rw.pos + 1 WHERE id = rw.id;
    END LOOP;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER do_reorder_update
    BEFORE UPDATE ON list_items
    FOR EACH ROW
    EXECUTE PROCEDURE reorder_update();

The problem is whenever I do UPDATE or DELETE operation, nothing happens a t all! (for DELETE, the designated row is deleted but the trigger is not applied)

Upvotes: 0

Views: 67

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22962

You have two triggers. The first runs a function that updates some rows. The second also runs a function that updates some rows.

Whenever rows are updated the second function will be run.

This includes when rows are updated by either trigger.

Re-evaluate your functions in this light and see if this matches what you are seeing.

Also, you may find RAISE NOTICE useful to show what each function is doing.

Upvotes: 1

Related Questions