参考: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(); } }
1 String deleteTrigger = "CREATE TRIGGER delete_till AFTER INSERT ON " + _table + " WHEN (select count(*) from " + _table + ")>" + _maxrow + " " + 2 "BEGIN " + 3 "DELETE FROM " + _table + " WHERE " + _table + ".id IN (SELECT " + _table + ".id FROM " + _table + " ORDER BY " + _table + ".id limit (select count(*) -"+_maxrow+" from " + _table + " ));" +" "+ 4 "END;";