1. 概述

觸發(fā)器是一種特殊的存儲(chǔ)過程，它不能被顯式地調(diào)用，而是在往表中插入記錄﹑更新記錄或者刪除記錄時(shí)被自動(dòng)地激活。 所以觸發(fā)器可以用來實(shí)現(xiàn)對表實(shí)施復(fù)雜的完整性約束。

2. 觸發(fā)器的分類

SQL Server2000提供了兩種觸發(fā)器:“Instead of” 和“After” 觸發(fā)器。

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

2.1 “Instead of”觸發(fā)器

• “Instead of”觸發(fā)器在執(zhí)行真正“插入”之前被執(zhí)行。除表之外，“Instead of” 觸發(fā)器也可以用于視圖，用來擴(kuò)展視圖可以支持的更新操作。
• “Instead of”觸發(fā)器會(huì)替代所要執(zhí)行的SQL語句，言下之意就是所要執(zhí)行SQL并不會(huì)“真正執(zhí)行”

alter trigger trigger_學(xué)生_Delete
on 學(xué)生
instead of Delete
as
begin
  select 學(xué)號(hào), 姓名 from deleted
end
delete from 學(xué)生 where 學(xué)號(hào) = 4 

上例中定義了“trigger學(xué)生_Delete”觸發(fā)器，該觸發(fā)器從“delete”表中打印出所要?jiǎng)h除的學(xué)生.在執(zhí)行“delete”操作后，會(huì)發(fā)現(xiàn)“學(xué)號(hào) = 4”的學(xué)生并未被刪除， 原因在于“trigger學(xué)生Delete”替代了所要執(zhí)行的“delete from 學(xué)生 where 學(xué)號(hào) = 4”語句，而在“trigger學(xué)生_Delete”中并未真正刪除學(xué)生。

2.2 “After”觸發(fā)器

• “After”觸發(fā)器在Insert、Update或Deleted語句執(zhí)行之后被觸發(fā)?！癆fter”觸發(fā)器只能用于表。
• “After”觸發(fā)器主要用于表在修改后（insert、update或delete操作之后），來修改其他表

3. Inserted和Deleted表

SQL Server為每個(gè)觸發(fā)器都創(chuàng)建了兩個(gè)專用表:Inserted表和Deleted表。

• 這兩個(gè)表由系統(tǒng)來維護(hù)，它們存在于內(nèi)存中而不是在數(shù)據(jù)庫中，可以理解為一個(gè)虛擬的表。
• 這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同。
• 觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的這兩個(gè)表也被刪除。
• Deleted表存放由于執(zhí)行Delete或Update語句而要從表中刪除的所有行。
• Inserted表存放由于執(zhí)行Insert或Update語句而要向表中插入的所有行。

4. 觸發(fā)器的執(zhí)行過程

• 如果一個(gè)Insert﹑update或者delete語句違反了約束，那么這條SQL語句就沒有執(zhí)行成功，因此“After”觸發(fā)器也不會(huì)被激活。
• “Instead of” 觸發(fā)器可以取代激發(fā)它的操作來執(zhí)行。它在Inserted表和Deleted表剛剛建立，其它任何操作還沒有發(fā)生時(shí)被執(zhí)行。因?yàn)椤癐nstead of” 觸發(fā)器在約束之前執(zhí)行，所以它可以對約束進(jìn)行一些預(yù)處理。

5. 創(chuàng)建觸發(fā)器

create trigger trigger_name
on {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相應(yīng)T-SQL語句

6. 修改觸發(fā)器：

alter trigger trigger_name
on {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相應(yīng)T-SQL語句

7. 刪除觸發(fā)器：

drop trigger trigger_name

8. 查看數(shù)據(jù)庫中已有觸發(fā)器：

8.1 查看數(shù)據(jù)庫中所有觸發(fā)器

select * from sysobjects where xtype='TR'

8.2 查看單個(gè)觸發(fā)器

exec sp_helptext '觸發(fā)器名'

9. “Instead of”相關(guān)示例:

兩張表：學(xué)生(學(xué)號(hào) int, 姓名 varchar)、借書記錄(學(xué)號(hào) int, 圖書編號(hào) int)

實(shí)現(xiàn)功能：在刪除學(xué)生表時(shí)，如果該學(xué)生仍有借書記錄（未還）則不能刪除

alter trigger trigger_學(xué)生_Delete
on 學(xué)生
instead of Delete
as
begin
  if not exists(select * from 借書記錄, deleted where 借書記錄.學(xué)號(hào) = deleted.學(xué)號(hào))
    delete from 學(xué)生 where 學(xué)生.學(xué)號(hào) in (select 學(xué)號(hào) from deleted)
end

10. “After”觸發(fā)器

10.1 在“訂單”表中建立觸發(fā)器，當(dāng)向“訂單”表中插入一條訂單記錄時(shí)，檢查“商品”表的貨品狀態(tài)“狀態(tài)”是否為1(正在整理)，則不能往“訂單”表加入該訂單。

create trigger trigger_訂單_insert
on 訂單
after insert
as
  if (select 狀態(tài) from 商品, inserted where 商品.pid = inserted.pid)=1
  begin
    print 'the goods is being processed'
    print 'the order cannot be committed'
    rollback transaction --回滾，避免加入
  end

• 該示例中“pid”為商品編碼
  
• 該示例的if判斷嚴(yán)格來講是不準(zhǔn)確的，因?yàn)椤坝唵巍北砣绻看尾迦胍粭l記錄，該判斷沒有問題；如果一次插入多條記錄，則“select 狀態(tài)”返回的是多行。

10.2 在“訂單”表建立一個(gè)插入觸發(fā)器，在添加一條訂單時(shí)，減少“商品”表相應(yīng)的貨品記錄中的庫存。

create trigger trigger_訂單_insert2
on 訂單
after insert
as
  update 商品 set 數(shù)量 = 數(shù)量 - inserted.數(shù)量
  from 商品, inserted
  where 商品.pid = inserted.pid

10.3 在“商品”表建立刪除觸發(fā)器，實(shí)現(xiàn)“商品”表和“訂單”表的級(jí)聯(lián)刪除。

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

10.4 在“訂單”表建立一個(gè)更新觸發(fā)器，監(jiān)視“訂單”表的“訂單日期”列，使其不能被“update”.

create trigger trigger_訂單_update
on 訂單
after update
as
  if update(訂單日期)
  begin
    raiserror('訂單日期不能手動(dòng)修改',10,1)
    rollback transaction
  end

10.5 在“訂單”表建立一個(gè)插入觸發(fā)器，保證向“訂單”表插入的貨品必須要在“商品”表中一定存在。

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 “訂單”表建立一個(gè)插入觸發(fā)器，保證向“訂單”表插入的貨品信息要在“訂單日志”表中添加

alter trigger trigger_訂單_insert
on 訂單
for insert
as
insert into 訂單日志 select inserted.Id, inserted.pid,inserted.數(shù)量 from inserted 

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持本站。

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。