In the previous post, I showed a simple example on using a database trigger to set the "updated" field when data changes. Now I will show a more complex example.
Let's take a schema for a task list:
CREATE TABLE IF NOT EXISTS tasklist ( id SERIAL PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE IF NOT EXISTS task ( tasklist_id INTEGER NOT NULL, name VARCHAR(100) NOT NULL, "order" INTEGER NOT NULL CHECK ("order" > 0), PRIMARY KEY (tasklist_id, name), FOREIGN KEY (tasklist_id) REFERENCES tasklist (id) );
Here we have two tables,
task, the latter containing a column called
order, which is, obviously, an order number for the tasks of a specific list.
Now starts our problems: To insert a new task, we need to fetch the max order number for the list, increment by 1, and insert the new task. But what will happen when two people try to insert a task at the same time?
Another problem is changing the order number of a task, because you need to shift the order of the others tasks. Deleting a task has the same issue.
To solve these problems, we can tell the database to set the order number when inserting a new task and shift the order of the tasks when a task is updated or deleted.
First, let's create a database function to help us:
CREATE OR REPLACE FUNCTION task_shift_order( list_id INTEGER, new_order INTEGER, old_order INTEGER ) RETURNS INTEGER AS $$ DECLARE max_order INTEGER; BEGIN -- if the task table is empty, max("order") will return null -- use coalesce to return 0 instead of null SELECT coalesce(max("order"), 0) INTO max_order FROM task WHERE tasklist_id = list_id; -- do not allow order number greater then max order IF new_order > max_order THEN RAISE EXCEPTION 'New order (%) exceeds number of tasks (%) in tasklist(id = %)', new_order, max_order, list_id; END IF; IF new_order IS NULL THEN -- task is being inserted without order new_order := max_order + 1; ELSIF old_order IS NULL THEN -- task is being inserted at specific order UPDATE task SET "order" = "order" + 1 WHERE tasklist_id = list_id AND "order" >= new_order; ELSIF new_order > old_order THEN -- task is assigned a higher order UPDATE task SET "order" = "order" - 1 WHERE tasklist_id = list_id AND "order" > old_order AND "order" <= new_order; ELSE -- task is assigned a lower order UPDATE task SET "order" = "order" + 1 WHERE tasklist_id = list_id AND "order" >= new_order AND "order" < old_order; END IF; -- return new order number when inserting without specifying the order RETURN new_order; END; $$ LANGUAGE plpgsql;
This function will update the orders on the
task table and return the new order number in the case of a new task being inserted.
Now we just need to setup three triggers, before insert a task, before update a task order and after delete a task:
CREATE FUNCTION task_insert_order() RETURNS TRIGGER AS $$ BEGIN NEW."order" := task_shift_order(NEW.tasklist_id, NEW."order", NULL); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER task_insert_order BEFORE INSERT ON task FOR EACH ROW EXECUTE PROCEDURE task_insert_order();
CREATE FUNCTION task_update_order() RETURNS TRIGGER AS $$ BEGIN NEW."order" := task_shift_order(NEW.tasklist_id, NEW."order", OLD."order"); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER task_update_order BEFORE UPDATE ON task FOR EACH ROW WHEN (pg_trigger_depth() = 0 -- prevent trigger recursion AND NEW."order" <> OLD."order") -- only execute when order changes EXECUTE PROCEDURE task_update_order();
We needed to set the condition
WHEN (pg_trigger_depth() = 0 AND NEW."order" <> OLD."order") to avoid recursion, since the trigger updates the
task table itself.
For the after delete trigger, we do not need to call
task_shift_order() since the logic is simpler:
CREATE FUNCTION task_delete_order() RETURNS TRIGGER AS $$ BEGIN UPDATE task SET "order" = "order" - 1 WHERE tasklist_id = OLD.tasklist_id AND "order" > OLD."order"; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER task_delete_order AFTER DELETE ON task FOR EACH ROW EXECUTE PROCEDURE task_delete_order();
You may have noticed that I did not use a unique constraint on
order columns. This is because I was not able to update the orders without causing a conflict. If you know a way to do it, please tell it in the comments.