觸發器設計技巧與執行個體

 

關鍵字: 觸發器 執行個體

在資料庫設計中,有兩種方法可設定自動化的資料處理規則,一種是條件約束,一種是觸發器,一般而言,條件約束比觸發器較容易設定及維護,且執行效率較好,但條件約束只能對資料進行簡單的欄位檢核,當涉及到多表操作等複雜操
作時,就要用到觸發器了.

一個資料庫系統中有兩個虛擬表用於儲存在表中記錄改動的資訊,分別是:虛擬表Inserted、虛擬表Deleted

在表記錄新增時 存放新增的記錄 不儲存記錄
修改時 存放用來更新的新記錄 存放更新前的記錄
刪除時 不儲存記錄 存放被刪除的記錄

觸發器的種類及觸發時機

After觸發器:觸發時機在資料已變動完成後,它將對變動資料進行必要的善後與處理,若發現有錯誤,則用交易回復（Rollback Transaction)將此次操作所更動的資料全部回複.

Istead of 觸發器:觸發時機在資料變動前發生,且資料如何變動取決於觸發器.

現在介紹一下建立觸發器的編寫格式:

After類型:
Create Trigger 觸發器名稱
on 表名
after 操作(insert,update)
as
Sql語句

Instead of類型:
Create Trigger 觸發器名稱
on 表名
Instead of 操作(update,delete)
as
Sql語句

執行個體1:

在訂單(表orders)中的訂購數量(列名為num)有變動時,觸發器會先到客戶(表Customer)中取得該使用者的信用等級(列名為Level),然後再到信用額度(Creit)中取出該等級許可的訂購數量上下限,最後比較訂單中的訂購數量是否符合限制。

代碼:
Create Trigger num_check
on orders
after insert,update
as 
if update(num)
begin
       if exists(select a.* from orders a join customer b on a.customerid=b.customerid
       join creit c on b.level=c.level
       where a.num between c.up and c.down)
       begin
         rollback transaction
         exec master..xp_sendmail 'administrator','客戶的訂購數量不符合限制'
       end
end

執行個體2:

有工資管理系統中,當公司對某員工甲的月薪進行調整時,通常會先在表員工中修改薪資列,然後在表員工記錄中修改薪資調整時間與薪資

Create trigger compensation
on 員工
after update
as
if @@rowcount=0 return
if update(薪資)
begin
insert 員工記錄
select 員工遍號,薪資,getdate()
from inserted
end