触发器不像预期的那样工作

Question

我的扳机有点问题。它应该是：

• 更新主表中插入的历史表，并添加时间戳。
• 如果历史表中的一行已经具有相同的值，则不应该添加任何内容，但是计数器应该增加，并且应该更新时间。
• 如果它是自上次更新以来超过24小时前，将创建一个新的行。

草率伪码：

IF NOT EXISTS ( --if the value isn't in the history table
    SELECT History.value1 FROM History, INSERTED
    WHERE History.value1 LIKE INSERTED.value1
    AND History.value2 LIKE INSERTED.value2
       ) 
       OR EXISTS ( --or if it has been added over 24h ago
        SELECT History.value1 FROM History, INSERTED
        WHERE History.value1 LIKE INSERTED.value1
        AND History.value2 LIKE INSERTED.value2
        AND DATEDIFF(HOUR,History.time, GETDATE()) > 24
       )     
    BEGIN --Insert it
        INSERT INTO History(value1, value2, counter, time)
        SELECT value1, value2, counter GETDATE() FROM INSERTED
    END
    ELSE
    BEGIN  -- else, increase counter and add new time
        UPDATE History
        SET History.time = GETDATE(), 
                               History.Items = History.Items + INSERTED.Items
        FROM History
        JOIN INSERTED ON History.value1 = INSERTED.value1
        AND History.value2 = INSERTED.value2
            AND DATEDIFF(HOUR, _History.time, GETDATE()) < 24;
    END

示例表：

__________________________________________________
| value1 | value2 | counter | time(last updated) |
+------------------------------------------------+
| test1  | test2  |    1    |        < 24h       |
| test3  | test4  |    1    |        > 24h       |
| test3  | test4  |    1    |        < 24h       |
+------------------------------------------------+

输入：

INSERT INTO main_table(value1, value2, counter)
VALUES ('test3', 'test4', 1);

结果表：

__________________________________________________
| value1 | value2 | counter | time(last updated) |
+------------------------------------------------+
| test1  | test2  |    1    |        < 24h       |
| test3  | test4  |    1    |        > 24h       |
| test3  | test4  |    1    |        < 24h       | <--This counter+time should be updated
| test3  | test4  |    1    |        < 24h       | <--This row shouldn't be added
+------------------------------------------------+

我理解为什么会发生这种情况(因为代码会发现一个超过24小时的历史值，而忽略较新的那个)，但我不知道如何修复它。

Answer 1

天哪，我解决了它^^试图修复它大约2个小时，就在我将它发布到堆栈溢出时，我设法修复了它^

我所做的：

将第二个“存在”测试更改为：

OR EXISTS ( --or if it has been added over 24h ago
        SELECT History.value1 FROM History, INSERTED
        WHERE History.value1 LIKE INSERTED.value1
        AND History.value2 LIKE INSERTED.value2
        AND DATEDIFF(HOUR,History.time, GETDATE()) > 24
       )     

至：

OR NOT EXISTS ( --or if it has been added over 24h ago
        SELECT History.value1 FROM History, INSERTED
        WHERE NOT History.value1 LIKE INSERTED.value1
        OR History.value2 LIKE INSERTED.value2
        OR DATEDIFF(HOUR,History.time, GETDATE()) > 24
       )     

Answer 2

我认为，如果inserted包含混合行，则触发器仍然中断--因为您的IF/ELSE结构对要采取的操作做出了单一的决定。

最好有一个MERGE，类似于：

;MERGE INTO History h USING INSERTED i
   ON h.Value1 = i.Value1 and h.Value2 = i.Value2 and
      DATEDIFF(HOUR,h.time, GETDATE()) <= 24
WHEN MATCHED THEN
   UPDATE SET time = GETDATE(), Items = h.Items + i.Items
WHEN NOT MATCHED THEN
   INSERT (Value1,Value2,Items,time)
   VALUES (i.Value1,i.Value2,i.Items,GETDATE());

这应该可以取代你的整个触发体。

顺便说一句，DATEDIFF计算跨越边界的转换数，而不是计算精确的差异(例如，DATEDIFF(hour,'00:59','01:01')是1)。如果您想接近24小时截止时间(同时考虑分钟和秒)，更好的比较方法是：

h.Time >= DATEADD(day,-1,GETDATE())

顺便说一句，它还允许使用包含History的time列的索引。

Answer 3

-像这样改变你的标准声明看起来不错。不管怎么说，你写的扳机真的不好。触发器应该设计为处理set not记录，因为一批插入/更新/删除只能触发一次。

IF NOT EXISTS ( --if the value isn't in the history table
SELECT History.value1 FROM History, INSERTED
WHERE History.value1 LIKE INSERTED.value1
AND History.value2 LIKE INSERTED.value2
   ) 
   OR EXISTS ( --or if it has been added over 24h ago
    SELECT INSERTED.value1 
    FROM INSERTED
    cross apply
        (select max(History.[time]) mx_time
        from    History
        where   History.value1 LIKE INSERTED.value1
                AND History.value2 LIKE INSERTED.value2
        ) as t
    WHERE DATEDIFF(HOUR,t.mx_time, GETDATE()) >= 24
   )     
BEGIN --Insert it
    INSERT INTO History(value1, value2, items, time)
    SELECT value1, value2, items, GETDATE() FROM INSERTED
END
ELSE
BEGIN  -- else, increase counter and add new time

    UPDATE History
    SET History.time = GETDATE(), 
                           History.Items = History.Items + INSERTED.Items
    FROM History
    JOIN INSERTED ON History.value1 = INSERTED.value1
    AND History.value2 = INSERTED.value2
        AND DATEDIFF(HOUR, History.time, GETDATE()) < 24;
END