Help trigger

• Jeff-132889

  Ten Centuries

  Points: 1112

  More actions

  July 15, 2005 at 1:41 pm

  #67738

  I am writing a trigger with the following goal:

  When a bit field (a.bit) in table A is updated from a 1 to a 0, the trigger fires and identifies that row (a.id) and updates the child table (b.id) column (b.bit).

  The following is what I have so far:

   

  declare @chapter_id varchar(100), @sql varchar(1000), @DBNAME VARCHAR(200), @staffid INT

  select @Chapter_id = substring(chapterid,0,4) from tblstaff where staffid = 8203

     

  begin

   Select @dbname = 'Wishdata_' +@chapter_id+ '.dbo.tbluser'

   Select @sql = 'update '+@DBNAME+

     ' set ' +@dbname+ '.staffpermission = null

     from '  inner join mawdata.dbo.staffid on '+@dbname+ '.staffid = mawdata.dbo.staffid

     where mawdata.dbo.staffid = 8203'

   

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  July 15, 2005 at 2:04 pm

  #575037

   

  Create trigger on YourParentTable for Update

  as

  if @@rowcount = 0 return

  if update(YourParentBitColumn)

   begin

    Update C set ChildTableBitColumn =  i.YourParentBitColumn

     YourChildTable  C

     join

     inserted i

     on c.pkey = i.pkey 

     where

   (i.YourParentBitColumn is not null and  c.ChildTableBitColumn  is null)

    or

           (i.YourParentBitColumn is null and  c.ChildTableBitColumn is not null )

    or

    (i.YourParentBitColumn <> c.ChildTableBitColumn  )

   end

  
  * Noel