SQLite and recursive triggers

Question

I want to understand exactly what a recursive trigger is. Of course, I know what recursion is, but, in the case of sqlite, do those that call themselves? Or that call themselves recursively, but only on the same lines? Does this definition include indirect recursion? only on the same lines or not?

Answer 1

A recursive trigger is one that directly or indirectly fires the same trigger on the same lines or not.

The pragma command recursive_triggerscontrols if recursive triggers are enabled. By default, they are not (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.

Answer 2

- , ( ).