Sql SERVER 触发器的使用

Sql SERVER 触发器的使用

 

 

有A、B两表。B表中记录取自A表  
  A：  
  ID(PK),     Name,     Sex,     Job,     Address,   ….  
  B：  
  ID(PK),     Name,     Address  
   
  现想在A上建立触发器，当A中记录发生增加、某个字段（Address）发生更新、记录删除时，对B中的记录进行相应的增加、更新和删除的工作。

 

我们既可以写成三个触发器，又可以写成一个触发器。但是 写成一个触发器要判断当前操作是什么类型,所以在效率上没有三个好。

 

 

1、 三个的触发器

 

 --新增同步  
  create   trigger   tr_insert_A   on   A  
  for   insert  
  as  
  insert   B(ID,Name,Address)   select   ID,Name,Address   from   inserted  
  go  
   
  --删除同步  
  create   trigger   tr_delete_A   on   A  
  for   delete  
  as  
  delete   B   from   deleted   d   where   B.ID=d.ID  
  go  
   
  --更新同步  
  create   trigger   tr_update_A   on   A  
  for   update  
  as  
  if   update(Name)   or   update(Address)  
  update   B   set   Name=i.Name,Address=i.Address  
  from   B,inserted   i,deleted   d  
  where   i.ID=d.ID   and   i.ID=b.ID  

 

 

  2、写成一个触发器  
    
  create   trigger   tr_insert_update_delete_A   on   A  
  for   insert,update,delete  
  as  
  if   not   exists(select   *   from   deleted)   --如果是新增

  
   insert   B(ID,Name,Address)   select   ID,Name,Address   from   inserted  

 

  else   if   not   exists(select   *   from   inserted)   --如果是删除

  
  delete   B   from   deleted   d   where   B.ID=d.ID     

  else   if   update(Name)   or   update(Address)     --如果是更新  

 
  update   B   set   Name=i.Name,Address=i.Address  
  from   B,inserted   i,deleted   d  
  where   i.ID=d.ID   and   i.ID=b.ID   

 

 

注意：

 

1.插入操作（Insert）

　　Inserted表有数据，Deleted表无数据

2.删除操作（Delete）

　　Inserted表无数据，Deleted表有数据

3.更新操作（Update）

　　Inserted表有数据（新数据），Deleted表有数据（旧数据）

 

另外：

如果是这样的两个表，又应该怎样做呢？主要A的关键字段变了  
  有A、B两表。B表中记录取自A表  
  A：  
  Name(PK),     Sex,     Job,     Address,   ….  
  B：  
  ID(PK),     Name,     Address  

那么 2 可以改成

  create   trigger   tr_insert_update_delete_A   on   A  
  for   insert,update,delete  
  as  
  if   not   exists(select   *   from   deleted)   --如果是新增  

 
  insert   B(Name,Address)   select   Name,Address   from   inserted  

  else   if   not   exists(select   *   from   inserted)   --如果是删除  

  delete   B   from   deleted   d   where   B.Name=d.Name  

 
  else   if   update(Name)   or   update(Address)     --如果是更新  

  update   B   set   Name=i.Name,Address=i.Address  
  from   B,inserted   i,deleted   d  
  where   i.Name=d.Name   and   i.Name=b.Name  

 

以上 触发器Insert和Delete都没有问题，但是在Update情况下会有如下问题，就是 A表的关键字段Name更新时，B表的Name字段无法更新！！

 

改成这样：

 

create   trigger   tr_insert_update_delete_A   on   A  
  for   insert,update,delete  
  as  

  declare   @r   int  
  set   @r=@@rowcount  
  if   @r=0   return  

  if   not   exists(select   *   from   deleted)   --如果是新增

  
  insert   B(Name,Address)   select   Name,Address   from   inserted  

  else   if   not   exists(select   *   from   inserted)   --如果是删除

  
  delete   B   from   deleted   d   where   B.Name=d.Name  

 
  else   if   update(Name)   and   @r>1   --如果更新了Name,则不允许多行,否则不能实现同步

  
  begin  
  raiserror('不允许同时更新多条记录的主键值,更新被取消!',1,16)  
  rollback   tran  
  return  
  end  
  else   if   update(Name)  
  update   B   set   Name=i.Name,Address=i.Address  
  from   B,inserted   i,deleted   d  
  where   B.Name=D.Name  
  else   if   update(Address)  
  update   B   set   Name=i.Name,Address=i.Address  
  from   B,inserted   i,deleted   d  
  where   B.Name=D.Name   and   i.Name=d.Name

 

 

需要注意的是，如果触发器的代码太复杂，或者效率不高，因为触发器的动作往往是更新数据，会产生一定的排它锁，对于触发器来说效率是必须时刻注意的！

而且，对于批量 操作带有触发器的表的时候，速度上可能会变得很慢。 但是对于单条数据来说，影响是很小的。因为这些都是在内存中执行的。