How to use TRIGGER in Android SQLite

Question

I have two tables in the database:

• The table has a name and a column number number.
• Table 2 has a number number and a time column.

Now that the room number from the first column is removed or added, my second table should also be updated. I think this is possible with the TRIGGER command, but I'm not sure how to use it.

Typically, my database creation statement is as follows:

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 that the rooms are deleted or added to the first table, my second table needs to 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 comparing the contents of two tables. But this will definitely lead to a performance issue later as the data grows. So I came across TRIGGER. I think this can solve my problem, but I don’t know how exactly I should use it.

I found out from Using SQLite Database with Android .

I explained this problem with a screenshot in another question. Please take a look at it, and if please, please help me a new question

Answer 1

A 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

Depending on the version of SQLite on which your application is running , you may use SQLite foreign key support .

In the old version of SQLite, you can use the genfkey utility to create triggers to enforce restrictions on foreign key constraints (older versions of SQLite will analyze foreign key constraints added during the CREATE TABLE statement, but will not actually implement them).

Hope this helps.

Answer 3

Demo for Sqlite Trigger on Android HERE

A trigger is some procedural code that is executed after a certain event has occurred in our database.

I wrote a sample demo for a trigger.

Example. Consider the database of any University. Therefore, if a student record is added to the student table, a new row (tuple) is automatically added to the library section or dining room section, etc.

Thus, by writing a simple trigger, we can automatically insert new entries into other sections, avoiding the boiler plate code.

Scheme

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

A trigger for automatically adding entries to the library and 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 that inserts values ​​into the dining room and library based on the new student ID.