Possible bug at Category Deletion

[m.Th.]

I deleted all my categories (yes, I wanted to ) and I saw that in the Tags table in DB remained ~ 97 rows (categories). The number depends, of course, of the category tree structure and the way in which one deletes it.

The program runs ok because in fact these categories are orphans (their ParentID field points nowhere - their parent row is deleted). It is just garbage.

The possible cause of the problem is the recursive trigger...

Code: Select all

CREATE TRIGGER delete_cat DELETE ON Tags
BEGIN
 DELETE FROM Tags
  WHERE ParentID = OLD.TagID; 
END

...which, as you see, is a BEFORE DELETE trigger (BEFORE defaults) and according to documentation the BEFORE triggers can lead to unexpected results especially if they modify the 'self' table.

The fix is obvious: make it an AFTER trigger:

Code: Select all

CREATE TRIGGER delete_cat AFTER DELETE ON Tags
BEGIN
 DELETE FROM Tags
  WHERE ParentID = OLD.TagID; 
END

[xnview]

Ok thanks