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:
