Post reply

Update Trigger with Case statement

  • February 21, 2012 at 4:23 am

    #250953

    Hi All,

    I was wondering if anyone could help, I'm trying to update a column in a table so if the price has changed in price column i would like it to update another column in the same table with 1 or 0

    basically If the price < price then 1 else 0

    so it will only be a 1 if the price has been lowered

    I've tried

    For Update

    as

    UPDATE <table> t1

    SET pricechange = case when t1.price < i.price then 1

    ELSE 0

    END

    FROM pricetable t1

    INNER JOIN Inserted I ON I.ThisID = T1.ThisID

    am i missing something? it does update but only to 1 value and doesn't update if the price is higher or lower it just comes out as 1.

  • February 21, 2012 at 4:36 am

    #1449595

    You need to compare new value with previous one, which can be found in DELETED:

    UPDATE t1

    SET pricechange = case when D.price < i.price then 1 else 0 end

    FROM pricetable t1

    INNER JOIN Inserted I ON I.ThisID = T1.ThisID

    INNER JOIN Deleted D ON D.ThisID = I.ThisID

    BTW, why not to implement it as computed column...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • February 21, 2012 at 4:44 am

    #1449599

    That's worked great, didn't think of using computed column but i shall have a play around and see how that goes.

    The above worked a treat, thanks for the quick reply 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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