creating update trigger

• Sab-399780

  Newbie

  Points: 3

  More actions

  June 8, 2007 at 4:36 am

  #172660

  Hi,

   

  I am new to triggers and have been confused  by reading various articles on how to create an update trigger.Im looking for help with creating an update trigger on a table. I need to put a trigger on a table (called table A) which has a date column. If that date is then updated I need to update another table, table B so that it indicates that the date has changed for a particular file, this done by changing the flag to 1 if there is a change and 0 if no change. Both tables A and B have a common column called code.

   

  Table A

  Code	Date
  111111	01/01/2007
  222222	02/02/2007

   

  Table B

  Code	Flag
  111111	0
  222222	1

   

  Thanks you very much

  Rookie

• Jeff Moden

  SSC Guru

  Points: 1004601

  More actions

  June 10, 2007 at 9:21 pm

  #711895

  Sab,

  Would you mind showing us the code you've tried so far, please...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sab-399780

  Newbie

  Points: 3

  More actions

  June 11, 2007 at 2:46 am

  #711926

  Hi Jeff,

  Many thanks for taking the time to reply, below is the code I tried. After reading a few books/web sites its the best I could come up with and as I am totally new to triggers I feel that I maybe way off.

  CREATE TRIGGER Flag_update

  on table_A

  for update

  as

  if Update (Table_A.[Date])

   Begin

    update table_B

    Set Flag = 1

    From table_A join inserted i on table_A.Code = inserted.code

  end

  Thanks again

   

  Rookie

• Jeff Moden

  SSC Guru

  Points: 1004601

  More actions

  June 11, 2007 at 7:24 am

  #711985

  Already posted this on the PM you sent me... thought I'd share the solution I think you are looking for...

  CREATE TRIGGER Flag_update

  on table_A

  for update

  as

  if Update (Table_A.[Date])

   Begin

    update table_B

    Set Flag = 1

    From table_B join inserted i on table_B.Code = inserted.code

  end

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sab-399780

  Newbie

  Points: 3

  More actions

  June 11, 2007 at 7:26 am

  #711988

  Thanks Again Jeff

  Rookie