SQL server觸發器簡單示例

fan_rockrock發表於2014-01-26


  一:觸發器是一種特殊的儲存過程,它不能被顯式地呼叫,而是在往表中插入記錄﹑更新記錄或者刪除記錄時被自動地啟用。所以觸發器可以用來實現對錶實施複雜的完整性約束。

  二:SQL Server為每個觸發器都建立了兩個專用表:Inserted表和Deleted表。這兩個表由系統來維護,它們存在於記憶體中而不是在資料庫中。這兩個表的結構總是與被該觸發器作用的表的結構相同。觸發器執行 完成後,與該觸發器相關的這兩個表也被刪除。

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

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

  三:Instead of 和 After觸發器

  SQL Server2000提供了兩種觸發器:Instead of 和After 觸發器。這兩種觸發器的差別在於他們被啟用的同:

  Instead of觸發器用於替代引起觸發器執行的T-SQL語句。除表之外,Instead of 觸發器也可以用於檢視,用來擴充套件檢視可以支援的更新操作。

  After觸發器在一個Insert,Update或Deleted語句之後執行,進行約束檢查等動作都在After觸發器被啟用之前發生。After觸發器只能用於表。

  一個表或檢視的每一個修改動作(insert,update和delete)都可以有一個instead of 觸發器,一個表的每個修改動作都可以有多個After觸發器。

  四:觸發器的執行過程

  如果一個Insert﹑update或者delete語句違反了約束,那么After觸發器不會執行,因為對約束的檢查是在After觸發器被激動之前發生的。所以After觸發器不能超越約束。

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

  五:使用T-SQL語句來建立觸發器

  基本語句如下:

  create trigger trigger_name

  on {table_name | view_name}

  {for | After | Instead of }

  [ insert, update,delete ]

  as

  sql_statement

  六:刪除觸發器:

  基本語句如下:

  drop trigger trigger_name

  七:檢視資料庫中已有觸發器:

  -- 檢視資料庫已有觸發器

  use jxcSoftware

  go

  select * from sysobjects where xtype='TR'

  -- 檢視單個觸發器

  exec sp_helptext '觸發器名'

  八:修改觸發器:

  基本語句如下:

  alter trigger trigger_name

  on {table_name | view_name}

  {for | After | Instead of }

  [ insert, update,delete ]

  as

  sql_statement

  九:相關示例:

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

  create trigger orderinsert

  on orders

  after insert

  as

  if (select status from goods,inserted

  where goods.name=inserted.goodsname)=1

  begin

  print 'the goods is being processed'

  print 'the order cannot be committed'

  rollback transaction --回滾,避免加入

  end

  2:在Orders表建立一個插入觸發器,在新增一條訂單時,減少Goods表相應的貨品記錄中的庫存。

  create trigger orderinsert1

  on orders

  after insert

  as

  update goods set storage=storage-inserted.quantity

  from goods,inserted

  where

  goods.name=inserted.goodsname

  3:在Goods表建立刪除觸發器,實現Goods表和Orders表的級聯刪除。

  create trigger goodsdelete

  on goods

  after delete

  as

  delete from orders

  where goodsname in

  (select name from deleted)

  4:在Orders表建立一個更新觸發器,監視Orders表的訂單日期(OrderDate)列,使其不能手工修改.

  create trigger orderdateupdate

  on orders

  after update

  as

  if update(orderdate)

  begin

  raiserror(' orderdate cannot be modified',10,1)

  rollback transaction

  end

  5:在Orders表建立一個插入觸發器,保證向Orders表插入的貨品名必須要在Goods表中一定存在。

  create trigger orderinsert3

  on orders

  after insert

  as

  if (select count(*) from goods,inserted where goods.name=inserted.goodsname)=0

  begin

  print ' no entry in goods for this order'

  rollback transaction

  end

  6:Orders表建立一個插入觸發器,保證向Orders表插入的貨品資訊要在Order表中新增

  alter trigger addOrder

  on Orders

  for insert

  as

  insert into Order

  select inserted.Id, inserted.goodName,inserted.Number from inserted

相關文章