Using Trigger in SQL Server 2005

• hiYusuf

  SSC Enthusiast

  Points: 151

  More actions

  August 13, 2011 at 4:11 am

  #255506

  Hi all,

  I am using trigger in TABLE A for update TABLE B with below code

  CREATE TRIGGER [dbo].[UpdtMasterFG]

  ON [dbo].[RateLog]

  FOR Update

  As

  UPDATE FinishGoods SET

  dbo.FinishGoods.CurrentQty1 =(SELECT SUM(dbo.RateLog.Qty1) FROM dbo.RateLog WHERE

  dbo.RateLog.StyleNo = dbo.FinishGoods.StyleNo AND dbo.RateLog.ColorID = dbo.FinishGoods.ColorID AND dbo.RateLog.SizeID = dbo.FinishGoods.fldSize)

  Its working fine but problem is , it is updating all record (7000 record) of table "FinishGoods". i want to update only those record which i update in "RateLog" Table.

  i heard about inserted table but don't know how to use it.

  Any help would be appreciable.

  Thanks & regards,

  Mohammad Yusuf

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 13, 2011 at 8:12 am

  #1368327

  The inserted table has the same structure as the table the trigger is on, but only has the rows have been changed. Hence you would probably use it in place of RateLog in the trigger.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 13, 2011 at 8:41 am

  #1368330

  Ya those exemples are harder to fin in the documentation (not in create trigger, but burried deep in a link).

  http://msdn.microsoft.com/en-us/library/ms190752(v=SQL.90).aspx