SqlServer的觸發器

標籤：

觸發器是Sqlserver響應以下任意語句而自動執行的一天T-SQL語句。delete,insert,update.表和視圖支援觸發器。（但是暫存資料表不支援）。

我們在哪些業務情境需要用到觸發器呢？

1.每當訂購一個產品時，都從庫存數量中減去訂購的數量。

2.每當增加一個顧客到某個資料庫表時，都檢查其電話號碼格式是否正確，州的縮寫是否為大寫。

1.首先，建立觸發器。觸發器用create trigger語句建立。下面是一個簡單的例子:

create trigger newproduct_trigger on productsafter insertas select ‘product added‘

觸發器定義為after insert,所以此觸發器將在insert語句成功執行後執行。觸發器按每個表每個事件每次地定義，因此每個表最多支援3個觸發器（insert,update,delete各一個觸發器）

2.刪除觸發器。我們用drop trigger語句刪除一個觸發器。如下所示:

drop trigger newproduct_trigger;

3.啟用和禁用觸發器,我們用disable trigger和enable trigger語句:

--禁用觸發器disable trigger newproduct_trigger on products;--重新啟用觸發器enable trigger newproduct_trigger on products;

 

4.我們如何確定這個表有沒有觸發器呢？我們可以使用內建的預存程序sp_helptrigger：

--sp_helptrigger取一個表名並返回觸發器的一個列表。SP_HELPTRIGGER products;

5.使用觸發器。
   5.1  insert觸發器

insert觸發器在insert語句執行之後執行。在insert觸發器代碼內，可引用一個名為inserted的虛擬表，訪問被插入的行。

--建立一個insert觸發器，當插入一個新訂單時，產生一個新訂單號儲存到order_num中create trigger neworder_trigger on ordersafter insertasselect @@identity as order_num;--為測試這個觸發器，試著插入一下新行。insert into orders(order_date,cust_id) values(GetDate(),10001)--輸出     order_num--            20010

5.2  delete觸發器
delete觸發器在delete語句執行之後執行。在delete觸發器代碼內，可引用一個名為deleted的虛擬表，訪問被刪除的行。

--使用delete觸發器儲存將要被刪除的行到一個存檔表中create trigger deleteorder_trigger on ordersafter deleteasbegin    insert into orders_archive(order_num,order_date,cust_id)  select order_num,order_date,cust_id from deleted;end;

此觸發器在從表orders中刪除行時知行。它使用insert select語句儲存deleted中的行到一個名為orders_achive的存檔表中。

5.3 update觸發器

update觸發器在update語句執行之後執行。在update觸發器代碼中，你可以引用名為deleted的虛擬表訪問以前的值，引用名為inserted的虛擬表訪問新更新的值。

--下面的例子中保證州名縮寫總是大寫。create trigger vendor_trigger on vendorsafter insert,updateasbegin    update vendors    set vend_state=upper(vend_state)    where vend_id in (select vend_id from inserted);end;

此觸發器在insert,update之後執行。每當行被插入或更新時，vend_state中的值都用upper(vend_state)替換。

 

SqlServer的觸發器