logo标签列表

仅当在SQL Server中满足条件时触发触发器

sql-servertriggers
37

我希望这对于 SQL 专家来说是一个足够简单的问题...

我们有一张存储系统配置数据的表，并且通过触发器与一张历史表相关联，以便我们可以跟踪谁在何时更改了什么。

我需要向这个表中添加另一个值，但它是经常从代码中更改的值，并且要求我们不要跟踪其历史记录（我们不想让表格每天更新成千上万次而变得混乱）。

目前，我们的触发器类似于以下内容...

CREATE TRIGGER 
    [dbo].[SystemParameterInsertUpdate]
ON 
    [dbo].[SystemParameter]
FOR INSERT, UPDATE 
AS
  BEGIN
    SET NOCOUNT ON
      INSERT INTO SystemParameterHistory 
      (
        Attribute,
        ParameterValue,
        ParameterDescription,
        ChangeDate
      )
    SELECT
      Attribute,
      ParameterValue,
      ParameterDescription,
      ChangeDate
    FROM Inserted AS I
END

我希望能够添加一些逻辑，如果属性列的值以特定字符串（例如"NoHist_"）为前缀，则停止创建记录。

考虑到我几乎没有使用触发器的经验，我想知道最好如何实现这个功能...我尝试了以下where子句：

where I.Attribute NOT LIKE 'NoHist_%'

但是它似乎不起作用。该值仍然被复制到历史记录表中。

如果您能提供任何帮助，将不胜感激。

好吧 - 正如Cade Roux所预测的那样，这在多次更新时会失败。我将不得不采取新的方法来解决这个问题。请问还有其他建议吗？

各位 - 请教一下...在这种情况下，为什么LEFT()比LIKE更可取？我知道我已经接受了答案，但我想了解一下。

7个回答
50

考虑到 WHERE 子句没有起作用，也许尝试这个可以：

CREATE TRIGGER 
    [dbo].[SystemParameterInsertUpdate]
ON 
    [dbo].[SystemParameter]
FOR INSERT, UPDATE 
AS
  BEGIN
    SET NOCOUNT ON

      If (SELECT Attribute FROM INSERTED) LIKE 'NoHist_%'
      Begin
          Return
      End

      INSERT INTO SystemParameterHistory 
      (
        Attribute,
        ParameterValue,
        ParameterDescription,
        ChangeDate
      )
    SELECT
      Attribute,
      ParameterValue,
      ParameterDescription,
      ChangeDate
    FROM Inserted AS I
END
5
使用这个触发器时，您只处理了一行数据。如果有人同时插入/更新2行或更多行，会发生什么？数据就会出错。 - Mladen Prajdic
3
“”也是通配符，因此LIKE 'NoHist%'也会匹配'NoHistX'。 - Cade Roux
编辑队列已满，请更新语法。应该使用LIKE 'NoHist[_]'来取下划线。Left和Like不再有区别，在旧版本的Sql Sever中，sql并不总是擅长在6个字符后停止搜索。如果运行两个查询的执行计划，它们将是相同的。 - e-Fungus
这个 _ 是故意留下的。尽管它可以匹配任何字符，但在实际代码中我会保持它这样，因为这样更容易阅读，并且极不可能出现误报。 - Joel Coehoorn
7

这个怎么样？

CREATE TRIGGER 
[dbo].[SystemParameterInsertUpdate]
ON 
[dbo].[SystemParameter]
FOR INSERT, UPDATE 
AS
BEGIN
SET NOCOUNT ON
  IF (LEFT((SELECT Attribute FROM INSERTED), 7) <> 'NoHist_') 
  BEGIN
      INSERT INTO SystemParameterHistory 
      (
        Attribute,
        ParameterValue,
        ParameterDescription,
        ChangeDate
      )
    SELECT
      Attribute,
      ParameterValue,
      ParameterDescription,
      ChangeDate
   FROM Inserted AS I
END
END
1
我更喜欢使用LEFT()函数，而不是我自己提出的LIKE()建议。 - Joel Coehoorn
我对 SQL 服务器内部发生的事情了解不够，无法从性能角度判断哪种方法更好。实际上，我更喜欢你的方法，因为它立即退出而不是将整个 INSERT 包装在条件语句中。 - Matty
1
如果插入或更新多条记录，这个也会失败。 - Cade Roux
1
好的观点，Cade。为了使它正常工作，它必须遍历插入/更新。这将增加不必要的开销，使用您的代码片段可以避免这种情况。 - Matty
6

_字符也是通配符，顺便说一下，但我不确定为什么这对你不起作用：

CREATE TRIGGER 
    [dbo].[SystemParameterInsertUpdate]
ON 
    [dbo].[SystemParameter]
