Post reply

UPDATE question

  • November 24, 2009 at 8:44 am

    #218039

    Hi,

    Is there any way to determine what column's values were changed on an update trigger?

    We have an UI that always sends the values even if they didn't change and I'd like to know if there's a simple way of knowing what were the columns whose values changed.

    Example:

    UPDATE table SET col1 = @1, col2 = @2, ... WHERE id = @id

    Only col2 changed the value, is there any way of knowing it without having to compare each column of the inserted and deleted object on the trigger?

    or without selecting the values before the update and compare them...

    Thanks,

    Pedro


    If you need to work better, try working less...

  • November 24, 2009 at 3:24 pm

    #1083710

    Hi! Have you tried to use Change Data Capture?

  • November 24, 2009 at 3:56 pm

    #1083724

    Thats a very heavy feature considering what I want to do.

    It's easier and faster to use the comparison between inserted and deleted...

    I was wondering if anyone has used the IF UPDATE (column) or COLUMNS_UPDATED()?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • November 25, 2009 at 8:27 am

    #1084088

    IF UPDATE fires if a value is sent for the column even if is has not changed. Try this:

    IF OBJECT_ID('dbo.temp') IS NOT NULL

    BEGIN

    DROP TABLE temp

    END

    CREATE TABLE temp

    (

    col1 INT

    );

    INSERT INTO temp (

    col1

    ) VALUES (

    0 );

    GO

    CREATE TRIGGER upd_test ON temp

    AFTER UPDATE

    AS

    SET NOCOUNT ON;

    IF UPDATE(col1)

    BEGIN

    Select 'Col1 updated';

    END

    GO

    UPDATE temp

    SET col1 = col1

    It appears columns_updated() works the same way.

    I think you have to do a compare.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 25, 2009 at 9:04 am

    #1084132

    Thanks for the help but the problem with that is if I make

    UPDATE table SET col1 = col1, col2 = col2, col3 = 'xxxx' WHERE id = 1

    the UPDATE (column) says that col1 was updated cause it's on the UPDATE clause, even if it has the same value...

    Probably I have to do:

    alter trigger upd_t1 on t1 after update

    as

    declare @c1_i int, @c1_d int, @c2_i int, @c2_d int

    select @c1_i = col1, @c2_i = col2 from inserted

    select @c1_d = col1, @c2_d = col2 from deleted

    if @c1_i <> @c1_d

    --do something

    if @c2_i <> @c2_d

    --do something

    for each column.. I was wondering if there was any easier way...

    Pedro


    If you need to work better, try working less...

  • November 25, 2009 at 9:08 am

    #1084138

    Right. That was the point I was making. There is no easier way, at least not that I know of.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 25, 2009 at 9:15 am

    #1084145

    Thanks...

    I was hopping if the UPDATE was "smart" enough to know if a column value was changed...

    But if that was true the update would be lot "heavier"...

    Just another question... I never really used triggers... Are they sync or assync?

    Pedro


    If you need to work better, try working less...

  • November 25, 2009 at 9:25 am

    #1084155

    Triggers are synchronous and are part of the transaction so if the trigger fails the transaction rolls back. Here's an introductory article I wrote about triggers[/url].

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 25, 2009 at 9:31 am

    #1084162

    Thanks..

    I'll take a look at it.

    Thanks once again,

    Pedro


    If you need to work better, try working less...

  • November 25, 2009 at 12:02 pm

    #1084259

    PiMané (11/25/2009)

    Thanks for the help but the problem with that is if I make

    UPDATE table SET col1 = col1, col2 = col2, col3 = 'xxxx' WHERE id = 1

    the UPDATE (column) says that col1 was updated cause it's on the UPDATE clause, even if it has the same value...

    Probably I have to do:

    alter trigger upd_t1 on t1 after update

    as

    declare @c1_i int, @c1_d int, @c2_i int, @c2_d int

    select @c1_i = col1, @c2_i = col2 from inserted

    select @c1_d = col1, @c2_d = col2 from deleted

    if @c1_i <> @c1_d

    --do something

    if @c2_i <> @c2_d

    --do something

    for each column.. I was wondering if there was any easier way...

    Pedro

    I have to tell you that writing a trigger like that is a form of "Death-by-SQL" because it will only correctly process the first row of the "batch" no matter how may rows are in the batch. This isn't a casual warning... a trigger like that could end your career as a developer because of the damage it can do simply by not handling all of the rows correctly.

    Tell us what the "do something"s in the code above does and let us show you how to write this trigger correctly so you don't have to learn a new trade. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 25, 2009 at 12:18 pm

    #1084268

    Jeff Moden (11/25/2009)

    I have to tell you that writing a trigger like that is a form of "Death-by-SQL" because it will only correctly process the first row of the "batch" no matter how may rows are in the batch. This isn't a casual warning... a trigger like that could end your career as a developer because of the damage it can do simply by not handling all of the rows correctly.

    Tell us what the "do something"s in the code above does and let us show you how to write this trigger correctly so you don't have to learn a new trade. 😀

    That part is covered in the article I linked to. Got tired of writing the same thing over and over. Now I just point to the article.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 25, 2009 at 9:47 pm

    #1084435

    Jack Corbett (11/25/2009)

    Jeff Moden (11/25/2009)

    I have to tell you that writing a trigger like that is a form of "Death-by-SQL" because it will only correctly process the first row of the "batch" no matter how may rows are in the batch. This isn't a casual warning... a trigger like that could end your career as a developer because of the damage it can do simply by not handling all of the rows correctly.

    Tell us what the "do something"s in the code above does and let us show you how to write this trigger correctly so you don't have to learn a new trade. 😀

    That part is covered in the article I linked to. Got tired of writing the same thing over and over. Now I just point to the article.

    Damn... how did I miss that? I must have been on vacation then. Thanks Jack... haven't read the article yet but, knowing the author, I'm sure it'll be great. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 26, 2009 at 12:56 am

    #1084468

    my purpose is to log on a table the changes made to other tables but only the values that "really" changed and not the values on the update clause...

    I could also use XML to get the deleted row and inserted XML and use a CLR to compare the XML using XMLDiffPatch or something like that but I think that would be slower and heavier.

    This is not something to apply to all tables. On our app we have data tables (to store app data like invoices, customers, ..) and customization tables (to store data fields information, form layouts, ...) that don't change that much but need to track the changes the users made.

    Something easier would be just to have a "replica" of the original table with all the deleted row information and let the app do the job of comparing :D...

    Probably this last one will be the chosen...

    Thanks,

    Pedro

    PS: Our app doesn't do batch updates... Just row by row, with id clearly identified. It sends multiples rows, with users table data type, but updates one by one since each one has it's id.


    If you need to work better, try working less...

  • November 26, 2009 at 6:34 am

    #1084577

    PiMané (11/26/2009)

    PS: Our app doesn't do batch updates... Just row by row, with id clearly identified. It sends multiples rows, with users table data type, but updates one by one since each one has it's id.

    Ahhh, but there is always someone who has access to the database outside the application and it doesn't hurt anything to code the trigger to properly handle set-based updates, but it DOES hurt if there is even 1 time a set-based update occurs and the trigger doesn't handle to properly.;-)

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 26, 2009 at 11:17 am

    #1084672

    I agree with Jack. Having RBAR triggers will eventually cause you a huge problem.

    As a casual observation, all the XML and CLR stuff you're talking about will just make for a big, slow, complicated mess... much worse than doing the column to column compares which you can actually write a little script to build for you from one of the systems tables (sys.sysColumns I believe... not sure because this is 2k8).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

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