Trigger to fire only if a condition is met in SQL Server

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt    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

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt   NoHist       BEGIN       INSERT INTO SystemParameterHistory                  Attribute          ParameterValue          ParameterDescription          ChangeDate             SELECT       Attribute        ParameterValue        ParameterDescription        ChangeDate    FROM Inserted AS I END END

User · Answer

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

User · Answer

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

User · Answer

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  lt    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

User · Answer

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

User · Answer

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   lt  gt   NoHist       BEGIN       INSERT INTO SystemParameterHistory                  Attribute          ParameterValue          ParameterDescription          ChangeDate             SELECT       Attribute        ParameterValue        ParameterDescription        ChangeDate    FROM Inserted AS I END END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt   NoHist       BEGIN       INSERT INTO SystemParameterHistory                  Attribute          ParameterValue          ParameterDescription          ChangeDate             SELECT       Attribute        ParameterValue        ParameterDescription        ChangeDate    FROM Inserted AS I END END

User · Answer

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

User · Answer

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   lt  gt   NoHist       BEGIN       INSERT INTO SystemParameterHistory                  Attribute          ParameterValue          ParameterDescription          ChangeDate             SELECT       Attribute        ParameterValue        ParameterDescription        ChangeDate    FROM Inserted AS I END END

User · Answer

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] Trigger to fire only if a condition is met in SQL Server

Examples related to sql-server

Examples related to triggers