仅在SQL Server中满足条件时触发触发 [英] Trigger to fire only if a condition is met in SQL Server

问题描述

我希望对于所有SQL人员来说这是一个简单的问题...

I hope this is a simple enough question for any SQL people out there...

我们有一个表来保存系统配置数据，并且该表与通过触发器的历史记录表，因此我们可以跟踪谁更改了内容以及更改的时间。

We have a table which hold system configuration data, and this is tied to a history table via triggers so we can track who changed what, and when.

我需要在此表中添加另一个值，但这是一个经常从代码中更改的值，并且要求我们不要跟踪它历史记录（我们不想每天阻塞成千上万的更新。

I have a requirement to add another value in to this table, but it is one that will change frequently from code, and has a requirement that we don't track it's history (we don't want to clog the table with many thousands of updates per day.

目前，我们的触发器有点像这样...

At present, our trigger is a little like this...

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

我希望能够添加一些逻辑来阻止其创建记录属性列的值以特定的字符串作为前缀（例如

I'd like to be able to add some logic to stop it creating the record if an Attribute colum value is prefixed with a specific string (e.g. "NoHist_")

鉴于我几乎没有使用触发器的经验，我想知道如何最好地实现它……我尝试了where子句如下所示

Given that I have almost no experience working with triggers, I was wondering how it would be best to implement this... I have tried a where clause like the following

where I.Attribute NOT LIKE 'NoHist_%'

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

but it doesn't seem to work. The value is still copied over into the history table.

您能提供的任何帮助将不胜感激。

Any help you could offer would be appreciated.

好-正如Cade Roux所预测的那样，这在多次更新中都失败了。我将不得不采取一种新的方法。有人有其他建议吗？

OK - as predicted by Cade Roux, this fail spectacularly on multiple updates. I'm going to have to take a new approach to this. Does anyone have any other suggestions, please?

人-请在这里教育我...为什么会使用LEFT（）在这种情况下比LIKE更可取？我知道我已经接受了答案，但是我想知道自己接受的教育。

Guys - Please educate me here... Why would LEFT() be preferable to LIKE in this scenario? I know I've accepted the answer, but I'd like to know for my own education.

推荐答案

鉴于WHERE子句不起作用，也许可以这样：

Given that a WHERE clause did not work, maybe this will:

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

这篇关于仅在SQL Server中满足条件时触发触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！