Sqlite triggers to bump Powerdns zone serial on insert/update/delete of record

soas.sql

-- SOAs table since split in sqlite is tricky
CREATE TABLE soas (
  id         INTEGER PRIMARY KEY,
  domain_id  INTEGER NOT NULL,
  mname      VARCHAR(255) NOT NULL COLLATE NOCASE,
  rname      VARCHAR(255) NOT NULL COLLATE NOCASE,
  serial     INTEGER NOT NULL,
  refresh    INTEGER NOT NULL,
  retry      INTEGER NOT NULL,
  expiry     INTEGER NOT NULL,
  nxdomain   INTEGER NOT NULL
);

triggers.sql

drop trigger if exists bump_serial_on_insert;

create trigger bump_serial_on_insert insert on records
when new.type <> "SOA"
begin
  update soas set serial=
   (select case
      when substr(serial,0,9) < strftime('%Y%m%d', 'now')
        then strftime('%Y%m%d01', 'now')
        else serial + 1
      end
    from soas where domain_id = new.domain_id)
  where domain_id = new.domain_id;

  update records set content =
   (select  mname||" "||rname||" "||serial||" "||refresh||" "||retry||" "||expiry||" "||nxdomain
    from soas where domain_id = new.domain_id)
  where domain_id = new.domain_id and type = 'SOA';
end;

drop trigger if exists bump_serial_on_update;

create trigger bump_serial_on_update update on records
when new.type <> "SOA"
begin
  update soas set serial=
   (select case
      when substr(serial,0,9) < strftime('%Y%m%d', 'now')
        then strftime('%Y%m%d01', 'now')
        else serial + 1
      end
    from soas where domain_id = new.domain_id)
  where domain_id = new.domain_id;

  update records set content =
   (select  mname||" "||rname||" "||serial||" "||refresh||" "||retry||" "||expiry||" "||nxdomain
    from soas where domain_id = new.domain_id)
  where domain_id = new.domain_id and type = 'SOA';
end;

drop trigger if exists bump_serial_on_delete;

create trigger bump_serial_on_delete delete on records
when old.type <> "SOA"
begin
  update soas set serial=
   (select case
      when substr(serial,0,9) < strftime('%Y%m%d', 'now')
        then strftime('%Y%m%d01', 'now')
        else serial + 1
      end
    from soas where domain_id = old.domain_id)
  where domain_id = old.domain_id;

  update records set content =
   (select  mname||" "||rname||" "||serial||" "||refresh||" "||retry||" "||expiry||" "||nxdomain
    from soas where domain_id = old.domain_id)
  where domain_id = old.domain_id and type = 'SOA';
end;