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

Answer 2

Demo for Sqlite Trigger in Android HERE

Trigger are some procedural code executed after certain event occur in our database.

I have wrote a sample demo for trigger.

Example: Consider a database of any University. So if any Student record is added in student table , new row(tuple) is added automatically in library section or canteen section etc.

So by writing a simple trigger we can automatically insert new records in other sections avoiding boiler plate code.

Schema

 CREATE TABLE student (sid INTEGER PRIMARY KEY, sname TEXT)  
 CREATE TABLE canteen (cid , sid )  
 CREATE TABLE library (lid INTEGER PRIMARY KEY, sid TEXT)

Trigger to automatically add records in library and canteen table:

CREATE TRIGGER if not exists add_student   
   AFTER INSERT  
 ON[student]  
   for each row  
     BEGIN  
        insert into library values (2 , new.sid );  
        insert into canteen values (3 , new.sid);  
     END; 

Explanation:The concept here is to create a trigger ,which insert the values in canteen and library based on new student id.

Answer 3

Depending on which version of SQLite your app is running on, you might be able to use SQLite's foreign key support.

In older version's of SQLite you might be able to use the genfkey utility to create triggers to enforce your foreign key constraints (older versions of SQLite would parse foreign key constraints added during a CREATE TABLE statement, but wouldn't actually implement them).

Hope this helps.