Sqlite Android Launch Operation Passes

Question

This is my first table in which there are 3 columns, and all are entered by the user. No complications, and everything is working fine. But there is another table, and it depends on this table of the 3rd column. i.e. column number numbers. Please ignore the naming convention as I have not followed any naming conventions.

This is my second table, and as you can see, it has the last column number of the room. please ignore the name convention. This column number number is a foreign key, it depends on the input of the first table or deletion. Now I have created a schema for the second table. which announces that it has 5 columns and the last one is a 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 can see, are created successfully. Now I have to write a TRIGGER statement based on this, when I delete the room number from the first table, then all the rows in the 2nd table that have the same room number should be deleted. And if I add a new room number to the first table, it should be added to the second table. The trigger statement for entering a new room number that I wrote looks like this

  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 "+DATABASE_PATIENT_TABLE+" WHERE "+KEY_ROOM_NUMBER+"=new."+KEY_ROOMS +" ) IS NULL)"+ " THEN RAISE (ABORT,'Foreign Key Violation') END;"+ " END;"); 

This piece of code that I added in the onCreate () file of my database file. What else am I doing? Nothing happens here. Please tell me where I am making a mistake. Thanks in tons, Shaist

Answer 1

You have added an INSERT trigger. This causes an error if you try to insert a row that does not match the restriction specified in the trigger; it does nothing to delete rows from one table when rows from another are deleted.

For the first table, you need an AFTER DELETE trigger that deletes the corresponding rows.