仅在 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

如果属性列值以特定字符串(例如NoHist_")为前缀，我希望能够添加一些逻辑来阻止它创建记录

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屋！