10

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

Improve this question
6
  • 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. Commented May 12, 2011 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. Commented May 12, 2011 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. Commented May 12, 2011 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. Commented May 12, 2011 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. Commented May 12, 2011 at 19:27

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

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?
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.
I am sorry but I feel like back to the square.
Give details about what update should happen in your notes table.
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.
|
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).

Improve this answer

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.