SQL Server：觸發器詳解

1. 概述

觸發器是一種特殊的存儲過程，它不能被明確地調用，而是在往表中插入記錄﹑更新記錄或者刪除記錄時被自動地激活。 所以觸發器可以用來實現對錶實作複雜的完整性約束。

2. 觸發器的分類

SQL Server2000提供了兩種觸發器:「Instead of」 和「After」 觸發器。

一個表或視圖的每一個修改動作(Insert、Update和Delete)都可以有一個“Instead of” 觸發器，一個表的每個修改動作都可以有多個“After”觸發器。

2.1 “Instead of”觸發器

“Instead of”觸發器在執行真正“插入”之前被執行。除表之外，「Instead of」 觸發器也可以用於視圖，用來擴展視圖可以支援的更新操作。

「Instead of」觸發器會取代所要執行的SQL語句，言下之意就是所要執行SQL並不會「真正執行」

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    select 学号, 姓名 from deleted
end
 
delete from 学生 where 学号 = 4

上例中定義了「trigger學生_Delete」觸發器，該觸發器從“delete”表中列印出所要刪除的學生.在執行“delete”操作後，會發現“學號= 4”的學生並未被刪除， 原因在於“trigger學生Delete”替代了所要執行的“ delete from 學生where 學號= 4」語句，而在「trigger學生_Delete」中並未真正刪除學生。

2.2 “After”觸發器

“After”觸發器在Insert、Update或Deleted語句執行之後被觸發。 “After”觸發器只能用於表。

「After」觸發器主要用於表在修改後（insert、update或delete操作之後），來修改其他表

3. SQL Server：觸發器詳解

SQL Server為每個觸發器都創建了兩個專用表:Inserted表和Deleted表。

這兩個表由系統來維護，它們存在於記憶體中而不是在資料庫中，可以理解為一個虛擬的表。

這兩個表的結構總是與被該觸發器作用的表的結構相同。

觸發器執行完成後，與該觸發器相關的這兩個表也被刪除。

Deleted表存放由於執行Delete或Update語句而要從表中刪除的所有行。

Inserted表存放由於執行Insert或Update語句而要向表中插入的所有行。

4. 觸發器的執行過程

如果一個Insert﹑update或delete語句違反了約束，那麼這條SQL語句就沒有執行成功，因此「After」觸發器也不會被啟動。

「Instead of」 觸發器可以取代激發它的操作來執行。它在Inserted表和Deleted表剛剛建立，其它任何操作還沒有發生時被執行。因為“Instead of” 觸發器在約束之前執行，所以它可以對約束進行一些預處理。

5. 建立觸發器

create trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

   

6. 修改觸發器：

alter trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

   

8. 查看資料庫中已有觸發器：

8.1 查看資料庫中所有觸發器

drop trigger trigger_name

   

8.2 查看單一觸發器

select * from sysobjects where xtype='TR'

   

兩張

號varchar)、借書記錄(學號int, 圖書編號int)

實現功能：在刪除學生表時，如果該學生仍有借書記錄（未還）則不能刪除

exec sp_helptext '触发器名'

   

10. 「After」觸發器

10.1 在「訂單」表中建立觸發器，當向「訂單」表插入一條訂單記錄時，檢查「商品」表的貨品狀態「狀態」是否為1(正在整理)，則不能往「訂單」表格加入該訂單。

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号)
        delete from 学生 where 学生.学号 in (select 学号 from deleted)
end

   

該範例中「pid」為商品編碼

該範例的if判斷嚴格來講是不準確的，因為「訂單」表如果每次插入一條記錄，該判斷沒有問題；如果一次插入多筆記錄，則「select 狀態」傳回的是多行。

10.2 在「訂單」表建立一個插入觸發器，在新增一條訂單時，減少「商品」表對應的貨品記錄中的庫存。

create trigger trigger_订单_insert
on 订单
after insert
as
    if (select 状态 from 商品, inserted where 商品.pid = inserted.pid)=1
    begin
        print 'the goods is being processed'
        print 'the order cannot be committed'
        rollback transaction --回滚，避免加入
    end

   

10.3 在“商品”表建立删除触发器，实现“商品”表和“订单”表的级联删除。

create trigger goodsdelete trigger_商品_delete
on 商品
after delete
as
    delete from 订单 where 订单.pid in (select pid from deleted)

   

10.4 在“订单”表建立一个更新触发器，监视“订单”表的“订单日期”列，使其不能被“update”.

create trigger trigger_订单_update
on 订单
after update
as
    if update(订单日期)
    begin
        raiserror('订单日期不能手动修改',10,1)
        rollback transaction
    end

   

10.5 在“订单”表建立一个插入触发器，保证向“订单”表插入的货品必须要在“商品”表中一定存在。

create trigger trigger_订单_insert3
on 订单
after insert
as
    if (select count(*) from 商品, inserted where 商品.pid = inserted.pid)=0
    begin
        print '商品不存在'
        rollback transaction
    end

   

10.6 “订单”表建立一个插入触发器，保证向“订单”表插入的货品信息要在“订单日志”表中添加

alter trigger trigger_订单_insert
on 订单
for insert
as
    insert into 订单日志 select inserted.Id, inserted.pid,inserted.数量 from inserted