Problem:
When maintaining hierarchical data in a table, reference loops can cause severe problems like endless loops etc.
 

Solution:
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.