none
Loop each Column In Table For Using IF UPDATE () in MS SQL Trigger.

    Question

  • Hi, All Expert ...

    I Want To Loop and Check All Column in A Table For Value Changes Using IF UPDATE () Function. But The Problem is this Function show a Error Message as "Incorrect syntax near '@StringColumn'." The Below is the Code :"

    DECLARE @StringColumn nvarchar (50)
    DECLARE @Counter int
    DECLARE @ColumnCount int

    SELECT @ColumnCount =(SELECT COUNT(COLUMN_NAME)
                                             FROM INFORMATION_SCHEMA.COLUMNS
                                             WHERE TABLE_NAME = 'Customer')

    WHILE @Counter < @ColumnCount
    BEGIN 
          Set @Counter = @Counter + 1
          SELECT @StringColumn = (SELECT COL_NAME(OBJECT_ID(@TableName), @Counter) AS 'Column_Name')

    If Update(@StringColumn)     

         -- Do Some Function Here

    END

    Thanks in advanced ...

    Monday, May 07, 2012 10:04 AM

Answers

  • Hi Sheng2000,

    UPDATE() function may not a reliable way to determine whether the value changed for a column, since it will always return true if the column is included in the SET section in UPDATE operation, even if the value is the same as original. So you may consider to comparing the new and old values based on INSERTED and DELETED tables within the trigger.

    Based on my test, it seems using UPDATE() function with variables as parameter is not supported. In this case, you may need to specify each column one by one.

    Here is an example:

    --/* 
    IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL    
    DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
    GO 
     --*/  
    CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample   
    AFTER INSERT, UPDATE  
    AS 
    BEGIN --Trigger    
    	IF UPDATE(SampleName)       
    	BEGIN       
    		UPDATE tblSample SET       
    		SampleNameLastChangedDateTime = CURRENT_TIMESTAMP       
    		WHERE         
    		SampleID IN (SELECT Inserted.SampleID                 
    		FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID                
    		WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))   --If the value changed, do the updae 
    	END  
    END --Trigger 


    For more information, please pay attention to the second reply on the following thread elaborated on this usage:
    Update function in TSQL trigger.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:32 AM
    Tuesday, May 08, 2012 5:26 AM

All replies

  • Hello,

    The solution provided, assuming I understand the issue may not scale very well for production loads. Can you give us some sample input and output so we can help you?

    -Sean


    Sean Gallardy, MCC | Blog

    Monday, May 07, 2012 11:48 PM
  • Hi, Sean.

    Thanks for replying, I just want to check Each Column In selected Table Is that Value/Figure Change or not

    In Update MS SQL Trigger, and apply the Selected Column into a nvarchar(50) Variable (@StringColumn), then

    Check the function IF UPDATE(@StrringColumn) Then do some insert operation.

    Tuesday, May 08, 2012 2:27 AM
  • Hi Sheng2000,

    UPDATE() function may not a reliable way to determine whether the value changed for a column, since it will always return true if the column is included in the SET section in UPDATE operation, even if the value is the same as original. So you may consider to comparing the new and old values based on INSERTED and DELETED tables within the trigger.

    Based on my test, it seems using UPDATE() function with variables as parameter is not supported. In this case, you may need to specify each column one by one.

    Here is an example:

    --/* 
    IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL    
    DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
    GO 
     --*/  
    CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample   
    AFTER INSERT, UPDATE  
    AS 
    BEGIN --Trigger    
    	IF UPDATE(SampleName)       
    	BEGIN       
    		UPDATE tblSample SET       
    		SampleNameLastChangedDateTime = CURRENT_TIMESTAMP       
    		WHERE         
    		SampleID IN (SELECT Inserted.SampleID                 
    		FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID                
    		WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))   --If the value changed, do the updae 
    	END  
    END --Trigger 


    For more information, please pay attention to the second reply on the following thread elaborated on this usage:
    Update function in TSQL trigger.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:32 AM
    Tuesday, May 08, 2012 5:26 AM
  • Stephanie Lv, Thanks For Reply .

    Now I met another "Fresh" Problem ...

    The Below is sample code for Getting the Inserted Data and keep in @CompareID1

    The Original Need Is the variable @CompareID1 after Trigger Will Give Me the Value/Figure

    from Fields @StringColumn, But Now it give me the Fields's Name not the value from that fields.

    How I have to code it ?

    SELECT @StringColumn = (SELECT COL_NAME(OBJECT_ID(@TableName), @Counter) AS 'Column_Name')
    SELECT @CompareID1 = @StringColumn From Inserted

    Thanks In adavanced ...

    Tuesday, May 08, 2012 5:47 AM
  • Sheng2000,

    Sorry, I am not clear about your question. I would recommend you open a new thread with more detail elaboration on your requirement for better assistance.


    Stephanie Lv

    TechNet Community Support

    Thursday, May 10, 2012 4:58 AM