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

 

 

需要注意的是，如果觸發器的代碼太複雜，或者效率不高，因為觸發器的動作往往是更新資料，會產生一定的排它鎖，對於觸發器來說效率是必須時刻注意的！

而且，對於批量 操作帶有觸發器的表的時候，速度上可能會變得很慢。 但是對於單條資料來說，影響是很小的。因為這些都是在記憶體中執行的。