sql 基础--触发器，如何限制表最大行数

参考：http://blog.csdn.net/love_android_2011/article/details/20137385

http://blog.csdn.net/lihuibo128/article/details/43667865

 

语法

创建触发器的基本语法如下：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

在这里，event_name 可能是INSERT，DELETE和UPDATE操作所提到的表table_name数据库。您可以选择指定FOR EACH ROW表名后。

以下是语法上创建一个触发器UPDATE操作一个或多个指定一个表列如下：

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;


例子：

CREATE TRIGGER delete_till_50 INSERT ON _table WHEN (select count(*) from _table)>50 
BEGIN
    DELETE FROM _table WHERE _table._id IN  (SELECT _table._id FROM _table ORDER BY _table._id limit (select count(*) -50 from _table ));
END;

　　注意：1.默认是 BEFORE，即在插入时判断执行 左右最终表会有51条数据

　　　　　2.BEGIN 要与前后与空格

所以用ormlite 语句改写为：

    // 初次运行程序会执行该onCreate方法,如果不是初次运行程序则不会执行该方法,防止重复建表。
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
        try {
            //创建表
            TableUtils.createTable(connectionSource, CommentMessageItemBean.class);

            //设置触发器限制表条数上限

            String _table = "tb_comment_message_item";
            String _maxrow = 1000 + "";

            String deleteTrigger = "CREATE TRIGGER delete_till AFTER INSERT ON " + _table + " WHEN (select count(*) from " + _table + ")>" + _maxrow + " " +
                    "BEGIN " +
                    "DELETE FROM " + _table + " WHERE " + _table + ".id IN  (SELECT " + _table + ".id FROM " + _table + " ORDER BY " + _table + ".id limit (select count(*) -"+_maxrow+" from " + _table + " ));" +" "+
                    "END;";

            sqLiteDatabase.execSQL(deleteTrigger);

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

            String deleteTrigger = "CREATE TRIGGER delete_till AFTER INSERT ON " + _table + " WHEN (select count(*) from " + _table + ")>" + _maxrow + " " +
                    "BEGIN " +
                    "DELETE FROM " + _table + " WHERE " + _table + ".id IN  (SELECT " + _table + ".id FROM " + _table + " ORDER BY " + _table + ".id limit (select count(*) -"+_maxrow+" from " + _table + " ));" +" "+
                    "END;";