如何在SQLite中使用触发器为特定表设置最大项目数？
技术问答
260 人阅读
|
0 人回复
|
2023-09-12
|
|
这是一个简单的问题。1 O6 J4 ~) |& S5 D9 b8 y0 ^
背景基于时间戳字段，我应该在一个表中最多包含400行，所以旧行会自动删除。在这里，我们假设是3。
5 O% U+ z3 z( M5 t0 w: i表有多个字段，但时间戳在这里很重要。% s" p3 y( ?+ e! t" m$ N
问题即使我成功了，由于某种原因，它也使我最大程度地增加了其他项目，因此我只是对其进行了相应的调整。这意味着我得到了3个项目，而不是3个。
& l+ x. s" H' k: H+ [; Bprivate const val MAX_ITEMS = 3private val TIMESTAMP_FIELD = "timestamp"private val DELETE_FROM_CALL_LOG_TILL_TRIGGER = String.format( "CREATE TRIGGER %1\$s INSERT ON %2\$s WHEN (select count(*) from %2\$s)>%3\$s BEGIN DELETE FROM %2\$s WHERE %2\$s._id IN " "(SELECT %2\$s._id FROM %2\$s ORDER BY %2\$s.$TIMESTAMP_FIELD DESC LIMIT %3\$d,-1)；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；；END;" "delete_till_reached_max",TABLE_NAME,MAX_ITEMS - 1)我尝试过的我试过 :
% I; F* L# W7 R. H+ E+ r& I将条件更改为仅插入（表示没有WHEN零件）& Y; d, k8 Z8 i. R, y m* F* J% G) E
更改 LIMIT %3\$d,-1为LIMIT -1 OFFSET %3\$d。还尝试了不同于 -1”的数字（尝试0，因为我认为这是多余的）。问题[ol]+ g* a+ o) _7 l6 _, c* ?2 `$ a
为什么要用？MAX_ITEMS - 1而不是仅仅使用MAX_ITEMS？为什么我只剩下4个项目而不是3个项目？
. C/ {3 H0 x9 ~+ @' I我在WHEN有关系吗？好点吗？[/ol] 7 b& N* z. N7 ? ^! L4 n6 g9 Y
解决方案: |
|
|
|
|
|
|
|
|
|
