Forums.ATC.no

Teknisk => Generelt teknisk => Emne startet av: ATC på 11. Januar 2009, 16:29 pm

Tittel: MySQL: Using a trigger to prevent reference loops in hierarchical data
Skrevet av: ATC på 11. Januar 2009, 16:29 pm

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

Tittel: [Solved] MySQL: Using a trigger to prevent reference loops in hierarchical data
Skrevet av: ATC på 11. Januar 2009, 16:29 pm

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.