FOR INSERT, UPDATE 
AS
  BEGIN
    SET NOCOUNT ON
      INSERT INTO SystemParameterHistory 
      (
        Attribute,
        ParameterValue,
        ParameterDescription,
        ChangeDate
      )
    SELECT
      I.Attribute,
      I.ParameterValue,
      I.ParameterDescription,
      I.ChangeDate
    FROM Inserted AS I
    WHERE I.Attribute NOT LIKE 'NoHist[_]%'
END
5
您的 where 子句应该是可行的。我不知道为什么它没有起作用。让我向您展示如何找出 where 子句的问题，这可能会帮助您以后解决类似的问题。
当我创建触发器时，我从查询窗口开始，创建一个名为 #inserted（和/或 #deleted）的临时表，并拥有表中的所有列。然后我使用典型值填充它（始终是多个记录，并尝试在值中命中测试用例）。
然后编写触发器逻辑，可以在不实际存在触发器的情况下进行测试。在像您的 where 子句未按预期工作的情况下，我可以轻松地通过注释插入来查看选择返回了什么。然后我很可能能够轻松地看到问题所在。我向您保证，如果正确编写，where 子句确实可以在触发器中工作。
一旦我知道代码对所有情况都有效，我全局替换 #inserted 为 inserted，并添加创建触发器代码，完成测试的触发器。
正如我在评论中所说，我担心您选择的解决方案在多记录插入或更新时无法正常工作。触发器应始终编写以考虑到这一点，因为您无法预测它们将何时发生（并且它们最终会发生在几乎每个表中）。
当你说你有疑虑时，能否详细说明一下？我在这个串中没有看到你的另一个评论...如果你的意思是有可能会错误地删除非“ NoHist_”记录，你可以提出一个解决方案吗？ - ZombieSheep
是的，这就是为什么我认为让where子句正常工作是值得的。如果您查看您采用的解决方案，您会发现它返回NoHist记录，因此我怀疑它不会处理同一批次中具有不同值的任何记录。进行测试以确认。 - HLGEM
谢谢HLGEM，当我在应用程序上施加一些负载时，我会记住这个建议。 - ZombieSheep
2
CREATE TRIGGER
    [dbo].[SystemParameterInsertUpdate]
ON 
    [dbo].[SystemParameter]
FOR INSERT, UPDATE 
AS
  BEGIN
    SET NOCOUNT ON 

    DECLARE @StartRow int
    DECLARE @EndRow int
    DECLARE @CurrentRow int

    SET @StartRow = 1
    SET @EndRow = (SELECT count(*) FROM inserted)
    SET @CurrentRow = @StartRow

    WHILE @CurrentRow <= @EndRow BEGIN

        IF (SELECT Attribute FROM (SELECT ROW_NUMBER() OVER (ORDER BY Attribute ASC) AS 'RowNum', Attribute FROM inserted) AS INS WHERE RowNum = @CurrentRow) LIKE 'NoHist_%' BEGIN

            INSERT INTO SystemParameterHistory(
                Attribute,
                ParameterValue,
                ParameterDescription,
                ChangeDate)
            SELECT
                I.Attribute,
                I.ParameterValue,
                I.ParameterDescription,
                I.ChangeDate
            FROM
                (SELECT Attribute, ParameterValue, ParameterDescription, ChangeDate FROM (
                                                                                            SELECT ROW_NUMBER() OVER (ORDER BY Attribute ASC) AS 'RowNum', * 
                                                                                            FROM inserted)
                                                                                    AS I 
            WHERE RowNum = @CurrentRow

        END --END IF

    SET @CurrentRow = @CurrentRow + 1

    END --END WHILE
END --END TRIGGER
-1

使用LIKE将为您提供定义其余字符串应如何查找的选项，但如果规则只是以'NoHist_'开头，那么这并不重要。

-1

对于触发器，通常需要使用游标来处理多行的插入或更新。例如：

DECLARE @Attribute;
DECLARE @ParameterValue;
DECLARE mycursor CURSOR FOR SELECT Attribute, ParameterValue FROM inserted;
OPEN mycursor;
FETCH NEXT FROM mycursor into @Attribute, @ParameterValue;
WHILE @@FETCH_STATUS = 0
BEGIN

If @Attribute LIKE 'NoHist_%'
      Begin
          Return
      End

etc.

FETCH NEXT FROM mycursor into @Attribute, @ParameterValue;
END

触发器在SQL Server中非常麻烦，我尽量避免使用它们。

如果你从一开始就考虑到多行数据，那么你可以很好地处理多个插入或更新。在我看来，OP的第一次尝试（“where I.Attribute NOT LIKE 'NoHist_%'”）本应该这样做...不知道出了什么问题。 - Christian Severin
游标是不被推荐的，但如果必须使用游标，你应该至少 (a) 在可能的情况下将其声明为只读for以进行优化，(b) 使用游标变量实现批处理内的自动关闭和释放，(c) 或者如果您使用手动管理的游标，确切地打算close和deallocate它。 - underscore_d
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接