触发器代码(更新表A数据自动更新表B字段列)

 

表A

=================

id             字段名

1               ft1

2               ft2

 

 

表B

=================

/id        ft1        ft2   ...

 

 

Create Trigger Tri_test On t1
After insert ,update, delete
As
Begin
   Declare @new varchar(10),@old varchar(10),@s varchar(100)
   Select @new=field From inserted
   Select @old=field From deleted
   --新增情况
   IF Not Exists(Select 1 From syscolumns Where id=object_id(t2) and name=@new)
   Begin
      Set @s='Alter table t2 add '+@new+' varchar(10)'
      exec(@s)
   End
   --更新情况
   IF EXISTS(Select 1 From syscolumns Where id=object_id(t2) and name=@old)
   Begin
      Set @s='Alter table t2 alter column '+@new+' varchar(10)'
      Exec(@s)
   End
   --删除
   IF EXISTS(Select 1 From syscolumns Where id=object_id(t2) and name=@old)
   Begin
      Set @s='Alter table t2 drop column '+@old
      Exec(@s)
   End
End