SQL Server: trigger to fire only if a condition is met

Question

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_")

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.

Answer 1

Maybe this:

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

Answer 2

How about this?

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

Answer 3

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.

Thanks :)

Answer 4

Your where clause should have worked. I am at a loss as to why it didn't. Let me show you how I would have figured out the problem with the where clause as it might help you for the future.

When I create triggers, I start at the query window by creating a temp table called #inserted (and or #deleted) with all the columns of the table. Then I popultae it with typical values (Always multiple records and I try to hit the test cases in the values)

Then I write my triggers logic and I can test without it actually being in a trigger. In a case like your where clause not doing what was expected, I could easily test by commenting out the insert to see what the select was returning. I would then probably be easily able to see what the problem was. I assure you that where clasues do work in triggers if they are written correctly.

Once I know that the code works properly for all the cases, I global replace #inserted with inserted and add the create trigger code around it and voila, a tested trigger.

AS I said in a comment, I have a concern that the solution you picked will not work properly in a multiple record insert or update. Triggers should always be written to account for that as you cannot predict if and when they will happen (and they do happen eventually to pretty much every table.)

Answer 5

Using LIKE will give you options for defining what the rest of the string should look like, but if the rule is just starts with 'NoHist_' it doesn't really matter.

Answer 6

The _ character is also a wildcard, BTW, but I'm not sure why this wasn't working for you:

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

Answer 7

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?

Answer 8

For triggers in general, you need to use a cursor to handle inserts or updates of multiple rows. For example:

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

Triggers, at least in SQL Server, are a big pain and I avoid using them at all.

Answer 9

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