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)