SQL Server - Recursive CTE from leafs to root (inverse)

Question

Imagine following scenario: I have a lot of levels, from up level (root parent) to down levels (childs or leafs).

                   (root parent)               LEVEL 0
                       ID:98
                      /     \
                     /       \
                    /         \
                   o           +               LEVEL 1
                ID:99        ID:100
                             /   \
                            /     \
                           o       +           LEVEL 2
                        ID:101   ID:102
                                  / \
                                 /   \ 
                                o     o        LEVEL 3         
                            ID:201  ID:202

Imagine now '+' symbols are rooms. Rooms at the same level cannot communicate between them. Each room has some gates. Through these gates you can communicate to other rooms (childs) at another level down.

Symbols 'o' are the leafs, I mean, rooms which haven't gates to access other rooms at a lower level.

For simplicity here, Each room has two gates, but could have more than two.

So now, finally image the following: If an explosion originates in any of the child/leaf rooms belonging to the parent room, then all gates of the parent room will be closed automatically to prevent explosion propagates up to the root parent.

So imagine the following table:

ROOM_ID | PARENT_ROOM | GATES_OPEN | EXPLOSION
    98       NULL          1         0
    99        98           1         0
   100        98           1         0
   102       100           1         0
   101       100           1         0 
   200       102           -         0
   201       102           -         0

All gates for all rooms are opened since initially there are no explosions. Rooms 200 and 201 has no gates.

Imagine each room has a sensor to detect a possible explosion. If the sensor detects an explosion, the signal is propagated to the parent room, and parent room closes all its gates. This signal is also propagated up to the parent rooms, and all the parent rooms also close all its gates and so on until root parent is reached which also closes all its gates.

So now imagine an explosion is caused in room ID:102 so I need to obtain below table updated:

ROOM_ID | PARENT_ROOM | GATES_OPEN | EXPLOSION
    98       NULL          0         0
    99        98           1         0
   100        98           0         0
   102       100           1         1
   101       100           1         0 
   200       102           -         0
   201       102           -         0

So using a recursive CTE, how can obtain final table updated from the initial table? I need to propagate it from the root in which explosion was caused to root parent.

Answer 1

Here is one way to do it:

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    ROOM_ID int,
    PARENT_ROOM int,
    GATES_OPEN bit,
    EXPLOSION bit
)


INSERT INTO @T VALUES
(98, NULL, 1, 0),
(99, 98, 1, 0),
(100, 98, 1, 0),
(102, 100, 1, 0),
(101, 100, 1, 0),
(200, 102, NULL, 0),
(201, 102, NULL, 0)

Then, create the CTE:

DECLARE @RoomId int = 102;

;WITH CTE AS
(
    SELECT ROOM_ID
          ,PARENT_ROOM
          ,GATES_OPEN
          ,CAST(1 AS BIT) AS EXPLOSION
    FROM @T 
    WHERE ROOM_ID = @RoomId 
    UNION ALL
    SELECT t.ROOM_ID
          ,t.PARENT_ROOM
          ,CAST(0 AS BIT) AS GATES_OPEN
          ,t.EXPLOSION
    FROM @T t 
    INNER JOIN CTE ON t.ROOM_ID = CTE.PARENT_ROOM
)

Update the table:

UPDATE t 
SET GATES_OPEN = CTE.GATES_OPEN,
    EXPLOSION = CTE.EXPLOSION
FROM @T t
INNER JOIN CTE ON t.ROOM_ID = CTE.ROOM_Id

Finally, test if the update was OK:

SELECT *
FROM @T 

Results:

ROOM_ID PARENT_ROOM GATES_OPEN  EXPLOSION
98      NULL        0           0
99      98          1           0
100     98          0           0
102     100         1           1
101     100         1           0
200     102         NULL        0
201     102         NULL        0

Update

If you don't know in what room the explosion occurs (I'm guessing some process updates the database table and sets the explosion value to 1), Then you can use a trigger on the table. It's almost the same as the query I've written before, and it's results are the same:

CREATE TRIGGER tr_Rooms_Update ON Rooms 
FOR UPDATE
AS

;WITH CTE AS
(
    SELECT ROOM_ID
          ,PARENT_ROOM
          ,GATES_OPEN
          ,EXPLOSION
    FROM inserted 
    WHERE EXPLOSION = 1
    UNION ALL
    SELECT t.ROOM_ID
          ,t.PARENT_ROOM
          ,CAST(0 AS BIT) AS GATES_OPEN
          ,t.EXPLOSION
    FROM Rooms t 
    INNER JOIN CTE ON t.ROOM_ID = CTE.PARENT_ROOM
)

UPDATE t 
SET GATES_OPEN = CTE.GATES_OPEN,
    EXPLOSION = CTE.EXPLOSION
FROM Rooms t
INNER JOIN CTE ON t.ROOM_ID = CTE.ROOM_Id

GO