Help with update trigger

• jordon.shaw

  Hall of Fame

  Points: 3571

  More actions

  March 10, 2010 at 3:12 pm

  #221423

  Ok, this is probably easier than I'm making it out to be, but is there a way to write a trigger on an update of one field. What I mean is, if I write a trigger on update, that means anytime that table is updated, the trigger will fire. I need my trigger to only run when a certain field is updated. What I'm tring to do:

  If APINSP.COMPDTTM is updated, at that value it's updated to, add 180 days to it and then update APBLDG.EXPTDTTM to the new value; however, if that record is updated and APINSP.COMPDTTM doesn't get updated, than don't update APBLDG.EXPTDTTM.

  Thanks,

  Jordon

• Nik Desai

  SSC Enthusiast

  Points: 135

  More actions

  March 10, 2010 at 6:32 pm

  #1131738

  You can use the internal tables INSERTED and the DELETED and find out what to do.

  Thanks,

  Nikul

• Paul White

  SSC Guru

  Points: 150467

  More actions

  March 10, 2010 at 8:15 pm

  #1131755

  Would a computed column work better?

  CREATE TABLE tempdb.dbo.ApInsp (CompDttm DATETIME NOT NULL, ExpDttm AS DATEADD(DAY, 180, CompDttm));

  INSERT tempdb.dbo.ApInsp (CompDttm) VALUES ('20090314');

  SELECT CompDttm, ExpDttm FROM tempdb.dbo.ApInsp;

  DROP TABLE tempdb.dbo.ApInsp;

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi