sql数据库触发器

• 创建sqlite触发器：

  /**
   * 创建删除触发器，删除超出
   * @param db 数据库
   */
  private void createDeleteTrigger(SQLiteDatabase db){
      String triggerDeleteOverflow="create trigger overflow_delete " +
              "before delete on " + TABLE_NAME + " " +
              "for each row " +
              "begin " +
              "delete from " + TABLE_NAME + " where id=old.id; " +
              "end;";
  
      db.execSQL(triggerDeleteOverflowNIBP);
  }

/**
 * 创建数据删除触发器，删除超出10H数据
 * @param db 数据库
 */
private void createDeleteTrigger(SQLiteDatabase db){
    //创建删除趋势数据触发器
    int total = MAX_MS + OVERFLOW_MS;
    String triggerDeleteOverflow="create trigger overflow_delete " +
            "before insert on " + TABLE_NAME + " " +
            "for each row " +
            "begin " +
            "delete from " + TABLE_NAME +
            " where id in (select id from " + TABLE_NAME +
            " where (select count(id) from " + TABLE_NAME + ") >=" + total + " order by Time asc limit " + MAX_MS + "); " +
            "end;";

    logger.debug(TAG + "triggerDeleteOverflow: " +triggerDeleteOverflow);
    db.execSQL(triggerDeleteOverflow, new String[]{});
}

• 查询触发器

SELECT name FROM sqlite_master WHERE type = 'trigger';