SQL Server - 如何自动删除“旧”数据库数据库记录？

Question

我有一个数据库表，用于存储用户的商店列表。我希望每个用户只存储 12 个商店列表，意味着如果当前 user1 在表中有 12 条记录，一旦 user1 创建新的商店列表，第一个商店列表（最旧的）将被删除，新的商店列表将被存储。

ShopList 表由 ShopListID (PK)、UserID (FK) 和 LastUpdatedDate 组成，一旦用户插入/删除属于商店列表的任何商店列表项，触发器就会更新该表。

我根本不知道该怎么做……是使用触发器吗？或者存储过程？这里真的需要帮助...

感谢任何反馈..谢谢...

Answer 1

您可以通过触发器或过程来执行此操作。您还可以在服务层/业务逻辑层中查询保存时的计数并删除旧记录。我支持业务逻辑方法，因为它更易于测试，并且使业务逻辑不受触发器或过程的影响，因此我的建议是基于代码的方法。

Answer 2

我个人会将选择查询更改为仅选择前 12 个，以便控制用户可以看到的内容。

然后，我将使用按计划运行的数据库作业来删除您不需要的作业。

Answer 3

我最近遇到了这个问题，这实际上取决于您的“归档”策略。

我所做的是创建一个存储过程，为每个用户帐户选择要存档的记录元素（我的要求与您的非常相似，因为我必须在用户帐户中选择第 31 个元素）。如果您认为有用的话，我还可以在这里给您一些代码。

我创建了一个名为 XXXX_archive 的额外表，它是 shopping_list 表上架构的克隆。这是为了在其中插入旧的存档记录，以防用户将来要求检索他的列表（这显然是可选的，但会派上用场）。

存储过程找到该记录并将它们插入 XXXX_archive 表中，然后从 XXXX 中删除它们。这通过 SQL Server 代理每晚运行（或者当您认为有必要时）。

其效果是，当用户创建另一个购物清单时，第 13 个元素不会被删除，但我认为这很好，因为您负责您的归档策略，并且可以在您的 TOS 中对其进行描述。

只是想我应该在这里写下我的经验，因为我几天前解决了这个问题。

编辑：我的存储过程如下：就是这样

INSERT into shopping_lists_archive
    SELECT *
    FROM shopping_lists 
    WHERE id in (
        select id
        from (
            SELECT ROW_NUMBER() OVER ( 
               PARTITION BY user_ID  
               ORDER BY user_ID desc) AS RowNumber,  
            id, user_ID
            FROM shopping_lists c
            where c.user_ID in (select USER_ID from shopping_lists group by user_id having COUNT(1) > 12)
        ) t
        where rownumber > 12
    )

DELETE FROM shopping_lists 
        WHERE id in (
            select id
            from (
                SELECT ROW_NUMBER() OVER ( 
                   PARTITION BY user_ID  
                   ORDER BY user_ID desc) AS RowNumber,  
                id, user_ID
                FROM shopping_lists c
                where c.user_ID in (select USER_ID from shopping_lists group by user_id having COUNT(1) > 12)
            ) t
            where rownumber > 12
        )

- 它可能与您需要的略有不同，因为我基于两个表之间的联接进行归档，并且必须根据您的要求修改我的原始查询。