I'm using the latest MySQL (8.0.15) and have a recursive query which updates the user table and I want to run it every time my user table has an INSERT, UPDATE or DELETE query run against it.
Context: As you'll see in the code snippet, I'm using a materialised path approach to storing my hierarchical data in MySQL. You can probably work out the point of it by looking at the data.
So I need this recursive query to run each time the table is modified so the correct Lineage and Depth is maintained.
The current problem is, after some troubleshooting with my original query I found on this SO post that I couldn't call a SET on the table as it would cause an infinite loop, so I need to prefix the columns with new.
I can't seem to work out how to do this and have provided a working example below with my current attempt.
Edit: I've updated the code below removing the DELIMITER due to db-fiddle limitations.
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.
db<>fiddle here
