Android Sqlite trigger operation go through

This is my first table it has 3 columns and all are entered by the user. No complication and it works fine. But there is another table and it is dependent on this table 3rd column. i.e. on room number column. Please ignore the names convention as i have not really followed any naming conventions.

This is my second table and as you can see it has its last column as room number. please ignore name convention. this room number column is foreign key it is dependent on the 1st table entry or delete Now I have created a schema for the 2nd table. which declares that it has 5 columns and the last one is the foreign key.

 private static final String DATABASE_CREATE_NOTES_ID_TABLE =
    ("CREATE TABLE "+ DATABASE_NOTES_TABLE +" ("+ KEY_ROWID +" INTEGER PRIMARY KEY AUTOINCREMENT, "+
            KEY_TIME + " TEXT, "+ 
            KEY_NOTES + " TEXT, "+ 
            KEY_DATE +  " TEXT, " +
            KEY_ROOMS + " INTEGER , FOREIGN KEY (" + KEY_ROOMS + ") REFERENCES " + DATABASE_PATIENT_TABLE +" (" +KEY_ROOM_NUMBER+ "));");

Tables as you see is getting created successfully. Now I have to write a TRIGGER statement, based on that when I delete a room number from the first table, then all the rows in the 2nd table which has the same room number should be deleted. And If I add a new room number in the first table then it should be added in the 2nd table. The trigger statement for insert of new room number that I have written is as follows

    db.execSQL("CREATE TRIGGER fk_notesTable_roomNumber " +
                " BEFORE INSERT "+
                " ON "+DATABASE_NOTES_TABLE+
                " FOR EACH ROW BEGIN"+
                " SELECT CASE WHEN ((SELECT "+KEY_ROOM_NUMBER+" FROM 开发者_C百科"+DATABASE_PATIENT_TABLE+" WHERE "+KEY_ROOM_NUMBER+"=new."+KEY_ROOMS +" ) IS NULL)"+
                " THEN RAISE (ABORT,'Foreign Key Violation') END;"+
                "  END;");

This piece of code I have added in the onCreate() of my database file. What else do I do ? Nothing is happening here. Please kindly point me out where I am making mistake. Thanks in tons, Shaista

You have added an INSERT trigger. This raises an error if you try and insert a row that doesn't match the constraint specified in the trigger; it does nothing about deleting rows from one table when rows from another are deleted.

You need an AFTER DELETE trigger on the first table which deletes the corresponding rows.