System and method for synchronously updating a hierarchy bridge table

A system for synchronously maintaining a hierarchy bridge table consistent with a corresponding hierarchy table, comprising a processor and memory, the processor configured to execute code stored in the memory, the memory storing executable code serving as an identification module and an update module, wherein:

• the identification module, when executed by the processor, detects and identifies a transaction on a hierarchy table, and provides an instruction to execute a method to update a bridge table, wherein the transaction include one of a delete leaf node, insert leaf node, and move node transaction, wherein in response to a move node transaction in which a node X is updated with a new parent Y, the update module is executed to complete the steps of:
• finding the set of rows S1 from the bridge table where CHILD_NODE=X and PARENT_NODE< >CHILD_NODE,
• finding a set of rows S2 from the bridge table where PARENT_NODE=X,
• deriving a Cartesian product S3=S1×S2,
• deleting rows from the bridge table where PARENT_NODE=S3.S1.PARENT_NODE and CHILD_NODE=S3.S2CHILD_NODE, finding a set of rows S4 from the bridge table where CHILD_NODE=Y deriving a Cartesian product S5=S4×S2, and
• inserting new rows in the bridge table where PARENT_NODE=S5.S4.PARENT_NODE, CHILD_NODE=S5.S2CHILD_NODE, CHILD_LEVEL=S5.S4.CHILD_LEVEL+S5.S2.CHILD_LEVEL+1;
• and
• the update module, when executed by the processor, in response to the instruction, updates the bridge table to maintain the bridge table consistent with the corresponding hierarchy table, wherein the update to the bridge table is completed within a boundary of the transaction of the hierarchy table.

A method for maintaining a hierarchy bridge table consistent with a corresponding hierarchy table, comprising:

• listening for a transaction, having a start boundary and an end boundary, on a hierarchy table wherein the transaction is a move node transaction in which a node X is updated with a new parent node Y, and wherein the update method comprises the steps of:
• finding a set S1 of rows from the bridge table wherein a child node ID is X and a parent node ID is not the same as the child node ID;
• finding a set S2 of rows from the bridge table wherein the parent node ID is X;
• computing a Cartesian product S3=S1×S2;
• deleting one or more rows listed in S3 from the bridge table;
• finding a set S4 of rows from the bridge table wherein the child node ID is Y;
• computing a Cartesian product S5=S4×S2;
• and
• inserting one or more rows listed in S5 into the bridge table;
• and
• upon detecting and identifying the transaction, directing execution of an update method on a bridge table, wherein the update method is completed within the boundaries of the transaction using the identity of the transaction and a state of the bridge table at the start boundary of the transaction.