Update Trigger that ignores NULL value updates in the source cloumn

• Cowboy DBA

  SSCrazy

  Points: 2135

  More actions

  July 19, 2005 at 12:06 pm

  #90121

  I have two tables: Table1 and Table2

  What I want to do is create a trigger that, when col1 on Table1 is updated it automatically updates col1 on Table2. However should col1 on Table1 be updated to NULL I wish is to be ignored i.e. any NULL value updates on TABLE1.COL1 should be ignored.

  I thought the following would work but it doesn't

  CREATE TRIGGER [UPDATE] ON [dbo].[TABLE1]

  FOR INSERT, UPDATE, DELETE

  AS

  UPDATE Table2

  SET[COL1] =

  (SELECT [COL1]

  FROM table1

  WHERE table1.[1] IS NOT NULL

  AND table1.ID =table2.id)

  Any ideas where I am going wrong?

• noeld

  SSC Guru

  Points: 96590

  More actions

  July 19, 2005 at 12:12 pm

  #575611

  UPDATE  t2

             SET[COL1] = i.[1]

  From Table2 t2 join  inserted i

       on  i.ID = t2.id

  where i.[1] IS NOT NULL

   

  
  * Noel