Post reply

Trigger - Display Updated Fileds

  • April 27, 2016 at 9:29 am

    #326046

    Is there a way with a trigger to display only the fields that were updated? My issue is it could be one or 5 of 30 fields in the table.

    Thanks

    Baze

  • April 27, 2016 at 9:37 am

    #1874987

    Join the Inserted and Deleted virtual tables, and then you can do a comparison between the old and new values.

    John

  • April 27, 2016 at 9:45 am

    #1874988

    a trigger has a COLUMNS_UPDATED integer value which represents a bitmask for the columns that were mentioned in the update, but it does not tell you if the value changed ie

    ie

    UPDATE myTable SET Value = 1 WHERE Value = 1

    the above would have a COLUMNS_UPDATED mask showing the column [Value ] was changed, but it didn't change, really

    it was the same value before and after;

    the way to do check old vs new would be an explicit test in the trigger itself, joining INSERTED and DELETED columns to compare old vs new values.

    what is it you are trying to check?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 27, 2016 at 9:48 am

    #1874991

    I am just trying to return all change values after an update

  • April 27, 2016 at 9:51 am

    #1874992

    It's going to be a bit complex. Perhaps you can show some DDL and what you'd like?

    If I have 3 fields, and 2 changes, do you want 2 fields returned or 3, or maybe you want the old and new values? do you want two rows, one for old and one for new?

    I know this seems simple, but depending on what you want to return, this could get complex.

    If I join inserted and deleted, I can tell what's different, but trying to check each field against others can be complex, and time consuming, not to mention resource intensive.

    Think about this carefully and mock up some results back.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply