How can I run trigger in Room Database?

Question

I tried to implement trigger onCallBack() this way but it does neither throw error nor it works.

 private static Callback sRoomDatabaseCallback = new Callback(){

    @Override
    public void onOpen (@NonNull SupportSQLiteDatabase db){
        super.onOpen(db);
        // If you want to keep the data through app restarts,
        // new PopulateDbAsync(INSTANCE).execute();
        // You can run trigger for database house keeping jobs directly here
        db.execSQL("Create Trigger IF NOT EXISTS "+DbConfig.Trigr_NewsAnnouncementDelete +
                " After Insert On " + DbConfig.Tbl_NewsAnnouncement+
                " Begin Delete From "+DbConfig.Tbl_NewsAnnouncement +
                " WHERE DATE(CreatedDate) > DATE('now','-10 days');" +
                " END ");
    }
};

While in debugging I found NewsAnnouncementDatabase_Impl.java (Auto created file) has got following lines of code

    @SuppressWarnings("unchecked")
    public class NewsAnnouncementDatabase_Impl extends NewsAnnouncementDatabase 
    {
      private volatile NewsAnnouncementDao _newsAnnouncementDao;

      private volatile DatabaseHouseKeepingDao _databaseHouseKeepingDao;

      @Override
      protected SupportSQLiteOpenHelper createOpenHelper(DatabaseConfiguration configuration) {
        final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(1) {
          @Override
          public void createAllTables(SupportSQLiteDatabase _db) {
            _db.execSQL("CREATE TABLE IF NOT EXISTS `NewsAnnouncement` (`Id` TEXT NOT NULL, `Title` TEXT, `Image` TEXT, `Description` TEXT, `PublishDate` TEXT, `CreatedDate` TEXT, `UserName` TEXT, `ShortDescription` TEXT, PRIMARY KEY(`Id`))");

.....
...
}

Can somebody help me to figure out the problem in my trigger syntax or any other idea. I am trying to delete the last 10 days records from database using trigger.

Thank you.

Answer 1

The trigger syntax looks fine and works (see below), although the logic of deleting all rows that have a created date greater than 10 days ago may result in the inserted row being deleted unless the creation date for that row is 10 days or more in the past.

For the trigger to work the value stored in the CreatedDate column must be in an acceptable date format i.e. yyyy-mm-yy (e.g. 2018-09-11).

Consider the following (a test version of your SQL, with data):-

DROP TABLE IF EXISTS NewsAnnouncement;
DROP TRIGGER IF EXISTS Trigr_NewsAnnouncementDelete;
CREATE TABLE IF NOT EXISTS `NewsAnnouncement` (`Id` TEXT NOT NULL, `Title` TEXT, `Image` TEXT, `Description` TEXT, `PublishDate` TEXT, `CreatedDate` TEXT, `UserName` TEXT, `ShortDescription` TEXT, PRIMARY KEY(`Id`));

SELECT * FROM NewsAnnouncement;

Create Trigger IF NOT EXISTS Trigr_NewsAnnouncementDelete 
    AFTER Insert On NewsAnnouncement
    Begin Delete From NewsAnnouncement 
        WHERE DATE(CreatedDate) > DATE('now','-10 days');
  END
;

SELECT * FROM sqlite_master WHERE type = 'trigger';

INSERT INTO NewsAnnouncement VALUES
    ('test001','title001','image001','desc001','2018-01-01','2018-01-01','fred','shordesc001'),
    ('test002','title002','image002','desc002','2018-10-01','2018-10-01','fred','shordesc002'),
    ('test003','title003','image003','desc003','2018-12-01','2018-12-01','fred','shordesc003'),
    ('test004','title004','image004','desc004','2018-08-01','2018-08-01','fred','shordesc004'),
    ('test005','title005','image005','desc005','2018-12-01','2018-12-01','fred','shordesc005'),
    ('test006','title006','image006','desc006','2018/08/01','2018/08/01','fred','shordesc006'),
    ('test007','title007','image007','desc007','2018/12/01','2018/12/01','fred','shordesc007')
;

SELECT *,date(CreatedDate) FROM NewsAnnouncement;

The result will be :-

That is :-

1. The insert of the test001 row does nothing via the Trigger, as it's CreatedDate is over 10 days old.
2. The insert of the test002 row results in the row being deleted by the Trigger as it's CreatedDate is less then 10 days old.
3. The insert of the test003 row results in the row being deleted by the Trigger as it's CreatedDate is less then 10 days old.
4. The insert of the test004 row does nothing via the Trigger, as it's CreatedDate is over 10 days old.
5. The insert of the test005 row results in the row being deleted by the Trigger as it's CreatedDate is less then 10 days old.
6. The insert of the test006 row does nothing via the Trigger, as it's CreatedDate is not a valid date format (thus date(CreatedDate) returns null).
7. The insert of the test007 row does nothing via the Trigger, as it's CreatedDate is not a valid date format (thus date(CreatedDate) returns null).

The use of SELECT * FROM sqlite_master WHERE type = 'trigger'; could be used to see if the Trigger exists. The output would be along the lines of (just the highlighted row):-