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.