开发记录——触发器实现 数据库关联字段

数据库关联字段
 当字段user_autoid修改的时候,我希望对象的另一个表的字段zs_schoolinfo也跟着修改

使用触发器code,当两个表的字段内容完全相同时,使用sql外键也可以实现

Create Trigger schoolID
on zs_useraccount
for INSERT,UPDATE,DELETE
NOT FOR REPLICATION
as
set nocount on
if(select count(*) from DELETED)>0
begin
   delete  zs_schoolinfo where schoolid in(select user_autoid from deleted)
end
if(select count(*) from inserted)>0
begin
  insert zs_schoolinfo(schoolid) select schoolid=user_autoid from inserted
end

同时更新数据库多个字段

Create Trigger schoolID_celtype1
on zs_useraccount
for INSERT,UPDATE,DELETE
NOT FOR REPLICATION
as
set nocount on
if(select count(*) from DELETED)>0
begin
   delete  zs_schoolinfo where schoolid in(select user_autoid from deleted)
end
if(select count(*) from inserted)>0
begin
  insert zs_schoolinfo(schoolid,schooltypeid,schoolname)
  select user_autoid,celtype,name  from inserted
 
end
 

posted on 2009-04-15 14:39  9who  阅读(444)  评论(1编辑  收藏  举报

导航