Re: [sqlite] Is there any way to enable recursive triggers?

[Paolo Vernazza]

Henry Miller wrote:
It seems to me that recursion that never touches the same row twice is
less an issue.  That is a trigger that just updates all other rows in
the table once should be fine.  So one (I suspect hard to implement)
idea would be to keep track of which rows have been touched as part of
a trigger.  Any row touched N times breaks out.   This works for both
your delete case, and update cases.
Mmmm... let's suppose that when deleting a row in the linke list table 
we update another table incrementing a counter so we can know how many 
rows we have just deleted...

CREATE TABLE list(
    id INTEGER PRIMARY KEY,
    next INTEGER REFERENCES list,
    data BLOB
  );
CREATE TABLE counter (value TEXT, count INTEGER);
INSERT INTO counter (value, count) VALUES ('deleted', 0);
  CREATE TRIGGER deep BEFORE DELETE ON list BEGIN
      DELETE FROM list WHERE id=old.next;
      UPDATE counter SET count=count+1 WHERE value='deleted';
  END;
It will reveal a loop on the second trigger because we are updating the 
same row in the table "counter"...

Paolo