Last Updated: February 24, 2015

Android Sqlite Trigger Demo


Description:

Triggers are some structured code that are executed  automatically when certain events occurred in our database. Events can be like INSERT, DELETE, UPDATE.

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

Trigger to delete records from library and canteen table:
 CREATE TRIGGER if not exists delete_student   
   AFTER DELETE   
 ON[student]  
  for each row  
    BEGIN  
        delete from library where sid = old.sid;  
        delete from library where sid = old.sid;  
    END;  

Explanation:The concept here is to  delete a record from student which thereby delete the values from library and canteen with old id of student.



CODE:
 public class MainActivity extends ActionBarActivity {  
   @Override  
   protected void onCreate(Bundle savedInstanceState) {  
     super.onCreate(savedInstanceState);  
     setContentView(R.layout.activity_main);  
     DatabaseHelper databaseHelper = new DatabaseHelper(this , DatabaseHelper.DB_NAME , null , DatabaseHelper.version);  
     databaseHelper.insertIntoStudent("100" , "Lionel Messi");  
     //Similarly if we delete any record the trigger get fired:e.g  
     //databaseHelper.deleteFromStudent("100");  
   }  
   public class DatabaseHelper extends SQLiteOpenHelper {  
     static final public String DB_NAME = "trigger_demo";  
     static final public int version =1;  
     public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {  
       super(context, name, null, version);  
     }  
     @Override  
     public void onCreate(SQLiteDatabase sqLiteDatabase) {  
       sqLiteDatabase.execSQL("CREATE TABLE student (sid INTEGER PRIMARY KEY, sname TEXT)");  
       sqLiteDatabase.execSQL("CREATE TABLE canteen (cid INTEGER PRIMARY KEY, sid TEXT)");  
       sqLiteDatabase.execSQL("CREATE TABLE library (lid INTEGER PRIMARY KEY, sid TEXT)");  
       sqLiteDatabase.execSQL(insertRecordTrigger()); // create trigger  
       sqLiteDatabase.execSQL(deleteRecordTrigger()); // delete trigger  
     }  
     @Override  
     public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {  
       sqLiteDatabase.execSQL("DROP table student");  
       sqLiteDatabase.execSQL("DROP table student");  
       sqLiteDatabase.execSQL("DROP table student");  
       sqLiteDatabase.execSQL("DROP trigger add_student"); // Drop trigger  
       sqLiteDatabase.execSQL("DROP trigger delete_student"); // Drop trigger  
       onCreate(sqLiteDatabase);  
     }  
     public String deleteRecordTrigger(){  
       String deleteRecord = "CREATE TRIGGER if not exists delete_student " +  
           " AFTER DELETE " +  
           " ON[student] " +  
           " for each row " +  
           " BEGIN " +  
           "  delete from library where sid = old.sid; " +  
           "  delete from library where sid = old.sid; " +  
           " END; ";  
       return deleteRecord;  
     }  
     public String insertRecordTrigger(){  
       String insertRecord = "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;";  
       return insertRecord;  
     }  
     /**  
      * Insert new student record into student table which eventually fire trigger and insert record into canteen and library  
      */  
     public void insertIntoStudent(String sid , String sname){  
       ContentValues insertValues = new ContentValues();  
       insertValues.put("sid", sid);  
       insertValues.put("sname", sname);  
       SQLiteDatabase db = getWritableDatabase();  
       db.insert("student", null, insertValues);  
     }  
     /**  
      * Delete student record from student table which eventually fire trigger and delete record from canteen and library  
      */  
     public void deleteFromStudent(String sid){  
       SQLiteDatabase db = getWritableDatabase();  
       db.execSQL("delete from student where sid = '"+sid+"'");  
     }  
   }  
 }  



Output:



