CODEWITHSUNDEEP
sqlitesystem.data.sqlitepragmarecursive-triggers
Mark A. Donohoe
Mark A. Donohoe

Reputation: 30428

Why are Recursive Triggers disabled in a 3.8.8.3 database where I did *not* specify the pragma?

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?

Upvotes: 0

Views: 450

Answers (1)

Googie
Googie

Reputation: 6057

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.

Upvotes: 2

Related Questions