Triggers the Hard Way

So, back in 2017, Adam McNeilly worked out the basic mechanics of setting up a trigger, by using the onCreate() callback function on a RoomDatabase.Callback.

The Trigger module of the book’s primary sample project demonstrates this approach.

In this project, we have two Room-managed tables:

• randomStuff, containing some random entities that we insert
• countOfRandomStuff, which contains the count of the number of entities in randomStuff, that for some strange reason we want to store in a separate table

To keep countOfRandomStuff up to date with respect to entities being inserted into randomStuff, we could use the following trigger:

CREATE TRIGGER updateCount AFTER INSERT ON randomStuff
BEGIN
 UPDATE countOfRandomStuff SET count = (SELECT COUNT(*) FROM randomStuff);
END;

(in principle, we also need an AFTER DELETE trigger, but since this sample app never deletes data from randomStuff, we can skip it)

The UPDATE countOfRandomStuff statement will update after one or more rows are inserted into randomStuff. The UPDATE countOfRandomStuff statement updates all of its rows to have a count column reflect the count of rows in randomStuff. As it turns out, we will only have one row in that countOfRandomStuff table.

We set up countOfRandomStuff using a Room @Entity:

package com.commonsware.room.trigger

import androidx.room.Dao
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.Query

@Entity(tableName = "countOfRandomStuff")
data class CountEntity(
  @PrimaryKey(autoGenerate = true) val id: Long,
  val count: Int
) {
  @Dao
  interface Store {
    @Query("SELECT count FROM countOfRandomStuff LIMIT 1")
    suspend fun getCurrent(): Int
  }
}

That CountEntity also has a nested DAO interface, describing a getCurrent() function that will return the count value for the first row in the countOfRandomStuff table.

RandomDatabase not only hooks up both the CountEntity and the RandomEntity (that defines the randomStuff table), but it also does some extra work when the database is created:

package com.commonsware.room.trigger

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.TypeConverters
import androidx.sqlite.db.SupportSQLiteDatabase

private const val DB_NAME = "random.db"

@Database(entities = [RandomEntity::class, CountEntity::class], version = 1)
@TypeConverters(TypeTransmogrifier::class)
abstract class RandomDatabase : RoomDatabase() {
  abstract fun randomStore(): RandomStore
  abstract fun countStore(): CountEntity.Store

  companion object {
    fun newInstance(context: Context) =
      Room.databaseBuilder(context, RandomDatabase::class.java, DB_NAME)
        .addCallback(object : RoomDatabase.Callback() {
          override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)

            db.execSQL("INSERT INTO countOfRandomStuff (count) VALUES (0);")
            db.execSQL(
              """
CREATE TRIGGER updateCount AFTER INSERT ON randomStuff
BEGIN
 UPDATE countOfRandomStuff SET count = (SELECT COUNT(*) FROM randomStuff);
END;
""".trimIndent()
            )
          }
        })
        .build()
  }
}

We use addCallback() and a RoomDatabase.Callback object to get control when the database is created, via onCreate(). In there, we do two things:

1. Insert a single row into countOfRandomStuff, so our trigger always has a row to update; and
2. Executes the CREATE TRIGGER shown earlier in this chapter, to define our trigger

Now, every time that we insert rows into randomStuff, countOfRandomStuff will get updated. We can access the countOfRandomStuff data via CountEntity and its DAO, just like any other Room-managed table. That is because countOfRandomStuff is a Room-managed table, just one whose contents are set up via the onCreate() callback and the trigger, rather than via calls on the DAO itself.