8 comments :

  1. Thanks for your greatful post.
    I also refer very helpful and useful article about SQLite- TRIGGERS EXAMPLE.
    Please Visit this helpful article
    http://www.mindstick.com/Articles/c34ac26a-f9fb-4b30-a3c1-3d486b45e5ca/SQLite%20TRIGGERS%20EXAMPLE
    http://www.tutorialspoint.com/sqlite/sqlite_triggers.htm

    ReplyDelete
    Replies
    1. Thanks Ashish. Apology for such a late reply won't be happening again.
      Pls do check http://www.coderconsole.com for new interesting stuffs in android
      #codingisanart @coderconsole

      Delete
  2. I'm curently making an android apps that can be use to save daily income/outcome. Then I wonder can I make a notification in android that occur from sqlite trigger action. Well the answer is obviously cant (from resource http://www.sqlite.org/lang_createtrigger.html) except making a custom function for it.

    And this is my first though on how to create trigger on my apps,in short I have 2 tables named "transaction" and "sumtrans". Transaction table has schema named , and that consist of 2 option . Then another table is only , and . Purpose of this table is only to contain the total amount of income/outcome in the other table by month, then i'll make function to compare between income and outcome.at certain calculation the function will make notification about result of the calculation.

    So i create this trigger to automatically insert the sum to the table "sumtrans". forgive me its still sqlite code based on the website above, you could tell me about your opinion to fix it,i'll change it to android sqlite version later.

    CREATE TRIGGER IF NOT EXIST sumcalc_income
    AFTER INSERT ON [transaction]
    FOR EACH ROW
    WHEN
    SELECT sum(income) FROM amount.transaction
    WHERE strftime (%Y-%m,now,start of month)
    BEGIN
    INSERT INTO income.sumtrans
    DEFAULT VALUES
    END;
    END;


    My question is

    1. Did i do it right?
    2. From the refference site above. At the insert statement section, should i do with DEFAULT VALUES or use the other?

    3. Which is better between trigger that UPDATE or REPLACE query on table "sumtrans"?
    4. From your tutorial should we have that many trigger? can we summarize it?
    Thanks before.

    ReplyDelete
    Replies
    1. Fews key points for your issue.

      If you want to sent a notification from the trigger you have created
      - Create a contentProvider with some Uri and register whereever you want to know if anything is inserted/deleted or update.

      - From your trigger snippets its difficult to figure out what is the schema you are accessing.
      - To debug you can try below steps
      a) Try to create the trigger first in the sqlite with your schema
      b) Then create an insert into your transation table & check if sumtrans is populated
      c) You can then now ready to use your trigger.

      Thanks

      Delete
    2. Thanks for replying.

      -if i use contentProvider with Uri, did it take data from my database? well you can call it my experiment project. I want to make a android app notification (push notification) that occur when trigger inside my sqlite db is fired.

      for example:
      whenever user INSERT new query the trigger will SUM it up then place (it can be UPDATE or REPLACE) the result on the table "sumtrans" differ by each column "month","income" and "outcome". There will be a function to read query from "sumtrans" to compare between "income" and "outcome" each "month". Then when outcome data of a user already reaching 70% of its income then the function will notify user using push notification.

      that sums up my intention do you have any suggestion?

      Thanks.

      Delete
    3. As pointed out earlier you can try 'Content provider' by implementing your logic for calulating percentage of outcome v/s income.

      So when ever you insert something into the transation table as per where you have implemented your 'Observer' you gonna get notified that new row is inserted and now you can apply your function of calulating percentage i.e 70% or greater & create a notification from there.

      getContentResolver().
      registerContentObserver(
      SOME_URI,
      true,
      transationObserver);

      Also getting output from trigger is a bit tricky, since its not the real purpose of the trigger although you can write your logic for insert/delete/update/select.

      Thanks

      Delete
    4. Thanks for your suggestion sir, but i think i'll pass on Content Provider because my theme is about SQLite Trigger manipulation that can fire android notification "manually" haha
      anyway can i ask ur email/fb? i'll show u my code later so u can check on it

      and i wanna ask what's better between these code?

      String insinc = "CREATE TRIGGER if not exists sumcalc_income"
      + "AFTER INSERT"
      + "ON"+TRANSACTION_TABLE
      + "FOR EACH ROW"
      + "WHEN"
      + "TRANS.type= income"
      + "WHERE strftime('%Y'-'%m',now,start of month)"
      + "BEGIN"
      + "insert into SUMTRANS.income"
      + "SELECT SUM(income) FROM TRANS.amount"
      + "END;";

      return insinc;
      }

      compared to this one

      String insinc = "CREATE TRIGGER if not exists sumcalc_income"
      + "AFTER INSERT"
      + "ON"+TRANSACTION_TABLE
      + "FOR EACH ROW"
      + "WHEN"
      + "TRANS.type= income"
      + "WHERE tdate BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime')"
      + "BEGIN"
      + "insert into SUMTRANS.income"
      + "SELECT SUM(income) FROM TRANS.amount"
      + "END;";

      return insinc;
      }

      which one will work better?
      the 1st one is taking reference from http://www.sqlite.org/lang_createtrigger.html
      and thw 2nd one is from some forums.

      Thanks You sir.

      Delete
  3. aw my reply didnt show up again >.<

    ReplyDelete

Your comments are valuable for us !!!