SQL Sever 2000中的前觸發器和後觸發器

我們知道，觸發器是一種特殊的儲存過程。當Insert Update或Delete語句修改表中一個或者多個行時就會執行觸發器。因為SQL Server對特定表上的每一個指定操作呼叫一個觸發器，所以可以使用觸發器擴充套件SQL Sever 的內建完整性和資料操縱功能。

（不像Delete 語句，Trancate Table 語句不啟用觸發器，Write Text 語句也不啟用觸發器）。

在SQL Sever 2000中支援兩種型別的觸發器，前觸發器(Instead Of Trigger)和後觸發器(After Trigger)。前觸發器就是在語句執行之前啟用觸發器，而後觸發器就是在語句執行之後啟用觸發器。可以通過FOR 子句來選擇使用何種觸發器。

當為每一種操作建立一個觸發器時，可以為所有三種操作建立一個觸發器，並且使用相應的程式設計技術處理每一種操作。下面的示例在For 子句中列出了三種語句型別，並且使用條件語句將相應的跟蹤值插入到CustUpdLog 表中。

Create Trigger TrackCustomerUpdates
On AppDta.dbo.Customer
For Insert,Update,Delete
As
Declare @InsertedCount Int
Declare @DeletedCount Int
Set @InsertedCount=(Select Count(*)From inserted)
Set @DeletedCount=(Select Count(*)From deleted)
If ( @InsertedCount>0)Begin
Insert Into AppDta.dbo.CustUpdLog
( CustID,
Action,
UpdUser,
UpdDateTime)
Select CustId,
Case
When( @DeletedCount>0)Then
'Update'
Else 'Insert'
End,
Current_User,
Current_TimeStamp
From inserted
End
Else If(@DeletedCount>0)Begin
Insert Into AppDta.dbo.CustUpdLog
( CustId,
Action,
UpdUser,
UpdDateTime)
select CustId,
'Delete',
Current_User,
Current_TimeStamp
From deleted
End

從以上這個示例我們可以發現，無論何時Insert或者Update語句影響一個或者多行時，inserted 臨時表都有記錄行。無論何時Delete 或者Update 語句影響一個或者多行時，deleted 臨時表都有記錄行。對於一個Update 語句，deleted 臨時表有舊行，inserted 臨時表有新行。這個示例還反映了觸發器的另一個重要方面：對於某個表的Update 或者Delete 操作，即使該語句沒有影響到行，也啟用觸發器 (也就是說沒有滿足Where 子句的行)。 觸發器的儲存過程應該預測這種可能性。

不僅可以為一個表建立多個觸發器，而且還可以為一個表的同一個SQL 語句(例如Update 語句)建立多個後觸發器，不能為同一個SQL 語句建立多個前觸發器。每一個新的Create Trigger 語句增加觸發器到那些指定表和語句已有的觸發器中。對於所建立的多個觸發器，可以用系統儲存過程sp_settriggerorder 來指定第一個被啟用的觸發器和最後一個被啟用的觸發器，而對於其他的觸發器，則不能指定其啟用順序，只能由系統決定。這種觸發器的特徵不會引起任何特殊的問題。因為總是可以實現各種動作作為正常的儲存過程，並且按照要求的順序從一個觸發器中呼叫它們。

 

儘管觸發器是一種儲存過程，但是不能使用Execute 語句呼叫它，如果有希望共享觸發器和正常的儲存過程的編碼，那麼只需把共享程式碼放在儲存過程中，從觸發器中呼叫它。如果一個觸發器修改一個表，那麼這些修改可能會啟用另一個觸發器，或者本身。在預設情況下，SQL Sever 允許這種巢狀的觸發器呼叫深度為32層。雖然我們建議允許巢狀的和疊代的觸發器，但是可以使用系統儲存過程禁止這麼做。下面的語句在指定的資料庫上防止疊代觸發器：

sp_dboption AppDta,`recursive triggers',`false'

為了在所有資料庫中防止巢狀觸發器呼叫(包括疊代呼叫)，可以使用下面的語句：

sp_configure `nested triggers',0

前面以後觸發器為例介紹了觸發器的基本內容，下面再介紹一下前觸發器的不同之處。要建立一個前觸發器必須用Instead Of 顯式宣告，如下面的例子：

create Trigger TrackCustomerUpdates
On AppDta.dbo.Customer
Instead Of Update
As
Insert Into AppDta.dbo.CustUpdLog
(CustId,
Action,
UpdUser,
UpdDateTime)
Select CustId,
‘Update’,
Current_User,
Current_TimeStamp
From inserted

與後觸發器不同的是：前觸發器既可以在表又可以在檢視上建立，但一條語句只能建立一個前觸發器，因此，前觸發器不存在啟用順序問題

 

觸發器應用舉例：從當前資料庫伺服器的Shop表Insert操作同步到另一臺伺服器的Shop。

CREATE TRIGGER Trigger_SynShopForInsert1
ON dbo.Shop
FOR INSERT
AS
  insert into OtherServer.dbo.shop
(
lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex
)
select  lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex
from shop where lngShopID in (select lngshopid from inserted)

 

或者使用下面的語句：

CREATE TRIGGER Trigger_SynShopForInsert2
ON dbo.Shop
FOR INSERT
AS
  insert into OtherServer.dbo.shop
(
lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex
)
select  lngShopID,strShopCode,strName,strShopName,strDescription,lngIndex
from  inserted

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-567502/，如需轉載，請註明出處，否則將追究法律責任。