SQlite (Room) Trigger: two queries

Question

I have the following trigger:

database.execSQL("DROP TRIGGER IF EXISTS Trigger_AfterIncomeRecordInsert");
database.execSQL(
"CREATE TRIGGER IF NOT EXISTS Trigger_AfterIncomeRecordInsert " +
"AFTER INSERT ON IncomeRecord " +
"BEGIN  " +
"UPDATE FinanceRepeatment SET numOfCreatedRepeatments = numOfCreatedRepeatments +1, " +
"actIncomeRecordId = NEW.id WHERE NEW.financeRepId=FinanceRepeatment.id;" +
"DELETE FROM favourite_tiles WHERE favourite = NEW.id;" +
"END;");

If I only use one query either update/delete the query works perfect, however if I want to use both like in this case, I receive the following error: END expected, got 'DELETE'

Thats why my question is does SQLITE even support multiple queries like in this case or is the error due to a syntac error?

Answer 1

Your code is a valid sql statement and should work.

The only reason I can think of that it throws the error is because of a limitation of the method execSQL():

public void execSQL (String sql)
Parameters sql String: the SQL statement to be executed.
Multiple statements separated by semicolons are not supported.

A workaround would be to create 2 separate triggers:

CREATE TRIGGER Trigger_AfterIncomeRecordInsert_1 AFTER INSERT ON IncomeRecord 
BEGIN  
  UPDATE FinanceRepeatment 
  SET numOfCreatedRepeatments = numOfCreatedRepeatments +1, 
      actIncomeRecordId = NEW.id 
  WHERE NEW.financeRepId=FinanceRepeatment.id;
END;

CREATE TRIGGER Trigger_AfterIncomeRecordInsert_2 AFTER INSERT ON IncomeRecord 
BEGIN  
  DELETE FROM favourite_tiles 
  WHERE favourite = NEW.id;
END;