How to use TRIGGER in Android SQLite

Question

I have two tables in database:

• table one has name and room number column
• table two has room number and time column.

Now when the room number from first column is deleted or added, my second table should also be updated. I think this is possible with TRIGGER command, but I am not really sure as how to use it.

Generally my create database statement is like this:

private static final String DATABASE_CREATE_PATIENT_ID_TABLE =
    "create table " + DATABASE_PATIENT_TABLE +
    " (_id integer primary key autoincrement,"
     + "patient_number text not null, room_numbertext not null, " +
            "patient_initial text not null);";

Now when the rooms are deleted or added in the first table my second table should be updated.

private static final String DATABASE_CREATE_NOTES_ID_TABLE =
    "create table " + DATABASE_NOTES_TABLE +
    " (_id integer primary key autoincrement," +
     " room_number text not null, time_hour text not null, " +
            "notes_hour text not null, today_date text not null);";

Initially I was doing was compare the content of the two tables. But this definitely will lead to performance issue later when data will increase. So I stumbled across TRIGGER thing. I think this can solve my problem, but I don't know how exactly should I use it.

I came to know about it from Using SQLite Database with Android.

I have explained this problem with the screen shot in my another question. Please have a look at it and if please kindly guide me new question

Answer 1

Simple start for you

create trigger simple_trigger1 after insert on database_patient_table begin update database_notes_table; end 
create trigger simple_trigger2 after delete on database_patient_table begin update database_notes_table; end

Use this documentation http://www.sqlite.org/lang_createtrigger.html