SQL触发器删除最旧的副本

Question

我有以下表格

HOLIDAY_DATE_TABLE：

USE BillingUI;
CREATE TABLE HOLIDAY_DATE_TABLE
(
HID INT IDENTITY PRIMARY KEY,
TABLE_NUMBER nchar(2) NOT NULL,
HOLIDAY_DATE nchar(8) NOT NULL,
FIELD_DESCRIPTION nVARchar(43) NULL,
);

tbl8_update_transactions：

USE BillingUI;
CREATE TABLE tbl8_update_transactions
(
TID INT IDENTITY PRIMARY KEY,
TABLE_NUMBER nchar(2) NOT NULL,
HOLIDAY_DATE nchar(8) NOT NULL,
FIELD_DESCRIPTION nVARchar(43) NULL,
HID int,
FOREIGN KEY (HID) REFERENCES HOLIDAY_DATE_TABLE (HID)
);

我正试图为tbl8_update_transactions编写一个触发器。它的目标是识别具有重复外键值的记录，并删除该行的所有实例，但最近的实例除外(通过具有最高主键号(如auto_incrementing)可识别)。

我目前所拥有的是..。

CREATE TRIGGER tbl8_cleanup
ON tbl8_update_transactions
FOR INSERT
AS
BEGIN
SELECT HID, COUNT(*)
FROM tbl8_update_transactions
GROUP BY HID
HAVING COUNT(*) > 1;
DELETE FROM tbl8_update_transactions
WHERE COUNT(HID) > 1;
END; 

我不确定如何让触发器删除具有重复项的行的所有实例，只有具有最高主键号(TID)的实例除外。

Answer 1

我会在CTE中使用row_number()：

with todelete as (
      select t.*, row_number() over (partition by HID order by TID desc) as seqnum
      from tbl8_update_transactions t
     )
delete from todelete
    where seqnum > 1;

Answer 2

与其每次发生任何活动时都扫描整个表，不如更有针对性地：

CREATE TRIGGER tbl8_cleanup
ON tbl8_update_transactions
INSTEAD OF INSERT --<-- Act before the new rows have been inserted
AS
BEGIN
DELETE FROM tbl8_update_transactions where HID in (select HID from inserted)
INSERT INTO tbl8_update_transactions (/* column list */)
SELECT /* column list */ from inserted
END; 

因此，在插入任何新行之前，我们首先删除任何重复的行，然后执行实际的插入操作。

通常情况下，如果您的触发器代码没有引用inserted和/或deleted，那么它可能就坏了。有关更多信息，请参见文档。