0

According to the SQLite docs, as of version 3.7.0, recursive triggers are supposed to be enabled by default. However, when I open a conncetion to a database using version 3.8.8.3 (specifically with using System.Data.SQLite core from NuGet) and check the pragma, they are set to '0' or off, and sure enough, they do not fire recursively.

If I open up the database in something like the open source tool Sqlite browser which uses 3.8.2, there too it shows the pragma as being set to off. I can enable them in the UI, but as soon as I close, then re-open the database, they are off again. WTF?!!

I have even tried adding "PRAGMA RECURSIVE_TRIGGERS = 1;" to the connection string, which some online docs say should work, but of course it doesn't.

I also looked at the SQLiteConnectionStringBuilder and while it does have a reference for other pragma-related things such as Binary GUIDs, it does not have a reference to recursive triggers making me believe even more you can't use the connection string for this.

Simply put, this is breaking our application. We are depending on recursive triggers for data integrity.

Our only solution is to manually edit every single SQL query the app is using to add the pragma, which is a real bitch!

So... can anyone help me figure out why recursive triggers are not being enabled by default?

Mark A. Donohoe
  • 28,442
  • 25
  • 137
  • 286
  • 1
    The [documentation](http://www.sqlite.org/pragma.html#pragma_recursive_triggers) says "Recursive triggers may be turned on by default in future versions of SQLite." – CL. May 27 '15 at 08:58
  • As I called out below in a similar comment, according to the docs on the 'Limits' page here... sqlite.org/limits.html... it says "Beginning with version 3.6.18, recursive triggers were supported but had to be explicitly enabled using the PRAGMA recursive_triggers statement. Beginning with version 3.7.0, recursive triggers are enabled by default but can be manually disabled using PRAGMA recursive_triggers" That's under "#10 - Maximum Depth Of Trigger Recursion" which is why I'm confused. More so since that information is *not* on the PRAGMA page where it should be. – Mark A. Donohoe May 27 '15 at 14:44

1 Answers1

2

They are not enabled by default because of backwards compatibility.

As you already mentioned, before 3.7.0 there were no recursive triggers. Once they were introduced it would break a lot of production environments if they were to be enabled by default.

Instead the careful approach was chosen and they are supported, but not enabled by default. Let everybody adopt it, make it a common thing and then it can be enabled by default. Just like @CL said - they may be enabled by default in future SQLite versions.

Googie
  • 5,742
  • 2
  • 19
  • 31
  • According to the docs on the 'Limits' page here... sqlite.org/limits.html... your versions are wrong. The text there says "Beginning with version 3.6.18, recursive triggers were supported but had to be explicitly enabled using the PRAGMA recursive_triggers statement. Beginning with version 3.7.0, recursive triggers are enabled by default but can be manually disabled using PRAGMA recursive_triggers" That's under "#10 - Maximum Depth Of Trigger Recursion" which is why I'm confused. More so since that information is *not* on the PRAGMA page where it should be. – Mark A. Donohoe May 27 '15 at 14:43
  • The [limits page](http://www.sqlite.org/limits.html) is wrong. Recursive triggers were [planned to be enabled by default in 3.7.0](http://www.sqlite.org/oldnews.html#2009_09_11), but this never actually happened for the mentioned reasons. – CL. May 27 '15 at 17:31