时间戳

首页 > 解决方案 > 使用触发器通过 X 字段从数据库中删除最后 N 个条目

sql - 使用触发器通过 X 字段从数据库中删除最后 N 个条目

问题描述

我在 SQL Server 中有一个表，它保存用户的密码历史记录，以防止用户重新使用以前使用的密码。

CREATE TABLE user_password_histories 
(
    id BIGINT IDENTITY CONSTRAINT user_password_histories_pk PRIMARY KEY NONCLUSTERED
    ,password VARCHAR(128) NOT NULL
    ,user_id BIGINT NOT NULL
    ,created_at DATETIME DEFAULT GETUTCDATE() NOT NULL
    ,updated_at DATETIME
)

但是，我只想为用户维护一个包含 10 个密码历史记录的列表。因此，我正在考虑使用触发器在插入期间仅为每个用户保留 10 个历史记录。

我想出的部分解决方案是这个

CREATE TRIGGER trg_user_password_histories  
ON user_password_histories
AFTER INSERT
AS
    DELETE FROM user_password_histories
    WHERE user_password_histories.user_id = Inserted.user_id // This will not work
      AND user_password_histories.id NOT IN (SELECT t2.id
                                             FROM user_password_histories t2
                                             WHERE t2.id = user_password_histories.id
                                             ORDER BY t2.created_at DESC 
                                                 OFFSET 10 ROWS)
GO

但这里的问题是Inserted可以有多个条目。因此，我不确定如何解决此问题或解决此问题的最佳方法。

标签： sqlsql-serverdatabasedatabase-trigger

解决方案

好的，最后我想我找到了解决方案。我用批量插入和单次插入对此进行了测试，它可以工作！！！我不得不用它cursor来解决这个问题。我在这里发布我的解决方案，以便其他人可以从中受益。

CREATE TRIGGER trg_user_password_histories_insert ON user_password_histories
    AFTER INSERT
    AS
    DELETE uph FROM user_password_histories uph
    WHERE uph.user_id IN (SELECT user_id FROM Inserted)
    AND uph.id NOT IN (
        SELECT id FROM (
            SELECT user_password_histories.id, ROW_NUMBER() OVER(PARTITION BY user_password_histories.user_id ORDER BY user_password_histories.created_at DESC) AS rn
            FROM user_password_histories
            WHERE user_password_histories.user_id IN (SELECT user_id FROM Inserted)
        ) t
        WHERE t.rn <= 10
    )
go

此解决方案可能不是在所有情况下都适用，但在这种情况下可以完美运行。

推荐阅读