0

I am trying to create a trigger like below in sqlite android. It works fine in other sqlite engine but shows syntax error in android. Please help me to make this update statement work.

create trigger if not exists trigger_cascade_update_carry_forward 
after update on cf_details
begin
 update cf_details set cf = c_f from (with recursive dates_after(d) as 
  ( values(new.date) union
     select strftime('%Y-%m',d||'-01','+1 month') as next_month
     from dates_after where exists(select 1 from cf_details where date = next_month and account_id = new.account_id))
        select d,c_f from dates_after,(select cf+net as c_f from cf_details where account_id = new.account_id and date = strftime('%Y-%m',(select d from dates_after order by d limit 1)||'-01','-1 month')))                         where date = d;
     end;
Padmaja Seshadri
  • 21
  • 2
  • 1
    UPDATE...FROM syntax was introduced in SQLite in version 3.33.0 which is not supported up to API Level 32 of Android (https://stackoverflow.com/a/4377116/10498828). Also *Common table expression are not supported for statements inside of triggers* (https://www.sqlite.org/lang_createtrigger.html#syntax_restrictions_on_update_delete_and_insert_statements_within_triggers). – forpas Apr 17 '22 at 19:09

0 Answers0