Consider this tree-like table structure:

```
CREATE TABLE nodes(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  parent INTEGER,
  descendant_count INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY(parent) REFERENCES nodes(id) ON DELETE CASCADE
);

```

The `descendant_count` column stores the number of descendant records.

Right now I'm maintaining it manually, by incrementing the value on each new insert (or decrementing it on deletions). Essentially I keep getting the parent record, then run

```
 UPDATE nodes SET descendant_count = (descendant_count + 1) ? WHERE...

```

on each parent, until I reach the root. Obviously this is quite slow on a deeply nested structure. 

Is it possible to use triggers to achieve this? Or are there faster and more reliable ways of doing it?

---

*update - 11.08.03*

It appears that SQLite supports [recursive triggers](http://www.sqlite.org/pragma.html#pragma_recursive_triggers). So if I update the count for a single node only, a trigger should then be able to update counts on all parent nodes:

```
CREATE TRIGGER setCounts AFTER UPDATE ON nodes
WHEN (NEW.descendant_count <> OLD.descendant_count)
BEGIN

  -- subtract old counts
  UPDATE nodes
    SET descendant_count = descendant_count - OLD.descendant_count
    WHERE id = NEW.parent;

  -- add new counts
  UPDATE nodes
    SET descendant_count = descendant_count + NEW.descendant_count
    WHERE id = NEW.parent;
END;

```

I tested it and it seems the numbers are right, so this is possible after all?