when one column is updated, want to update another column in same table using trigger.

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  September 24, 2008 at 11:18 pm

  #198719

  Dear All,

  I have a table in which there are fields like Quantity, Price, Total etc.There's is also a primary key field in this table.

  We have an interface where user can change the quantity (only quantity).

  I want to write a trigger on this table which calculates Total (qty * price) whenever qantity is updated.

  How can I do it..?. How will I know for which row the quantity is updated..?

  Pleas help...

  Thanks in advance..

  Santhosh Nair.

• SwePeso

  SSC-Dedicated

  Points: 39758

  More actions

  September 25, 2008 at 12:41 am

  #876801

  Something similar to this?

  CREATE TRIGGER dbo.trgMyTotalUpdate ON dbo.MyTable

  AFTER UPDATE

  AS

  IF UPDATE(Quantity)

  UPDATEmt

  SETmt.Total = i.Quantity * i.Price

  FROMdbo.MyTable AS mt

  INNER JOINinserted AS i ON i.PkCol = mt.PkCol

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  September 25, 2008 at 1:24 am

  #876810

  Thanks a lot...It's working...

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  September 25, 2008 at 10:31 am

  #877158

  how about a Computed Column that "computes" i.Quantity * i.Price

  http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/%5B/url%5D

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005