9

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

Community
  • 1
  • 1
Shaista Naaz
  • 8,281
  • 9
  • 37
  • 50
  • Why is it that people asking questions about database issues are so reluctant to name their tables in the question? This isn't aimed at you only, Shaista (though you are 'guilty' of it in this question); it is a general observation about database-related questions on SO. – Jonathan Leffler May 12 '11 at 19:16
  • 1
    But I have named it as you can see in the code itself. It is directly from my code, only when I explained it kept it short just to point the direct question. My intention was clear may be got misinterpreted. – Shaista Naaz May 12 '11 at 19:21
  • 2
    @Jonathan - There are different issues related to work, nobody wants to reveal their work code to general public unless they are completely done. Just my opinion. – yogsma May 12 '11 at 19:22
  • 7
    @Jonathan: I'd guess the first table is called something like 'PATIENTS' and the second something like 'NOTES' or 'PATIENT_NOTES'. What exactly is your point? Regardless of what the actual table names are in Shaista's DB, knowing their names has no relevance to the question about using TRIGGER in an Android SQLite DB. – Squonk May 12 '11 at 19:26
  • @yogsma: I understand not naming work-related tables - but at least if there are names in the question (which need not be the same as in the work scenario), then the answers can talk consistently, rather than forcing everyone to come up with their own names for the tables, making it difficult to correlate different parts of different answers. @Shaista: you named them indirectly; DATABASE_PATIENT_TABLE and DATABASE_NOTES_TABLE are (presumably) variables holding the names of the tables. It is best to name them where the bullet points are: _One table is **Patients** ...; the other is **Notes**._ – Jonathan Leffler May 12 '11 at 19:27
  • Looks like no one is interested in answering this question and I am really surprised the way some people simply like to point out whether they feel the name of the table is as exact as they have written in their code instead of caring about the question. I am novice with SQLite concept but I do understand that it is not a difficult task to learn them, but I felt people in SO are really kind, as I had felt always before. Probably I got to learn it myself all alone and put it here so that someone later might get helped, irrespective of the name of the table they put in their sample code. – Shaista Naaz May 14 '11 at 20:57

3 Answers3

6

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

yogsma
  • 10,142
  • 31
  • 97
  • 154
  • 1
    Thanks for your answer. But some quick questions My insert function for patient database has name insertInPatientDb so should I write the exact name fr insert function or just insert as you said is enough ? and I have not modified anything in the update function so Should I change the update function also? – Shaista Naaz May 12 '11 at 19:26
  • Yes, you need to use database table name. Change the update function the way you want update should happen in second table. I just gave an example, read that documentation , very helpful. – yogsma May 12 '11 at 19:31
  • I am sorry but I feel like back to the square. – Shaista Naaz May 12 '11 at 19:38
  • Give details about what update should happen in your notes table. – yogsma May 12 '11 at 19:46
  • OKAY, from DATABASE_PATIENT_TABLE room_number can be modified. Means this is one separate activity where the rooms will either get added or deleted. and There is another activity called notes screen where I am adding some information depending on the rooms already present in the database. But as the rooms are modified in the first activity (either deleted some rooms or added some rooms) so my second activity database should also be updated. second activity has DATABASE_NOTES_TABLE table. I hope I am clear this time. – Shaista Naaz May 12 '11 at 19:52
  • Well I asked about NOTES table. What information are you updating in NOTES table when room is inserted or deleted from PATIENT table? – yogsma May 12 '11 at 20:05
  • In the notes table I have 1. row id 2. rooms id 3. time hour 4. notes text 5. today_date. when the rooms in the patient table is changed I want the rooms in the notes table to be modified accordingly. thats all I want. – Shaista Naaz May 13 '11 at 09:58
  • in my first table i.e patient table I have rooms number. this room number should be declared as a foreign key when I declare the schema for the notes screen, as in the notes screen I have a column called room which is the same one in the patient id. But I am still struggling as how to use trigger and get this trigger hit from my code. When I make any changes in the first screen which has the room numbers and I delete any room from here or add a new one it should automatically get replicated in the notes table. – Shaista Naaz May 14 '11 at 21:01
  • Hi, I have written another question where I have explained things with the screen shot form sqlite browser. please have a look to it.[link](http://stackoverflow.com/questions/6010682/android-sqlite-trigger-operation-go-through) – Shaista Naaz May 15 '11 at 19:41
4

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).

halfer
  • 19,824
  • 17
  • 99
  • 186
Ian Oxley
  • 10,916
  • 6
  • 42
  • 49
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.

Nitesh Tiwari
  • 4,742
  • 3
  • 28
  • 46