Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

Livio Ribeiro
Livio Ribeiro

Posted on

     

Use your database! (part 2)

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));
Enter fullscreen modeExit fullscreen mode

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;
Enter fullscreen modeExit fullscreen mode

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();
Enter fullscreen modeExit fullscreen mode

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();
Enter fullscreen modeExit fullscreen mode

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();
Enter fullscreen modeExit fullscreen mode

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)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

Backend Developer, DevOps
  • Location
    Brazil
  • Work
    Software Analyst
  • Joined

More fromLivio Ribeiro

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp