Regarding the risk of infinite loops with triggers ...
I believe that SQLite's default case should be to simply let resources define the limits, and stop only when there simply aren't the resources to continue.
As with most programming languages, it should be the user's responsibility to not write infinite recursion or iteration in the first place. If the user is doing something that looks like infinite recursion at a casual glance, then we should trust they know what they are doing.
The main thing that SQLite should be responsible for is ensuring that any errors in SQL that the user writes will not corrupt the database.
If SQLite runs out of resources, then it should terminate and roll back the actions that the trigger did and/or the entire transaction, such as would happen if a unique value or primary key constraint was violated.
If possible, SQLite should also manage memory so that it has the resources necessary to roll back the infinite recursion and carry on as normal; SQLite should not crash from an infinity error.
Similarly, note that SQLite could run out of resources for many other reasons besides infinite user recursion, so the latter should simply be handled as an instance of the former.
So keep it simple and don't try to second-guess the user in the general case. Your code will be by far the simplest.
That said, you could add some code for common special cases if you want to give the user a more friendly error message and recover from the situation faster. But these should only be special cases, and not add much complexity to the code.
You *could* add a pragma that defines a hard limit for recursion, but that should be possible to disable and/or set to an extremely high number such that the resource limits kick in first. The hard limit would be an optional limiter, and not the main fallback limiter.
On a separate matter, in your linked list example, this could be handled a lot more effectively if you supported simple iteration, such as a while-loop; that way, the memory footprint is the same small amount regardless of how many items are in the linked list. Note that the SQL standard defines triggers as being more or less the same as stored procedures as to what they can contain.
-- Darren Duncan
