If you’re using SQLite you might be surprised to know that any DELETE triggers you’ve created will not fire after a REPLACE operation by default.

Delete triggers fire when rows are removed due to a REPLACE conflict resolution. This feature is only enabled when recursive triggers are enabled.

This was causing us some grief in one of our Android projects where we were using a virtual table for full-text search. Whenever we updated the products table we had a trigger fire to update the virtual-table. However, when we did a REPLACE into the product table a duplicate entry would be entered into the virtual table. We resolved this issue by turning on recursive triggers.

You can enable recursive triggers by running the following SQL query:

PRAGMA recursive_triggers='ON'

You can easily use the SQLiteDatabase.execSQL method if you’re doing this on Android.