如何在SQLite中使用触发器为特定表设置最大项目数? [英] How to set a max items for a specific table using Trigger, in SQLite?

问题描述

这是一个简单的问题.

我应该根据时间戳字段在某个表中最多容纳400行，因此旧表将被自动删除.在这里，我们假设它是3.

I'm supposed to have max of 400 rows in some table, based on timestamp field, so old ones will be removed automatically. For here, let's say it's 3 instead.

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

The table has various fields, but the timestamp is what's important here.

即使我成功了(在 此处 )，我最多只能添加一个其他项目，因此我只是对其进行了相应的调整.这意味着我得到了3个项目，而不是3个.

Even though I've succeeded (looked here), for some reason it got me to a max of an additional item, so I just adjusted it accordingly. This means that instead of 3, I got 4 items.

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)

我尝试过的

我尝试过:

What I've tried

I tried :

• 将条件更改为仅被插入(表示没有WHEN部分)
• 将LIMIT %3\$d, -1更改为LIMIT -1 OFFSET %3\$d.还尝试了一个不同于"-1"的数字(尝试0，因为我认为这是多余的).

• Change the condition to just being insertion (meaning without the WHEN part)
• Change LIMIT %3\$d, -1 to LIMIT -1 OFFSET %3\$d . Also tried a different number than "-1" (tried 0, because I thought it's extra).

1. 为什么我不得不使用MAX_ITEMS - 1而不是仅仅使用MAX_ITEMS?为什么我只剩下4个而不是3个?
2. 如果我在那里有WHEN，这有关系吗?更好吗?

1. How come I had to use MAX_ITEMS - 1 instead of just MAX_ITEMS ? Why does it leave me with 4items instead of 3 ?
2. Does it matter if I have WHEN there? Is it better?

推荐答案

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

1. You have omitted the BEFORE | AFTER clause, so it's BEFORE by default. This means you are counting the rows before the insert, not after it.
2. This depends. At first, when the table has not reached the limit yet, the quick count lookup may save you some time, as you avoid the more complicated delete. But as soon as the table is full, you'll have to delete anyway, so counting is just additional work to do.

这应该有效:

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删除.

Once there are 400 rows in the table, you can just as well call the trigger something like trg_keep_rowcount_constant and remove GROUP BY null HAVING COUNT(*) > %3\$s from the code.

演示: https://dbfiddle.uk/?rdbms=sqlite_3.27& ; fiddle = ea3867e20e85927a2de047908771f4f1

这篇关于如何在SQLite中使用触发器为特定表设置最大项目数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！