Search code examples
sqlitedatetriggersandroid-sqliteandroid-room

Use SQLITE/Room table columns as function params

So I came across the following sides:

sqlite dates

sqlite dates functions

Which explains how the date function can be used in SQLITE (which is the equilvanet to dateadd in sql)

So I came to an idea, to store the date function parameters (interval, value) in the table which I will want to use in the trigger. This kinda looks like this:

database.execSQL(
"CREATE TRIGGER IF NOT EXISTS tr_afMR " +
"AFTER INSERT ON Table1 " +
"BEGIN  " +
"UPDATE Table2 SET nextRepeatDate = date(nextRepeatDate,+interval repeatmentType)" +
"WHERE NEW.idTable2=Table2.idTable; " +
"END;");

In this case interval (= 1,2,..) and repeatmentType(DAY,MONTH,YEAR) are parameters in Table2, however the issue is that sqlite does not recognize the table2 columns as params: '(', ')', '.', BETWEEN or IN expected, got 'repeatmentType'

Is it even possible to use table columns in sqlite as params? Or is this kind of function only possible in sql?

Solution

  • The 2nd argument of the function date() is a string and you can construct it by concatenating interval and repeatmentType:

    CREATE TRIGGER IF NOT EXISTS tr_afMR 
AFTER INSERT ON Table1 
BEGIN  
  UPDATE Table2 
  SET nextRepeatDate = date(nextRepeatDate, interval || ' ' || repeatmentType)
  WHERE idTable = NEW.idTable2; 
END;

    The expression interval || ' ' || repeatmentType will be evaluated to something like '3 day'. There is no need for the + sign.