顶下自己写的触发器(主要是为了备份)

/*---------------------------------------------------
write  by:   JiaFengWang
Function:    数据同步更新
Create date: 2007-7-12
Change date: 2007-7-12
---------------------------------------------------*/

create trigger InsertIntoRelation on Autoinfo for update
as
   declare @old_bgyear smallint
   declare @old_edyear smallint
   declare @new_bgyear smallint
   declare @new_edyear smallint
   declare @loop       int
   declare @AC_id      int
   declare @A_id       int
   declare @A_oprice   int
   declare @cityid     sysname
   -- Get old Year
   select @old_bgyear = Deleted.A_bgyear,
               @old_edyear = Deleted.A_edyear
   from   Deleted
   ---Get New Year
   select @new_bgyear =  Inserted.A_bgyear,
              @new_edyear =  Inserted.A_edyear,
              @A_id       =  Inserted.A_id,
              @AC_id      =  Inserted.AC_id,
              @A_oprice   =  Inserted.A_oprice
   from   Inserted
if(update(A_oprice))
begin
    update RelationShip
    set rs_Price = @A_oprice
    where A_id = @A_id and AC_id = @AC_id
end
if (update(A_bgyear) or update(A_edyear))
begin
 set @loop = @old_bgyear
 while (@loop < @old_edyear)
 begin
  -- 如果老年份不在新年份范围之内
  if(@loop < @new_bgyear or @loop > @new_edyear)
  begin
      delete RelationShip
      where rs_year = @old_bgyear and A_id=@A_id and AC_id=@AC_id
  end
  set @loop = @loop + 1
 end
 set @loop = @new_bgyear
 while (@loop < @new_edyear)
 begin
 -- 如果新年份不在老年份范围之内
  if(@loop > @old_edyear or @loop < @old_bgyear)
  
  begin
   declare my_cursor cursor scroll dynamic
   for select di_city from Divide for READ ONLY
    open my_cursor
     fetch next from my_cursor into @cityid
     while(@@fetch_status = 0)
     begin
      -- insert Values
      insert RelationShip (rs_year, A_id,AC_id,rs_Active,city,rs_Price) values(@loop,@A_id,@AC_id,1,@cityid,@A_oprice)
      fetch next from my_cursor into @cityid
     end
    close my_cursor
    deallocate my_cursor
  end
  set @loop = @loop + 1
 end
end

/*---------------------------------------------------
write  by:   JiaFengWang
Function:    数据同步添加
Create date: 2007-7-12
Change date: 2007-7-12
---------------------------------------------------*/
create trigger InsertAutoInforAndRelation on Autoinfo for insert
as
   declare @bgyear     smallint
   declare @edyear     smallint
   declare @AC_id      int
   declare @A_id       int
   declare @A_oprice   int
   declare @cityid     sysname
   select  @bgyear     =  Inserted.A_bgyear,
                @edyear     =  Inserted.A_edyear,
                @A_id       =  Inserted.A_id,
                @AC_id      =  Inserted.AC_id,
                @A_oprice   =  Inserted.A_oprice
   from    Inserted
   while (@bgyear < @edyear)
   begin
   declare my_cursor cursor scroll dynamic
   for select di_city from Divide for READ ONLY
    open my_cursor
     fetch next from my_cursor into @cityid
     while(@@fetch_status = 0)
     begin
      -- insert Values
      insert RelationShip (rs_year, A_id,AC_id,rs_Active,city,rs_Price) values(@bgyear,@A_id,@AC_id,1,@cityid,@A_oprice)
      fetch next from my_cursor into @cityid
     end
    close my_cursor
    deallocate my_cursor
    set @bgyear = @bgyear + 1
    end