How to Logically DELETE a record in SQLite

Question

I would like to mark a record as deleted instead of actually deleting a record. My intention is to use an instead of trigger, but I am getting an SQLException that neither I nor Google know how to solve this.

My code:

    CREATE TRIGGER IF NOT EXISTS <Trigger>
           INSTEAD OF DELETE ON <Table>
            FOR EACH ROW
            BEGIN
              UPDATE <Table>
              SET Status = 'D'
              WHERE ID = old.ID;                
            END

My Error:

java.sql.SQLException: cannot create INSTEAD OF trigger on table: main.<Table>
    at org.sqlite.NativeDB.throwex(NativeDB.java:210)
    at org.sqlite.NativeDB._exec(Native Method)
    at org.sqlite.Stmt.executeUpdate(Stmt.java:152)

Assist me, please?

EDIT: What I really wanted was to activate foreign key enforcement.

Refer here: How do you enforce foreign key constraints in SQLite through Java?

Answer 1

You cannot use INSTEAD OF triggers on tables, and when RAISE-ing an error in BEFORE/AFTER triggers, any updates done in the trigger would also be rolled back.

You could rename your table, create a view for that table, and create lots of INSTEAD OF triggers to implement all the INSERT/UPDATE/DELETE operations.

However, it would be much easier to change your program to just execute the UPDATE when it wants to mark some record.

Answer 2

Instead of triggers are intended for use with views so that you can specify the underlying tables that an action should be carried out on when an insert, update our delete is issued on the view itself.

One thing you could try is to do a before delete trigger then raise an exception. The only thing is I'm not sure if this would also interfere with the update. Maybe worth a try though:

SELECT RAISE(ABORT, 'Prevent delete');