DEV Community

Dimitrios Desyllas
Dimitrios Desyllas

Posted on

How to sync a Nested Set(self-referencing) table into closure table using triggers?

As I ask upon:
https://dba.stackexchange.com/q/335297/118215

I try to sync a self-referencing table into a nested set one.

So far I manged to do it via insert trigger (for more clarity look at question):

CREATE TRIGGER "insert_sync_closure" ON files
AFTER insert AS
BEGIN
    INSERT INTO files_closure(parent_id,child_id)
    select files_closure.parent_id as parent_id ,INSERTED.id as child_id
    from INSERTED join files_closure on INSERTED.id=files_closure.parent_id
    where  files_closure.child_id = INSERTED.folder
END

Enter fullscreen mode Exit fullscreen mode

And initialize it using a recusive SQL query. But I am kinda stuck upon updating it using a trigger upon delete and update.

May I have your help with that?

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more