Tircky if within a trigger

• timscronin

  SSCertifiable

  Points: 6851

  More actions

  January 7, 2010 at 2:42 pm

  #136351

  I have tableA a which has an ID field, and a status field of either 'active' or inactive'

  I have a related table, tableb which has the id field to match the record of the row in tableA, and it has a status flag of 'y' or 'n'. I need a trigger (has to work on mass updates so I can't use variables for my compare) If the status is changed from Active to inactive in tablea I need the corresponding record in tableb to have the flag changed to 'n'. I tried below, but not working. I was trying to do a compare using inserted versus deleted

  alter trigger tim1 on tablea for update as

  select inserted.id from inserted inner join deleted on

  inserted.id = deleted.id where

  inserted.status = 'inactive' and deleted.status = 'active'

  if @@rowcount =1

  begin

  update tableb

  set status = 'N' from inserted

  where tableb.id = inserted.id

  end

• José.Cruz

  Right there with Babe

  Points: 722

  More actions

  January 7, 2010 at 2:59 pm

  #1100533

  Hi,

  I think that a trigger like this will do the job

  ALTER TRIGGER tim1 On tablea FOR UPDATE

  As

  BEGIN

  UPDATE tableb SET status = 'N'

  FROM tableb, deleted, inserted

  WHERE tableb.id = inserted.id

  AND deleted.id = inserted.id

  AND inserted.status = 'inactive'

  AND deleted.status = 'active'

  END[/code]

  José Cruz