CODEWITHSUNDEEP
sqlite
jkd
jkd

Reputation: 1664

PRAGMA recursive_triggers won't disable recursive triggers

I do not understand trigger recursion in SQLite 3.8.6.

From my understanding setting PRAGMA recursive_triggers to 0 should prevent a trigger from triggering another one.

But here is an example of what actually happens for me:

sqlite> PRAGMA recursive_triggers;
0
sqlite> CREATE TABLE t1(a TEXT, b TEXT, c TEXT);
sqlite> CREATE TRIGGER on_insert AFTER INSERT ON t1
   ...> BEGIN
   ...>     UPDATE t1 SET b="c" WHERE rowid=NEW.rowid;
   ...> END;
sqlite> CREATE TRIGGER on_update_b AFTER UPDATE OF b ON t1
   ...> BEGIN
   ...>     SELECT RAISE(fail,"triggered update b!");
   ...> END;
sqlite> INSERT INTO t1 VALUES("a","b","c");
Error: triggered update b!

I probably misunderstood something...

Is it possible to prevent the on_update_b trigger to be triggered?

Upvotes: 0

Views: 611

Answers (1)

CL.
CL.

Reputation: 180161

These triggers are not recursive.

A trigger is recursive if it triggers itself (directly or indirectly). This would be the case if on_update_b would execute an UPDATE or INSERT.

Upvotes: 1

Related Questions