Disable PRAGMA recursive_triggers

Question

The android room documentation states that the PRAGMA recursive_triggers is enables by default:

By default, all RoomDatabases use in memory storage for TEMP tables and enables recursive triggers.

This causes problems for me when I use insert with "onConflict(REPLACE)": If (and only if) recursice_triggers are enables, this fires my onDelete trigger (see SQLite Documentation):

REPLACE [...] When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.[...]

I tried to disable this by adding db.execSQL("PRAGMA recursive_triggers = 0;"); in RoomDatabase.Callback.onCreate() but this didn't have any effect.

    public static synchronized FnsDatabase getInstance(Context context){
        if (instance == null) {
            instance = Room.databaseBuilder(context, MyDb.class, "mydb.db")
                    .addCallback(triggerCallback)
                    .build();
        }
        return  instance;
    }

    private static RoomDatabase.Callback triggerCallback = new RoomDatabase.Callback(){
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);

            // adding some triggers here

            db.execSQL("PRAGMA recursive_triggers = 0;");

        }
    };

Can someone help me out with this?

Answer 1

Call it in RoomDatabase.Callback.onOpen() instead. It needs to come after the framework's pragma setting. You also don't need the super calls in your method overrides.