Update Trigger with Case statement

• Reyvon

  SSC-Addicted

  Points: 466

  More actions

  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.

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  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]

• Reyvon

  SSC-Addicted

  Points: 466

  More actions

  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