SQLite: Can you use a DELETE trigger to delete other records in the same table?

Question

In this specific case I'm using a SQLite database, but I believe this to be more of a conceptual question with practical implications.

I have a nested-set model in my database and I would like to ensure than when a parent node is somehow deleted from it, all of its children will be deleted as well.

From SQLite's documentation, triggers allow for multiple statements, so I got something like this:

CREATE TABLE "tbl_nsm" (
    "lft"   INTEGER,
    "rgt"   INTEGER
);

CREATE TRIGGER IF NOT EXISTS "delete_entire_leaf" AFTER DELETE ON tbl_nsm
    BEGIN
        DELETE FROM tbl_nsm
        WHERE 1=1
            AND lft > OLD.lft
            AND lft < OLD.rgt;

        UPDATE tbl_nsm SET rgt = rgt - (OLD.rgt - OLD.lft + 1) WHERE rgt > OLD.rgt;
        UPDATE tbl_nsm SET lft = lft - (OLD.rgt - OLD.lft + 1) WHERE lft > OLD.rgt;
    END
;

However, when populating the table with consistent values, whenever I delete a record, the trigger seems to do nothing: all other values remain and there isn't even a single warning. Instead of an AFTER DELETE, I changed it to a BEFORE DELETE trigger, but to no avail.

That got me thinking: can you use DELETE triggers to delete further values from your table? Or does SQL simply ignore any changes to the target table of such a trigger?

Answer 1

In SQLite the trigger can modify the table which it is defined on.

The trigger will not be fired by the action which is performed by this trigger. I.e. if ON DELETE deletes another row then this secondary deletion will not fire this trigger again.

But it will fire another trigger if another action is performed. If this secondary action should fire first trigger then it will be fired because this is not self-firing. For example, UPDATE fires ON UPDATE which performs DELETE which fires ON DELETE which performs UPDATE which causes ON UPDATE... and so on, until the chain stops due to the absence of the row to be altered by the trigger. So use such cross-firing with great caution. This may result in infinite loop or excess rows modifying (up to complete table clearing).

whenever I delete a record, the trigger seems to do nothing

You define per-statement trigger (there is no FOR EACH ROW) but the action looks like per-row one.