MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.13


CREATE TABLE user (
  `Id` INT primary key, 
  `Name` VARCHAR(55), 
  `ManagerUserID` INTEGER, 
  `Depth` INTEGER, 
  `Lineage` VARCHAR(255)
);
INSERT INTO user (
  `Id`, `Name`, `ManagerUserID`, `Depth`, 
  `Lineage`
) 
VALUES 
  ('1', 'User 1', NULL, 0, '1.'), 
  ('2', 'User 2', '1', 1, '1.2.'), 
  ('3', 'User 3', '4', 3, '1.2.4.3.'), 
  ('4', 'User 4', '2', 2, '1.2.4.'), 
  ('5', 'User 5', '2', 2, '1.2.5.');
SELECT * from user;


Id
Name
ManagerUserID
Depth
Lineage


1
User 1
null
0
1.

2
User 2
1
1
1.2.

3
User 3
4
3
1.2.4.3.

4
User 4
2
2
1.2.4.

5
User 5
2
2
1.2.5.


CREATE TRIGGER after_user_update 
   AFTER UPDATE ON user
   FOR EACH ROW 
BEGIN
	UPDATE
	  user
	SET
	  new.Lineage = CONCAT(Id, '.'),
	  new.Depth = 0
	WHERE
	  new.ManagerUserID IS NULL;
  
	WITH RECURSIVE prev AS (
	  SELECT
		*
	  FROM
		user
	  WHERE
		new.ManagerUserID IS NULL
	  UNION
	  SELECT
		t.new.Id,
		t.new.Name,
		t.new.ManagerUserID,
		p.new.Depth + 1 as Depth,
		CONCAT(p.new.Lineage, t.new.Id, '.')
	  FROM
		prev p
		JOIN user t ON t.new.ManagerUserID = p.new.Id
	)
	UPDATE
	  user t,
	  prev p
	SET
	  t.new.Depth = p.Depth,
	  t.new.Lineage = p.Lineage
	WHERE
	  t.new.Id = p.new.Id;
END
UPDATE user SET `ManagerUserID` = '1' WHERE (`Id` = '3');
Can't update table 'user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

SELECT * from user;


Id
Name
ManagerUserID
Depth
Lineage


1
User 1
null
0
1.

2
User 2
1
1
1.2.

3
User 3
4
3
1.2.4.3.

4
User 4
2
2
1.2.4.

5
User 5
2
2
1.2.5.

Id	Name	ManagerUserID	Depth	Lineage
1	User 1	null	0	1.
2	User 2	1	1	1.2.
3	User 3	4	3	1.2.4.3.
4	User 4	2	2	1.2.4.
5	User 5	2	2	1.2.5.