Triggers

Triggers are a way to teach SQLite to manipulate Table X every time that you do something to Table Y. While to date Google has elected to not support triggers directly in Room, you can still set them up manually if needed.

In this chapter, we will examine how to do this.

Trigger Basics

Triggers are most often associated with server-side databases: Oracle, SQL Server, and so on. And triggers have their downsides. However, they are an available option on SQLite, which supports the CREATE TRIGGER statement to define a trigger.

You can think of triggers as being an “if this, then that” sort of construct:

• If we insert a row in Table X, update some data in Table Y
• If we modify a row in Table Y, also modify related rows in Table Z
• If we delete data from Table Z, insert a row in Table Q
• And so on

At a high level, the syntax for creating a trigger is:

CREATE TRIGGER [name] [timing] [action] ON [table]
BEGIN
  [SQL statements]
END;

Each trigger has a name, the same way that tables and views have names. The timing usually is BEFORE or AFTER, and the action is INSERT, DELETE, or UPDATE OF [columns], with the latter representing modification of some column(s) on one or more rows. So, you get combinations like:

• BEFORE INSERT
• AFTER UPDATE OF name
• BEFORE DELETE
• And so on

The SQL statements between BEGIN and END; will be executed whenever the action occurs on the specified table, either BEFORE or AFTER the action itself is performed. So, BEFORE executes the SQL statements before the action is applied to the table, while AFTER executes the SQL statements after the action is applied to the table.