You can generalize the effects of an employee's movement on the employee and on all of his or her subordinates and devise a formula that calculates new lvl and hierarchy values—for example,
- Let old_emp_lvl = the employee's pre-update level
- Let new_mgr_lvl = the level of the employee's new manager
- Let new_mgr_hier = the hierarchy of the employee's new manager
- Let mov_empid = the employee's employee ID
- Let right_hier = the right part of the hierarchy of each affected employee, starting with his or her employee ID
Thus, the formula for calculating the new lvl and hierarchy values looks like
lvl = lvl - old_emp_lvl + { new_mgr_lvl + 1 | 0 }
hierarchy = { new_mgr_hier | '.' } + mov_empid + '.' + right_hier
Using the formula for lvl, you can calculate Michael's new level as
Michael's current level Andrew's old level + Janet's level + 1
= 2 1 + 1 + 1 = 3
Note that if the employee moves to the highest level in the hierarchy, you don't need to retrieve his or her manager's level because that employee has no manager; his or her level is therefore 0.
According to the hierarchy formula, Michael's new hierarchy is
Janet's hierarchy + Andrew's employee ID + '.' + Michael's right part of the hierarchy starting at his employee ID = '.1.3.' + '2' + '.' + '6.' = '.1.3.2.6.'
And, again, if the employee moves to the highest level in the hierarchy, you don't need to retrieve his or her manager's hierarchy because that employee has no manager.
The update statement inside the trigger updates the Employees table. But to obtain the employee and manager information needed to filter the rows that need updating and then perform the update, the statement has to join two tables. Figure 3 shows what happens logically inside the update trigger.
The update statement first joins the Employees table and the inserted table, which holds only the updated row for Andrew. The join is tricky because you base it on the LIKE operator instead of on equality:
FROM Employees AS E JOIN INSERTED AS I
ON E.hierarchy LIKE I.hierarchy + '%'
The join selects rows from the Employees table based on whether they start with the same hierarchy as the hierarchy in the inserted table (Andrew's old hierarchy). The result of this join is Andrew's row and the rows of his subordinates at all levels. The update statement then performs a left outer join of Step 1's results and the Employees table—which joins Andrew's and his subordinates' rows with Andrew's manager's row. The update trigger uses the left outer join here for the same reason the insert trigger used the left outer join earlier: to guard against a row dropping out in case you updated the employee's manager ID to NULL.
Removing Employees
Member removal is an operation that you might prefer to handle with stored procedures instead of an automatic mechanism such as a trigger because in different situations, you might want to implement different removal logic. Here are a few removal scenarios you can implement with stored procedures. Note that if you always want to use one of these or another removal operation for a particular scenario, you can implement the operation as a trigger instead of a stored procedure and have a totally self-maintaining solution.
Scenario 1: Remove the whole subtree, including the specified employee and all of his or her subordinates. Listing 4 shows the stored procedure that removes a subtree. The following statement removes Robert and his subtree:
EXEC RemoveSubtree @empid = 7
Scenario 2: Remove the specified employee and have his or her direct subordinates report to that employee's manager. Listing 5 shows the stored procedure that handles this scenario. Notice that the stored procedure doesn't take any action on the lvl and hierarchy columns of employees who move to the higher level; the update trigger takes care of modifying those values. Now, let's remove Andrew from the Employees table and assign subordinates Steven and Michael to Janet:
EXEC RemoveEmployeeUpgradeSubs @empid = 2
Scenario 3: Remove the specified employee and assign his or her direct subordinates to a new specified manager. Listing 6 shows the stored procedure that handles this scenario. Now, let's remove Janet and assign her subordinates to Margaret:
EXEC RemoveEmployeeMoveSubs @empid = 3, @newmgr = 4
Now that you've seen how this solution works, you can explore its practical implications. The sidebar "Practical Implementation," available online, discusses how indexes can improve query performance and how to control the order in which hierarchical queries return data. Although SQL Server doesn't offer built-in support for hierarchical structures, many businesses that rely on SQL Server to handle their business information need to manage hierarchical data. With triggers and T-SQL, you can create a functional—and self-maintaining—solution for creating and managing hierarchical dependencies.
This article is adapted from Itzik Ben-Gan's upcoming book Advanced Transact-SQL for SQL Server 2000 (Apress), co-authored by Tom Moreau.