[SOLVED] How to Logically DELETE a record in SQLite

How to Logically DELETE a record in SQLite

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?

Solution

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.