程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQL Server:觸發器實例詳解,sql觸發器

SQL Server:觸發器實例詳解,sql觸發器

編輯:更多數據庫知識

SQL Server:觸發器實例詳解,sql觸發器


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. Inserted和Deleted表

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

  • 這兩個表由系統來維護,它們存在於內存中而不是在數據庫中,可以理解為一個虛擬的表。
  • 這兩個表的結構總是與被該觸發器作用的表的結構相同。
  • 觸發器執行完成後,與該觸發器相關的這兩個表也被刪除。
  • Deleted表存放由於執行Delete或Update語句而要從表中刪除的所有行。
  • Inserted表存放由於執行Insert或Update語句而要向表中插入的所有行。

對表的操作 Inserted邏輯表 Deleted邏輯表 增加記錄(insert) 存放增加的記錄 無 刪除記錄(delete) 無 存放被刪除的記錄 修改記錄(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語句

7. 刪除觸發器:

drop trigger trigger_name

8. 查看數據庫中已有觸發器:

8.1 查看數據庫中所有觸發器

select * from sysobjects where xtype='TR'

8.2 查看單個觸發器

exec sp_helptext '觸發器名'

9. “Instead of”相關示例:

兩張表:學生(學號 int, 姓名 varchar)、借書記錄(學號 int, 圖書編號 int)

實現功能:在刪除學生表時,如果該學生仍有借書記錄(未還)則不能刪除

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

10. “After”觸發器

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

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

  • 該示例中“pid”為商品編碼
  • 該示例的if判斷嚴格來講是不准確的,因為“訂單”表如果每次插入一條記錄,該判斷沒有問題;如果一次插入多條記錄,則“select 狀態”返回的是多行。

10.2 在“訂單”表建立一個插入觸發器,在添加一條訂單時,減少“商品”表相應的貨品記錄中的庫存。

create trigger trigger_訂單_insert2

on 訂單

after insert

as

  update 商品 set 數量 = 數量 - inserted.數量

  from 商品, inserted

  where 商品.pid = inserted.pid

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 

以上就是本文的全部內容,希望對大家的學習有所幫助,也希望大家多多支持幫客之家。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved