update the date field in a table after each update

• sreeya

  SSC Eights!

  Points: 979

  More actions

  September 2, 2011 at 11:09 am

  #244254

  I need some help on this:

  I have a table and one of the columns has a datefield that has a default value getdate(), when evr there is a update to the table that field has to be changed to current datetime. Is it possible with the after update trigger?

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  September 2, 2011 at 11:40 am

  #1376602

  sreeya (9/2/2011)

  ---

  I need some help on this:

  I have a table and one of the columns has a datefield that has a default value getdate(), when evr there is a update to the table that field has to be changed to current datetime. Is it possible with the after update trigger?

  Yes.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 2, 2011 at 11:44 am

  #1376603

  to follow up on what Pablo said, here's a simple example:

  CREATE TABLE WHATEVER(

  WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

  DESCRIP VARCHAR(30)

  )

  INSERT INTO WHATEVER(DESCRIP)

  SELECT 'APPLES' UNION

  SELECT 'ORANGES' UNION

  SELECT 'BANANAS' UNION

  SELECT 'GRAPES' UNION

  SELECT 'CHERRIES' UNION

  SELECT 'KIWI'

  ALTER TABLE WHATEVER ADD INSERTDT DATETIME DEFAULT GETDATE()WITH VALUES,

  UPDATEDDT DATETIME DEFAULT GETDATE() WITH VALUES

  SELECT * FROM WHATEVER

  CREATE TRIGGER TR_WHATEVER

  ON WHATEVER

  FOR INSERT,UPDATE

  AS

  UPDATE WHATEVER

  SET UPDATEDDT = GETDATE()

  FROM INSERTED

  WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID

  INSERT INTO WHATEVER(DESCRIP)

  SELECT 'CANTALOUPE' UNION

  SELECT 'TANGARINES' UNION

  SELECT 'PLUMS' UNION

  SELECT 'PEACHES' UNION

  SELECT 'NECTARINES'

  SELECT * FROM WHATEVER

  UPDATE WHATEVER SET DESCRIP = DESCRIP + ' ' WHERE WHATEVERID IN (4,5)

  SELECT * FROM WHATEVER

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  September 2, 2011 at 11:47 am

  #1376604

  Lowell (9/2/2011)

  ---

  to follow up on what Pablo said, here's a simple example...

  hey hey hey! I answered exactly what poster asked - you are volunteering too much information 😀

  Just kidding 😉

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• sreeya

  SSC Eights!

  Points: 979

  More actions

  September 2, 2011 at 1:59 pm

  #1376676

  Thanks.