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:
CREATETABLEIFNOTEXISTStasklist(idSERIALPRIMARYKEY,nameVARCHAR(100));CREATETABLEIFNOTEXISTStask(tasklist_idINTEGERNOTNULL,nameVARCHAR(100)NOTNULL,"order"INTEGERNOTNULLCHECK("order">0),PRIMARYKEY(tasklist_id,name),FOREIGNKEY(tasklist_id)REFERENCEStasklist(id));Here we have two tables,tasklist andtask, the latter containing a column calledorder, 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:
CREATEORREPLACEFUNCTIONtask_shift_order(list_idINTEGER,new_orderINTEGER,old_orderINTEGER)RETURNSINTEGERAS$$DECLAREmax_orderINTEGER;BEGIN-- if the task table is empty, max("order") will return null-- use coalesce to return 0 instead of nullSELECTcoalesce(max("order"),0)INTOmax_orderFROMtaskWHEREtasklist_id=list_id;-- do not allow order number greater then max orderIFnew_order>max_orderTHENRAISEEXCEPTION'New order (%) exceeds number of tasks (%) in tasklist(id = %)',new_order,max_order,list_id;ENDIF;IFnew_orderISNULLTHEN-- task is being inserted without ordernew_order:=max_order+1;ELSIFold_orderISNULLTHEN-- task is being inserted at specific orderUPDATEtaskSET"order"="order"+1WHEREtasklist_id=list_idAND"order">=new_order;ELSIFnew_order>old_orderTHEN-- task is assigned a higher orderUPDATEtaskSET"order"="order"-1WHEREtasklist_id=list_idAND"order">old_orderAND"order"<=new_order;ELSE-- task is assigned a lower orderUPDATEtaskSET"order"="order"+1WHEREtasklist_id=list_idAND"order">=new_orderAND"order"<old_order;ENDIF;-- return new order number when inserting without specifying the orderRETURNnew_order;END;$$LANGUAGEplpgsql;This function will update the orders on thetask 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:
Before Insert:
CREATEFUNCTIONtask_insert_order()RETURNSTRIGGERAS$$BEGINNEW."order":=task_shift_order(NEW.tasklist_id,NEW."order",NULL);RETURNNEW;END;$$LANGUAGEplpgsql;CREATETRIGGERtask_insert_orderBEFOREINSERTONtaskFOREACHROWEXECUTEPROCEDUREtask_insert_order();Before Update:
CREATEFUNCTIONtask_update_order()RETURNSTRIGGERAS$$BEGINNEW."order":=task_shift_order(NEW.tasklist_id,NEW."order",OLD."order");RETURNNEW;END;$$LANGUAGEplpgsql;CREATETRIGGERtask_update_orderBEFOREUPDATEONtaskFOREACHROWWHEN(pg_trigger_depth()=0-- prevent trigger recursionANDNEW."order"<>OLD."order")-- only execute when order changesEXECUTEPROCEDUREtask_update_order();We needed to set the conditionWHEN (pg_trigger_depth() = 0 AND NEW."order" <> OLD."order") to avoid recursion, since the trigger updates thetask table itself.
For the after delete trigger, we do not need to calltask_shift_order() since the logic is simpler:
CREATEFUNCTIONtask_delete_order()RETURNSTRIGGERAS$$BEGINUPDATEtaskSET"order"="order"-1WHEREtasklist_id=OLD.tasklist_idAND"order">OLD."order";RETURNOLD;END;$$LANGUAGEplpgsql;CREATETRIGGERtask_delete_orderAFTERDELETEONtaskFOREACHROWEXECUTEPROCEDUREtask_delete_order();You may have noticed that I did not use a unique constraint ontasklist_id andorder 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.
Top comments(0)
For further actions, you may consider blocking this person and/orreporting abuse
