如何在SQLite中使用触发器为特定表设置最大项目数？

admin

如何在SQLite中使用触发器为特定表设置最大项目数？

sql

这是一个简单的问题。

背景

我应该基于时间戳字段在某个表中最多包含400行，因此旧的行将被自动删除。对于这里，我们假设它是3。

该表具有多个字段，但是时间戳在这里很重要。

问题

即使我成功了，由于某种原因，它也使我最大程度地增加了其他项目，因此我只是对其进行了相应的调整。这意味着我得到了3个项目，而不是3个。

private const val MAX_ITEMS = 3
private 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)

我尝试过的

我试过了：

将条件更改为仅插入（表示没有WHEN零件）
更改 LIMIT %3\$d, -1为LIMIT -1 OFFSET %3\$d。还尝试了一个不同于“ -1”的数字（尝试0，因为我认为这是多余的）。

问题

为什么我不得不使用MAX_ITEMS - 1而不是仅仅使用MAX_ITEMS？为什么我只剩下4个项目而不是3个项目？
我在WHEN那里有关系吗？好点吗？

阅读 178

2021-07-01

共1个答案

admin

您已省略了该BEFORE | AFTER子句，因此BEFORE默认情况下为该子句。这意味着您是在插入 之前（ 而不是插入之后）对行进行计数。
这取决于。首先，当表尚未达到限制时，快速计数查找可以为您节省一些时间，因为您可以避免更复杂的删除。但是，一旦表已满，无论如何都必须删除，因此计数只是要做的其他工作。

这应该工作：

private const val MAX_ITEMS = 3
private val TIMESTAMP_FIELD = "timestamp"

private val DELETE_FROM_CALL_LOG_TILL_TRIGGER =
  String.format(
    "CREATE TRIGGER %1\$s AFTER INSERT ON %2\$s 
     FOR EACH ROW
     BEGIN 
       DELETE FROM %2\$s WHERE _id =
         (SELECT _id FROM %2\$s ORDER BY %4\$s DESC LIMIT 1 OFFSET %3\$s);
     END;"
    , "delete_till_reached_max", TABLE_NAME, MAX_ITEMS, TIMESTAMP_FIELD)

一旦表中有400行，您就可以像这样调用触发器trg_keep_rowcount_constant并将其GROUP BY null HAVING COUNT(*) > %3\$s从代码中删除。

演示：https：//dbfiddle.uk/？rdbms
= sqlite_3.27＆fiddle =
ea3867e20e85927a2de047908771f4f1

2021-07-01