1 table hierarchie example

hierarchies_example.sql

/************************************************************
 * Code formatted by SoftTree SQL Assistant © v11.0.35
 * Time: 8/30/2020 2:54:20 PM
 ************************************************************/

-- Listing 16-8: Schema Creation Script for the New Employees Table
IF OBJECT_ID('Employees', 'U') IS NOT NULL
    DROP TABLE Employees;
GO

CREATE TABLE Employees
(
	empid         INT NOT NULL,
	mgrid         INT NULL,
	empname       VARCHAR(25) NOT NULL,
	salary        MONEY NOT NULL,
	lvl           INT NULL,
	hierarchy     VARCHAR(900) NULL,
	CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
	CONSTRAINT FK_Employees_mgrid_empid
	FOREIGN KEY(mgrid)
	REFERENCES Employees(empid)
);
GO

-- Listing 16-28: Creation Script for the trg_employees_u_calchierarchy Trigger 
CREATE OR
ALTER TRIGGER trg_employees_u_calchierarchy
ON Employees
FOR UPDATE
AS
	-- begin revised part
	DECLARE @rc AS INT;
	SET @rc = @@ROWCOUNT;
	
	IF @rc = 0
	    RETURN;
	
	IF EXISTS (
	       SELECT *
	       FROM   INSERTED       AS E
	              JOIN INSERTED  AS M
	                   ON  E.hierarchy LIKE M.hierarchy + '_%'
	   )
	BEGIN
	    RAISERROR (
	        'Updates to an employee and his/her subordinates are not
allowed!',
	        16,
	        1
	    );
	    ROLLBACK TRAN;
	END;
	-- end revised part
	
	IF 
	    UPDATE (empid)
	
	BEGIN
		RAISERROR ('Updates to empid not allowed!', 16, 1);
		ROLLBACK TRAN;
	END;
	ELSE
	IF 
	    UPDATE (mgrid)
	
	BEGIN
		UPDATE E
		SET    lvl           = E.lvl - I.lvl +
		       CASE 
		            WHEN I.mgrid IS NULL THEN 0
		            ELSE M.lvl + 1
		       END,
		       hierarchy     = ISNULL(M.hierarchy, '.') +
		       CAST(I.empid AS VARCHAR(10)) + '.' +
		       RIGHT(E.hierarchy, LEN(E.hierarchy) - LEN(I.hierarchy))
		FROM   Employees AS E
		       JOIN INSERTED AS I
		            ON  E.hierarchy LIKE I.hierarchy + '%'
		       LEFT OUTER JOIN Employees AS M
		            ON  I.mgrid = M.empid;
	END;
GO

-- Listing 16-13: Creation Script for the Trigger trg_employees_ioi_splitinsertstolevels
-- add support for multi-row inserts by splitting the inserts to levels
CREATE OR
ALTER TRIGGER trg_employees_ioi_splitinsertstolevels
ON Employees
   INSTEAD OF INSERT
AS
	DECLARE @curlvlemps TABLE -- will be filled with employee ids
	        (empid INT NOT NULL, lvl INT NOT NULL);
	
	DECLARE @curlvl AS INT; -- indicates the level of the employees in the subtree
	
	SET @curlvl = 0;
	
	-- insert first top-level employees in the subtree to @curlvlemps
	INSERT INTO @curlvlemps
	SELECT empid,
	       @curlvl
	FROM   INSERTED AS I
	WHERE  NOT EXISTS (
	           SELECT *
	           FROM   INSERTED AS I2
	           WHERE  I.mgrid = I2.empid
	       );
	
	-- loop while there are employees in the last checked level 
	WHILE @@ROWCOUNT > 0
	BEGIN
	    -- insert current level's employees to the Employees table
	    -- this will invoke the after trigger that takes care of the lvl and hierarchy columns
	    INSERT INTO Employees
	    SELECT I.*
	    FROM   INSERTED          AS I
	           JOIN @curlvlemps  AS C
	                ON  I.empid = C.empid
	                AND C.lvl = @curlvl;
	    
	    -- adjust current level
	    SET @curlvl = @curlvl + 1;
	    
	    -- add next level employees to @curlvlemps
	    INSERT INTO @curlvlemps
	    SELECT I.empid,
	           @curlvl
	    FROM   INSERTED          AS I
	           JOIN @curlvlemps  AS C
	                ON  I.mgrid = C.empid
	                AND C.lvl = @curlvl - 1;
	END;
GO

-- Listing 16-14: Altering the Trigger trg_employees_i_calchierarchy to Support Multi-Row Inserts
-- alter the insert trigger that maintains the columns lvl and hierarchy
-- to support multi-row inserts
CREATE OR
ALTER TRIGGER trg_employees_i_calchierarchy
ON Employees
FOR INSERT
AS
	DECLARE @numrows AS INT;
	SET @numrows = @@rowcount;
	IF @numrows >= 1
	BEGIN
	    UPDATE E
	    SET    lvl           = CASE 
	                      WHEN E.mgrid IS NULL THEN 0
	                      ELSE M.lvl + 1
	                 END,
	           hierarchy     = CASE 
	                            WHEN E.mgrid IS NULL THEN '.'
	                            ELSE M.hierarchy
	                       END + CAST(E.empid AS VARCHAR(10)) + '.'
	    FROM   Employees AS E
	           JOIN INSERTED AS I
	                ON  I.empid = E.empid
	           LEFT OUTER JOIN Employees AS M
	                ON  E.mgrid = M.empid;
	END;
GO


-- Listing 16-30: Creation Script for the RemoveSubtree Stored Procedure 
CREATE OR ALTER PROC RemoveSubtree(@empid INT)
AS

DELETE 
FROM   Employees
WHERE  hierarchy LIKE(
           SELECT hierarchy
           FROM   Employees
           WHERE  empid = @empid
       )
       + '%';
GO


-- Listing 16-32: Creation Script for Stored Procedure RemoveEmployeeUpgradeSubs
CREATE OR ALTER PROC RemoveEmployeeUpgradeSubs(@empid INT)
AS

BEGIN TRAN;

UPDATE E
SET    mgrid = M.mgrid
FROM   Employees AS E
       JOIN Employees AS M
            ON  E.mgrid = M.empid
WHERE  M.empid = @empid;

DELETE 
FROM   Employees
WHERE  empid = @empid;

  COMMIT TRAN;
GO

-- Listing 16-34: Creation Script for the RemoveEmployeeMoveSubs Stored Procedure
CREATE OR ALTER PROC RemoveEmployeeMoveSubs(@empid INT, @newmgr INT)
AS

BEGIN TRAN;

UPDATE E
SET    mgrid = @newmgr
FROM   Employees AS E
       JOIN Employees AS M
            ON  E.mgrid = M.empid
WHERE  M.empid = @empid;

DELETE 
FROM   Employees
WHERE  empid = @empid;

  COMMIT TRAN;

GO

from book https://github.com/Apress/adv-transact-sql-for-sql-server-2000