SQLite and recursive triggers

Question

I want to understand exactly what is a recursive trigger. I of course know what recursion is, but, in the case of sqlite, are recursive trigger those which call itself?, or which call itself but only over the same rows? is in that definition included the case of indirect recursion? only over the same rows or not?

Answer 1

A recursive trigger is one which fires the same trigger, directly or indirectly, over the same rows or not.

The pragma command recursive_triggers controls if recursive triggers are enabled or not. By default they aren't (version 3.8.2).

Example:

CREATE TABLE example (a INTEGER);

CREATE TRIGGER example_1
AFTER UPDATE ON example WHEN NEW.a = 5 OR NEW.a = 6
BEGIN UPDATE example SET a = NEW.a + 1; END;

INSERT INTO example SELECT 1;
UPDATE example SET a = 5;
SELECT * FROM example; -- a = 6. Direct recursion forbidden.

PRAGMA recursive_triggers = ON;

UPDATE example SET a = 5;
SELECT * FROM example; -- a = 7. Direct recursion enabled.

DROP TRIGGER example_1;

PRAGMA recursive_triggers = OFF;

CREATE TRIGGER example_1
AFTER UPDATE ON example WHEN NEW.a = 5 OR NEW.a = 7
BEGIN UPDATE example SET a = NEW.a + 1; END;

CREATE TRIGGER example_2
AFTER UPDATE ON example WHEN NEW.a = 6
BEGIN UPDATE example SET a = NEW.a + 1; END;

UPDATE example SET a = 5;
SELECT * FROM example; -- a = 7. Indirect recursion forbidden.

PRAGMA recursive_triggers = ON;

UPDATE example SET a = 5;
SELECT * FROM example; -- a = 8. Indirect recursion enabled.