Possible bug at Category Deletion
Posted: Sat Jul 20, 2013 8:02 am
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...
...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:
) 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;
ENDThe 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