The following example shows how to detect and prevent such problems. Note that it only covers UPDATE, you will want to use a similar trigger for INSERT.
delimiter !
create trigger folder_update before update on folders
for each row
begin
declare parent_id, new_parent integer;
set parent_id = new.parent;
drop temporary table if exists parent_path;
create temporary table parent_path (id integer primary key);
insert into parent_path values (new.id);
while parent_id > 0 and parent_id is not null
do
insert into parent_path values (parent_id); /* will fail on loop */
set new_parent = null;
select parent into new_parent from folders where id = parent_id;
set parent_id = new_parent;
end while;
end;
!
delimiter ;
select * from folders;
+----+-----------+--------+
| id | name | parent |
+----+-----------+--------+
| 1 | docs | 0 |
| 2 | old | 1 |
| 3 | new | 1 |
| 4 | deep | 3 |
| 5 | very deep | 4 |
+----+-----------+--------+
5 rows in set (0.00 sec)
Now, let's try to change the table data to introduce a reference loop:
update folders set parent = 5 where id = 1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
Not a very clear error message. but the folder table remains unharmed.
