Posted: 2005/1月/07 9:26上午 | IP記錄

使用存儲過程

該部分將向你介紹如何創建和使用你自己的存儲過程。在SQL Server中存儲過程是和傳統的計算機應用程序最相近的事物，並具有如下的優點：

假如你有一套複雜的SQL語句需要在多個Active Server Pages中執行。你可以把他們放入一個存儲過程，然後執行該存儲過程。這能夠減少你Active Server Pages的大小。同時還能確保在每一頁上執行的SQL語句都相同。

當你執行一個SQL的批處理時。服務器首先必須編譯在批處理中的所有語句。這不但需要時間，還要花費服務器資源。相比較而言，在 存儲過程第一次執行後，它就不需要重新編譯了。通過使用存儲過程，你可以跨過編譯這一步，更快地執行SQL語句集合。從一個Ac tive Server頁中執行一個存儲過程比執行一個SQL語句的集合更有效。

你可以對存儲過程輸入輸出值。這意味著存儲過程非常的靈活，相同的存儲過程可以根據不同的輸入值返回不同的信息。

當你向數據庫服務器傳遞一個SQL語句集合時，必須傳遞其中的每一個獨立語句，而當你執行存儲過程時，相反的，僅僅傳遞一個簡單 的語句。通過使用存儲過程，你可以減少在網絡上的阻塞。

你可以配置表的權限，比如用戶只能通過使用存儲過程來修改表。這就能增加在你數據庫中表的安全性。

你可以在其他的存儲過程內部執行你的存儲過程。這種策略就允許你在非常小的存儲過程上建立非常複雜的存儲過程。這也意味著你可以 為許多不同的編程任務使用相同的存儲過程。

當你在Active Server頁中添加SQL語句時，你必須仔細考慮能否把這些語句放置到存儲過程中。上面提到的優點都是實質性的。如下一部分所 示，存儲過程是非常容易創建的。

使用CREATE PROCEDURE創建存儲過程

你可以使用CREATE PROCEDURE來創建一個存儲過程。下面就是一個非常簡單的存儲過程的一個例子：

CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors

當你創建存儲過程時，你必須給它指定一個名稱。在本例子中，存儲過程的名稱為retrieve_authors。你可以給存儲過 程賦予任何你想要的名稱，但最好你能夠使該名稱在一定程度上描述存儲過程的功能。

每一個存儲過程都包括一個或多個SQL語句。為了指明是存儲過程一部分的SQL語句，你只需簡單地在關鍵詞AS後面包含它們。在 前面例子中的存儲過程只包含一個SQL語句。當該存儲過程執行時，它返回在Authors表中所有的記錄。

你可以使用EXECUTE語句來執行一個存儲過程。比如，為了執行retrieve_authors存儲過程，你可以使用如下的 語句：

EXECUTE retrieve_authors

當你執行該存儲過程時，所有包括在其中的SQL語句都會執行，在上面的例子中，會返回所有在Authors表中的記錄。

當在批處理中的第一個語句是調用存儲過程時，你並不需要使用EXECUTE語句。你可以簡單地提供存儲過程的名稱來執行存儲過程 。比如在ISQL/W中，可以象下面所示來執行存儲過程：

retrieve_authors

這起同樣的作用。存儲過程會被執行，並會返回結果。然而如果在該存儲過程之前還有其他的任何語句，你就會收到錯誤信息（一般地， 語法錯誤）。

當你創建和執行一個存儲過程時，這僅僅是在某一個數據庫的範圍內完成。假設你在數據庫MyDatabase內創建了存儲過程re trieve_authors。如果沒有指明過程調用，你就不能在另一個數據庫比如MyDatabase2中調用存儲過程ret rieve_authors。假如你需要在Mydatabase2中執行存儲過程retrieve_authors，你必須使用 如下的語句（注意下面的兩個點號）：

EXECUTE Mydatabase..retrieve_authors

一旦你已經創建了一個存儲過程，你就能使用系統存儲過程sp_helptext來觀看在該存儲過程的的SQL語句。比如，如果你 輸入命令sp_helptext retrieve_authors，就會顯示下面的結果：

text

……………………………………………

CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors

注意

你可能感到奇怪的是，sp_helptext系統過程本身就是一種存儲過程類型。它是一種系統的存儲過程。（系統存儲過程存儲在 Master數據庫中，能夠被所有的數據庫訪問。）為了滿足你的好奇心，你可以使用命令sp_helptext sp_helptext來觀看組成sp_helptext本身的SQL語句。

你在創建完存儲過程後，不能對其進行修改。假如你需要修改一個存儲過程。你必須首先破壞它，然後重新構建之。為了破壞一個存儲過 程。你可以使用DROP PROCEDURE語句，例如下面的語句刪除retrieve_authors存儲過程：

DROP PROCEDURE retrieve_authors

注意

你可以使用系統存儲過程sp_help來觀看在當前數據庫中所有存儲過程的列表。假如你不加任何修改地執行了sp_help。該 過程會顯示在當前數據庫中所有的存儲過程、觸發器和表。假如在sp_help後面跟上指定的存儲過程，sp_help會僅僅顯示 那個存儲過程的信息。

Posted: 2005/1月/07 9:26上午 | IP記錄

使用SQL Enterprise Manager 創建存儲過程

假如你要創建一個複雜的存儲過程，使用SQL Enterprise Manager要比ISQL/w要容易得多，該程序允許你更容易地修改存儲過程，它可以自動為你刪除和重新創建存儲過程。

要使用SQL Enterprise Manage來創建一個存儲過程。首先啟動該程序，選擇你要用於存儲表的數據庫，然後在菜單上選擇Manage|Manage Stored Procedure,這時就會顯示如圖12。4所示的窗口。

當你第一次打開存儲過程管理對話框窗口時，會自動顯示文本CREATE PROCEDURE <PROCEDURE NAME>。如果學過上面部分的內容，你就會對該文本非常的熟悉。這是CREATE PROCEDURE語句。替換<PROCEDURE NAME>成你所需要的名稱。

在關鍵詞AS後面，你可以輸入組成你存儲過程的語句。比如，假設你在數據庫中有一個名為Philosophers的數據庫表，其 中包含了一些哲學家的列表。下面的存儲過程在如果在數據庫中有Plato存在時，會打印「Plato is a philosopher」；否則它會打印「Plato is not a philosopher」

CREATE PROCEDURE check_philosophers AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=」Plato」)

PRINT 「Plato is a philosopher」

ELSE

PRINT 「Plato is not a philosopher」

當你已經完成輸入存儲過程後，你可以點擊保存對像按鈕來保存之（它看起來像一個綠色的三角形）。

圖12.4 存儲過程管理對話框窗口

為了觀看在你第一次保存之後的存儲過程的文本，你需要在Procedure下拉菜單中再次選擇該存儲過程。SQL Server會自動在你的文檔中插入新的語句。比如，在保存完check_philosophesr過程之後，會顯示如下的文本 ：

if exists (select * from sysobjects where id=object_id(『dbo.check_philisophers』) and sysstat & 0xf=4)

drop procedure dbo.check_philosophers

GO

CREATE PROCEDURE check_philosophers AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=」Plato」)

PRINT 「Plato is a philosopher」

ELSE

PRINT 「Plato is not a philosopher」

GO

為什麼SQL Server要添加這些語句呢？這些語句是什麼用的呢？這些添加的語句並不包含在存儲過程中。它們的使用是使存儲過程的使用變得 更加容易。

當在過程中的第一個語句執行時，它檢查是否有存儲過程check_philosophers已經存在於數據庫中。假如該過程已經 存在，語句會刪除該過程。這樣，假如你修改了你的存儲過程，然後點擊Save Object按鈕時，該語句會確保過程在修改後再次創建之前已經被刪除了。

注意

在修改你的存儲過程時，注意不要刪除那些添加的語句。假如說你刪除了第一個語句，你就不能正確地保存你的存儲過程。相反的，你就 會獲得一個錯誤信息告訴你，存儲過程已經存在於數據庫中（假如你正處於這種情況下，你只需要用語句DROP PROCEDURE來刪除前一版本）。

使用SQL Enterprise Manager而不是ISQL/w來創建你的存儲過程的主要優點是使用Enterprise Manage可以在將來很容易地修改存儲過程，在已經保存了存儲過程之後，你可以使用管理存儲過程對話框來選擇和修改它。使用I SQL/w在創建了存儲過程之後對其進行修改是非常困難的。

Posted: 2005/1月/07 9:26上午 | IP記錄

給存儲過程傳值

當你調用一個存儲過程時，你可以使用參數來傳值給它，從而使你的存儲過程變得非常的靈活。比如，你想修改過程check_phi losophers，使之能夠檢測是否存在某一個哲學家。你可以使用如下的語句進行修改：

CREATE PROCEDURE check_philosophers

(@philosopher VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher)

PRINT 「A philosopher」

ELSE

PRINT 「Not a philisopher」

當該過程執行時，它檢查傳遞給變量@philosopher的姓名是否存在於表Philosophers中。假如@philos opher的值存在於表中，打印文本「A philosopher」，否則打印文本「Not a philisopher」

當你在存儲過程中包含參數時，你把它們列在CREATE PROCEDURE語句的後面的括號內，但必須在關鍵詞AS之前。對於每一個參數，你都必須指明數據類型。在一個單獨的過程中， 你最多能設置255個參數。比如下面的過程檢查是否在傳遞的姓名當中，至少有一個是哲學家的姓名：

CREATE PROCEDURE check_philosophers

(@firstname VARCHAR(30)，@secondname VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers

WHERE name=@firstname OR name=@secondname)

PRINT 「At least one of them is a philosopher」

ELSE

PRINT 「Neither one of them is a philisopher」

為了執行一個具有一個或多個參數的存儲過程，你只需簡單地在存儲過程名稱的後面列出參數的值。比如下面的語句檢查Plato和A ristotle中是否至少有一個是哲學家：

EXECUTE check_philisophers 「Plato」,」Aristotle」

假如一個存儲過程具有多個參數。你必須以正確的順序來傳值。有時候這並不是很方便。你可以使用另一種方法，通過名稱來傳遞參數， 如下所示：

EXECUTE check_philisophers @firstname=「Plato」, @secondname=」Aristotle」

該語句完成於前一語句完全相同的事情。然而通過使用參數名，你可以使用任何你想要的順序來傳遞參數。

Posted: 2005/1月/07 9:27上午 | IP記錄

從存儲過程中獲得值

你可以從存儲過程中接受值。這些值可以直接在你的Active Server Pages中使用（參看第24章「使用Commands」）。同樣，你可以在其他的存儲過程中獲得這些值。假如第一個過程調用了 第二個存儲過程，則第一個過程能接受有第二個過程設置的參數值。

例如，下面的存儲過程輸出變量@conclusion的值：

CREATE PROCEDURE check_philosophers

(@philosopher VARCHAR(30)，@conclusion VARCHAR(30) OUTPUT)

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher)

SELECT @conclusion= 「A philosopher」

ELSE

SELECT @conclusion= 「Not a philisopher」

注意在本例子中關鍵詞OUTPUT的使用。該關鍵詞緊跟在參數@conclusion的定義後面。這指明該參數將會用於從該過程 中輸出信息。在這個簡單的例子中，參數的值將會是「A philosopher」或「Not a philisopher」，根據變量@philosophe的值的不同而變化。

為了這些一個具有輸出參數的存儲過程，你需要在EXECUTE語句中使用關鍵詞OUTPUT 。假如你在一個批處理或者另外一個存儲過程中執行該過程時，你必須首先定義一個變量用於存儲從過程中傳遞出的值，如下面的例子所 示：

DELCARE @proc_results VARCHAR(30)

EXECUTE check_philosophers 「Plato」,@proc_results OUTPUT

PRINT @proc_results

在該例子中的第一個語句定義了將用於存儲從過程check_philosophersZ中傳出的參數值的變量。該變量將和輸出參 數的數據類型一模一樣。第二個語句執行存儲過程。注意變量@proc_results後面必須緊跟關鍵詞OUTPUT。最後變量 @proc_results的值被打印到屏幕上。

你同樣可以使用名稱來接收輸出參數的值，下面就是一個簡單的例子：

DECLARE @proc_results VARCHAR(30)

EXECUTE check_philosophers @philosopher=」Plato」,@conclusion=@proc_results OUTPUT

PRINT @proc_results

注意在該EXECUTE語句中，參數的名稱總是列在前面。你要使用@conclusion=@proc_results來接收參 數@conclusion的值，而不是你可能期望的@proc_results=@conclusion。

Posted: 2005/1月/07 9:27上午 | IP記錄

在存儲過程中使用RETURN語句

在上面的文章中我們已經介紹了RETURN語句的使用。在存儲過程中該語句的用法和你在批處理中的用法是一模一樣的。RETUR N語句會導致過程立即退出。考察下面的例子：

CREATE PROCEDURE check_tables

(@who VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)

BEGIN

PRINT 「In the Philosophers Table」

RETURN

END

IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)

BEGIN

PRINT 「In the Authors Table」

RETURN

END

PRINT 「Not in any tables!」

RETURN

該過程檢查了兩個表以判斷一個人是否是哲學家或作者。假如提供的名稱即不是哲學家也不是作者，打印文本「Not in any tables!」。RETURN語句會在一旦存在匹配時，立刻退出該過程。

比如，假定你使用參數「Plato」來執行該過程。首先使用表Philosophers來檢測是否存在「Plato」。由於該名 字存在於該表中，所以過程打印文本」In the Philosophers table」，然後過程在執行到RETURN語句時，退出。

注意

你可以使用RETURN語句結束任何過程。這實際上並不完成任何事情。因為在任何情況下，過程總是要退出的。

當你在存儲過程而不是在批處理中使用RETURN語句時，你能夠返回一整數值。該整數值代表一個狀態碼。下面就是上面的例子經過 改寫後，返回特定的整數值。

CREATE PROCEDURE check_tables

(@who VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)

BEGIN

RETURN(1)

END

IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)

BEGIN

RETURN(2)

END

RETURN(3)

該過程完成和前面的那個過程相同的任務。然而當在表中發現該姓名時，並不打印信息，代替地，該過程使用RETURN語句來指明從 那個表中找到該名稱。例如，假如你使用參數「James Joyce」來執行該過程時。存儲過程會返回值2，因為James Joyce在表Authors中，而不是在表Philosophers中。你可以在Active Server Page或其他的存儲過程中使用狀態值來確定該姓名所在的表。

當使用狀態值時，你必須使用對於1或小於-99的值。SQL Server使用值0來報告一個存儲過程的成功執行。它同時使用小於0對於-100的值來報告錯誤(參看表12。1以獲得SQL Server使用的狀態值的完全列表)。

值
 意思
 
0
 過程成功執行。
 
-1
 對像丟失。
 
-2
 發生數據類型錯誤。
 
-3
 處理過程被死鎖。
 
-4
 發生權限錯誤。
 
-5
 發生語法錯誤。
 
-6
 發生惡意用戶錯誤。
 
-7
 發生資源錯誤，比如空間不夠等。
 
-8
 遭遇非致命的內部問題。
 
-9
 遭遇系統限制。
 
-10
 發生致命的內部不穩定性。
 
-11
 發生致命的內部不穩定性。
 
-12
 表或索引被破壞。
 
-13
 數據庫被破壞。
 
-14
 發生硬盤錯誤。
 
包含在-15到-99之間的值有SQL Server保留以在將來使用。
 

表12.1 過程狀態值

注意

當你使用RETURN語句返回狀態值時，切記不要返回NULL值。這會導致錯誤的結果。

為了獲得在前面例子中過程返回的狀態值，你可以使用如下的語句：

DECLARE @conclusion INT

EXECUTE @conclusion=check_tables 「James Joyce」

SELECT @conclusion

變量@conclusion用於保存狀態值。它必須定義成INT整數數據類型。當你執行存儲過程check_tables時，使 用下面的語句將狀態值賦予該變量：

EXECUTE @conclusion=check_tables 「James Joyce」

Posted: 2005/1月/07 9:27上午 | IP記錄

使用觸發器

觸發器是一系列當在表中的數據修改時要執行的SQL語句的集合。你可以創建一些當在表中的數據在插入，修改或刪除時觸發的觸發器 。在本節中，你將學習如何創建和使用觸發器。

使用命令CREATE TRIGGER來創建觸發器

你可以使用語句CREATE TRIGGER來創建一個觸發器。與存儲過程不同，每個觸發器都必須和某一個特定的表相關，而且每一個觸發器都只和作用於該表上 的一個或多個動作有關，下面就是CREATE TRIGGER語句的一個簡單例子：

CREATE TRIGGER tr_webusers_insert ON webusers FOR INSERT AS

EXECUTE master..xp_sendmail 「administrator」 「New user registered!」

該觸發器的名稱為tr_webusers_insert。你可以使用任何你想用的名稱來命名觸發器，但是每個觸發器都必須有唯一 的名稱。在觸發器的名稱內包含和觸發器相關聯的表和動作作為觸發器名稱的一部分是一個很好的主意。

在CREATE TRIGGER語句中等ON短語用於指明和觸發器相關聯的數據表。本例子中的觸發器和表webusers相關聯，它會在該表中的 數據變動時觸發。

FOR短語指明會觸發該觸發器的動作。在本例子中，觸發器會在有任何新的數據添加入該表時被觸發。同樣的，你可以創建一個在表內 的數據進行更新或刪除時觸發的觸發器，你只需要使用關鍵詞UPDATE或DELETE。

在AS短語後面，你可以列出一個或多個SQL短語。在本例子中，觸發器僅包含一個單獨的語句。當有新的記錄添加到表webuse rs中時，管理員會自動發送Email信息，New User registered!

對於觸發器，請牢記以下重要幾點：

觸發器和某一指定的表格有關，當該表格備刪除時，任何與該表有關的觸發器同樣會被刪除。比如，當表格webusers被刪除時， 觸發器tr_webusers_insert也同樣會被刪除。

在一個表上的每一個動作只能有一個觸發器與之關聯。例如：你不能在表webusers上創建第二個觸發器，該觸發器在有數據插入 表中時觸發。

在一個單獨的表上，你最多只能創建三個觸發器與之關聯，一個INSERT觸發器，一個DELETE觸發器和一個UPDATE觸發 器。

當你添加第二個由相同動作觸發的觸發器時，第一個觸發器會在沒有任何警告信息的條件下被刪除。這是件非常令人煩惱的事情。所以必 須仔細地記錄你的觸發器信息，以防止該類事情的發生。

注意

要觀看所有在當前數據庫中的觸發器列表。你可以使用系統存儲過程sp_help。假如你不加任何修改地執行sp_help，該過 程將會顯示在當前的數據庫中所有的過程，觸發器及表。假如在觸發器後面跟上一個指定的觸發器名稱（比如 sp_help tr_webusers_insert），sp_help將會只顯示和該觸發器有關的信息。

你可以讓一個觸發器和一個單獨的表中多於一個的動作關聯。比如你可以修改上面例子中的觸發器，使其在表中的數據在添加或更新時觸 發。下面就是你修改後的結果：

CREATE TRIGGER tr_webusers ON webusers FOR INSERT,DELETE AS

EXECUTE master..xp_sendmail 「administrator」 「User registered or modified!」

在你創建了觸發器後，你可以使用系統存儲過程sp_helptext來觀看包含在觸發器中的語句。該過程顯示觸發器的文本。例如 ：命令 sp_helptext tr_webusers會顯示下面的結果：

text

………………………………………..

CREATE TRIGGER tr_webusers ON webusers FOR INSERT,DELETE AS

EXECUTE master..xp_sendmail 「administrator」 「User registered or modified!」

觸發器只有在其FOR短語後面指定的動作發生時執行。你不能直接執行一個觸發器。你不能直接執行一個觸發器。比如說假如你嘗試執 行tr_webusers時，你會得到如下的錯誤信息：

The request for procedure 『tr_webusers』 failed because 『tr_webusers』 is a trigger object

有三種方法可以破壞一個觸發器，你可以使用命令DROP TRIGGER後面跟觸發器的名稱來顯式地刪除一個觸發器。比如，下面的語句刪除觸發器tr_webusers。

DROP TRIGGER tr_webusers

當數據表被刪除時，與之相關的觸發器會自動地被刪除了。例如，當表webusers被刪除時，觸發器tr_webusers會自 動被刪除掉。

最後，當一個新的在同一個表中與同一動作相關聯的觸發器創建時，舊的觸發器會被自動刪除。例如，當觸發器tr_webusers 創建時，觸發器tr_erbusers_insert會自動地被刪除掉。這是因為兩個觸發器都由同一個數據表的INSERT動作 觸發。

注意

如果觸發器tr_webusers在觸發器tr_webusers_insert之前創建。觸發器tr_webusers只會被 部分刪除，這是因為觸發器tr_webusers由INSERT和UPDATE觸發。觸發器將在有數據更新時繼續觸發。然而觸發 器將不會在有數據添加時觸發。新的觸發器tr_webusers_insert會取代該任務。

Posted: 2005/1月/07 9:28上午 | IP記錄

使用SQL Enterprise Manager創建觸發器

使用SQL Enterprise Manager創建觸發器要比使用ISQL/w要容易的多。使用SQL Enterprise Manager創建觸發器的主要優勢是它能保存觸發器的文本，這樣你就可以在以後的某個時候返回來修改之。通過使用該程序，你可 以很容易地記錄和管理你創建的觸發器。

在使用SQL Enterprise Manager來創建觸發器時，首先啟動程序，選擇存儲你數據表的的數據庫，從菜單中選擇Manage|Triggers,這時 就會顯示Manage Triggers對話框。（見圖12.5）。

圖12.5 觸發器管理窗口

在該對話框的頂部有兩個下拉式列表，在Table列表內，你可以選擇一個數據表。每一個觸發器必須和一個表格相關聯而且只和一個 表格相關聯，在觸發器下拉列表框中在單詞<new>的旁邊包含三個小圖標，這三個圖標分別表示INSERT，UPD ATE和DELETE，你可以使用該列表框對被這些動作觸發的觸發器進行選擇。單詞<new>將在你保存該觸發器後 被該觸發器的名稱所代替。

在對話框的文本區域內，提供了觸發器的缺省文本，如下所示：

CREATE TRIGGER <TRIGGER NAME> ON dbo.webusers

FOR INSERT ,UPDATE,DELETE

AS

如果你已經學過前面的內容，你肯定對該文本非常熟悉；這是一個觸發器的創建語句。使用你要用的觸發器名稱來代替表達式< TRIGGER NAME>

該自動創建語句創建一個由INSERT，UPDATE和DELETE觸發的觸發器。如果你要為各個動作創建不同的觸發器，或者創 建一個只由一個動作觸發的觸發器，你只需要修改這個缺省的語句。簡單地刪除你不想使用的動作就可以了。

在關鍵字AS下面，你可以輸入你觸發器所要用的語句。比如下面的觸發器在有一個新用戶添加到表webusers中時在表webl og中添加一個新的記錄：

CREATE TRIGGER tr_webusers_insert ON dbo.webusers

FOR INSERT

AS

INSERT weblog(activity) VALUES (「New User Added」)

你可以在Manage Triggers對話框內點擊保存對像按鈕(一個綠色的三角形)來保存一個觸發器。當一個觸發器第一次被保存時，觸發器的文本從 屏幕上消失了。你需要再一次從觸發器下拉列表中選擇該觸發器。

在觸發器第一次被保存之後，SQL Server自動地在你的觸發器語句內添加新增的語句。比如在觸發器tr_webusers_insert保存之後，tr_we busers_insert之內的文本變為：

if exists (select * from sysobjects where id=object_id(『dbo.tr_webusers_insert』) and sysstat & 0xf =8)

drop trigger dbo.tr_webusers_insert

GO

CREATE TRIGGER tr_webusers_insert ON dbo.webusers

FOR INSERT

AS

INSERT weblog(activity) VALUES (「New User Added」)

GO

這些添加的語句有什麼用呢？SQL Server為什麼要以這種方式來修改你的語句呢？這些新增的語句添加到你的文本中是為了確保在你的觸發器創建之前自動被刪除。 第一個語句檢查是否該觸發器已經存在。如果該文件已經自動存在，則該語句會自動地刪除它。

你可以使用SQL Enterprise Manager為每一個表創建三個觸發器。在你已經保存了觸發器之後，在將來你可以使用Manager Triggers對話框對該觸發器進行修改。如果沒有SQL Enterprise Manager,這並不是很容易完成的事情。

Posted: 2005/1月/07 9:28上午 | IP記錄

使用Inserted和Deleted表

我們這裡介紹兩個特殊的表，Inserted表和Deleted表。此二表僅僅在觸發器運行時存在。你可以使用該兩個表來精確地 確定觸發觸發器的動作對數據表所做的修改。比如，通過檢查Deleted表，你可以確定那些記錄由某一動作刪除。考慮下面的例子 ：

CREATE TRIGGER tr_webusers_delete ON webusers

FOR DELETE

AS

INSERT weblog (activity) SELECT user_name FROM Deleted

該觸發器自動地創建一個webusers表的記錄。當在webusers表內刪除一個用戶的姓名時，觸發器會自動地把該姓名插入 到weblog表中。假設你一不小心執行了下面的語句：

DELETE webusers

該語句會刪除在webusers表內的所有記錄。一般地，這些記錄會永久地丟失，然而上面的觸發器會在有記錄從webusers 表中刪除時自動的觸發。該觸發器會檢查表Deleted來確定有那些在webusers表內的記錄被刪除，並且把所有刪除了的記 錄拷貝到weblog表中。

為了恢復那些意外被刪除的記錄，你可以使用INSERT和SELECT語句再一次把它們從weblog表中拷貝到webuser s表中。如果你不能允許意外地丟失一條記錄時，你可以使用上面的方法來創建一個表內數據的備份。

Deleted表和有記錄被刪除的表的列結構一模一樣。在前面的例子內，Deleted表具有和webusers表相同的結構。

現在假定你想跟蹤所有插入某一表格的記錄。比如，你想把每一條插入webusers表內的記錄都在weblog表內做備份，你可 以使用下面的觸發器來完成該任務：

CREATE TRIGGER tr_webusers_insert ON webusers

FOR INSERT

AS

INSERT weblog(activty) SELECT user_name FROM INSERTED

該觸發器和前面的觸發器非常相似，除了以下兩點以外：

該觸發器在有記錄插入表格webusers時觸發；該觸發器是FOR INSERT。

該觸發器把記錄從Inserted表拷貝到weblog表內。

Inserted表內包含了所有已經插入到表內的新記錄。假如一個新用戶的姓名插入到webusers表內時，觸發器會自動地把 新的用戶姓名從webusers表拷貝到weblog表內。

當你想使用一個簡單的表來記錄所有發生在你數據庫中一個非常重要的表的動作時，這種複製數據的方法非常有用。你可以使用該表來獲 得對你的數據庫的活動的記錄，並且可以用於記錄和診斷一些可能發生的問題。

你同樣可以使用Inserted表和Deleted表來記錄UPDATE對觸發器所在的表所做的改動。當一個和觸發器相關的表內 的數據被修改時，Deleted表包含了所有列在修改之前的值，而Inserted表包含了所有列在修改之後的值。參看下面的表 12.2，以明確每一個動作是如何影響Deleted和Inserted表的。

表12.2.Inserted和Deleted表的內容

表
 INSERT
 DELETE
 UPDATE
 
Inserted
 插入列
 空
 修改前的列
 
Deleted
 空
 刪除列
 修改後的列
 

Inserted表和Deleted表僅僅在觸發器在執行時存在，這一點是非常重要的，我們一定要明確。它們在某一特定時間和某 一特定表相關。一旦某一個觸發器結束執行時，相應的在兩個表內的數據都會丟失。如果你想創建一個在任意一個這些表內數據的永久拷 貝，你需要在觸發器內把這些表內的數據拷貝到一個永久的表內。

Posted: 2005/1月/07 9:28上午 | IP記錄

結合觸發器和事務處理

觸發器和事務是一個強有力的結合。你可以使用語句ROLLBACK TRANSACTION來取消觸發觸發器的動作。考察下面的觸發器：

CREATE TRIGGER tr_webusers ON webusers FOR INSERT,UPDATE,DELETE AS

IF DATENAME(dw,GETDATE())=」Tuesday」

ROLLBACK TRANSACTION

該觸發器阻止任何人在星期二向表webusers內插入新的記錄，刪除記錄或修改記錄。如果你企圖在星期二向表中添加新記錄，該 動作會被語句ROLLBACK TRANSACTION回轉回去。

假如你目前正為如何阻止某一個特定的人訪問你的網站而困惑。你不想讓這個傢伙能夠在你的網站上註冊。為了做到這一點，你可以創建 一個和表webusers相關的觸發器，該觸發器用於檢查這個傢伙的姓名，如下所示：

CREATE TRIGGER tr_webusers FOR INSERT,DELETE,UPDATE AS

IF EXISTS(SELECT user_name FROM Inserted WHERE user_name=」Andrew Jones」)

ROLLBACK TRANSACTION

該觸發器阻止姓名為Andrew Jones的人添加入表webusers內，當姓名Andrew Jones包含在某個INSERT，DELETE或UPDATE語句內時，該動作就會被語句ROLLBACK TRANSACTION 倒轉回去。

Posted: 2005/1月/07 9:28上午 | IP記錄

使用計劃任務

許多任務需要在某一特定的時間執行。比如，有時候你可能需要每一個小時收到一封有關你的數據表內數據總結信息的電子郵件。或者你 可能需要在每一星期的特定一天清除在數據庫內的舊數據。為了在一個特定的時間執行一個存儲過程，你需要使用計劃任務。

本部分一步步的說明如何創建某一特定任務和計劃該任務，在下面的過程中，你將會學習到如何創建一個任務，該任務會自動的把包含在 你Web服務器log內的數據的總結信息自動地Email給你。

注意

為了使用在該部分內所描述的計劃任務，你需要使用一個郵件服務器，比如Microsoft Exchange Server(參看第四章，」Exchange Active Server,Index Server,和NetShow」)。你同樣需要配置Internet Information Server(IIS)把它的log保存在SQL Server內而不是保存在文本文件內。為了做到該點，你必須在Internet 服務管理器選擇ODBC logging做為當前的log格式(如果你想獲得有關如何配置ODBC logging的信息，請參看Internet服務管理器的在線文檔)。

創建計劃任務的第一個步驟是創建一個你想設定計劃的存儲過程。在本例子中，我們創建一個接收在你的服務器log內不同的IP地址 的數目的存儲過程。該數目可以對你站點的訪問次數做一個粗略的估計。

該存儲過程同樣接收你網站的主頁被訪問的次數，你網站的主頁假定為在你站點的根目錄下面的default.cfml文件。假如你 的主頁是另外一個文件，在下面的存儲過程中，把文件default.cfml替換成你主頁的文件名稱。

該過程同樣假定存儲你web服務器的log的表的名稱為weblog。假如你在配置IIS時使用了另外一個名稱的表，在下面的存 儲過程中做相應的修改。

最後，我們還假定你Email帳號的名稱配置成管理員。你可以使用任何有效的Email地址（比如：someone@somewhere.com）來替換該名稱。你甚至可以把該郵件發送給你的詢呼機，如果你的詢呼服務器支持接受Email的話。

下面就是該存儲過程：

CREATE PROCEDURE get_stats AS

DECLARE @IpCount INT, @HomePageCount INT,

@LogSum VARCHAR(100), @Subj VARCHAR(100)

SELECT @IpCount=COUNT(DISTINCT ClientHost)

FROM weblog WHERE DATEDIFF(hh,LogTime,GETDATE())<1

SELECT @HomePageCount=COUNT(target)

FROM weblog WHERE target=』/default.cfml』

AND DATEDIFF(hh,LogTime,GETDATE())<1

SELECT @LogSum=』Number of visitors:』+CONVERT(VARCHAR(4),@IpCount)

SELECT @LogSum=@LogSum+』… Number of times home

page accessed:』+CONVERT(VARCHAR(4),@HomePageCount)

SELECT @Subj=』Hourly Site Stats』+CONVERT(VARCHAR(20),GETDATE())

EXECUTE master..xp_sendmail

@@recipients=』administrator』,

@@subject=@Subj,

@@massage=@LogSum

RETURN

所有在上面的存儲過程內的語句對你來說一定非常的熟悉。前面的兩個SELECT語句使用聚集函數獲得訪問該網站的不同IP地址的 數目和該網站的主頁被訪問的次數。該信息接收了在一個小時之內的數據，隨後的兩個SELECT語句把所得到的值格式化成可讀的形 式。最後xp_sendmail系統存儲過程把查詢的結果以Email形式發送出去。

你可以使用任何一般的方法來創建該存儲過程，比如ISQL/w或在SQL Enterprise Manager中的管理存儲過程對話框。在繼續下面的過程之前，在ISQL/w中試著執行該存儲過程，如果你的數據庫，電子郵件 ，Internet服務器配置都是正確的話，你就能收到一封Email。

該存儲過程接收在前一個小時內你的Web服務器log的統計數字。假如你定制該過程，使其每個小時自動執行一次，你就能不斷地記 錄所有你網站的活動。

為了計劃該存儲過程，開始SQL Enterprise Manager。選擇你的數據庫，然後在菜單上選擇Server|Scheduled Tasks。這時就會顯示Manage Scheduled Tasks 對話框(見圖12.6)。

點擊新任務按鈕(它看起來像一個具有微弱光芒的時鐘)。這時就會顯示新任務對話框窗口(見圖12.7)。

圖12.6 Scheduled Tasks對話框

圖12.7 新任務對話框

跟隨下面的步驟來填寫在新任務對話框內的信息：

在名稱文本框內，提供一個你的計劃任務的名稱。比如，你可以使用名稱send_stats為該計劃任務命名。

在類型下拉式菜單內，選擇TSQL。這說明你將使用T－SQL語句。

在數據庫下拉菜單內，選擇要存儲你的計劃任務的數據庫。

在命令文本窗口內，輸入語句EXECUTE get_stats來執行你剛剛創建的過程。

在對話框的Schedule部分內，選擇Recurring選項。這將會使你的過程在一定的時間間隔內執行。

在你輸入完所有的上述指定的信息後，點擊Change按鈕。這裡你可以改變你任務的計劃時間表。當你點擊Change按鈕時，這 時就會顯示Task Schedule對話框（見圖12.8）。

圖12.8 Task Schedule對話框

在Task Schedule對話框內，計劃你的任務，使其每小時執行一次。在Occurs部分內，選擇Daily選項。在Daily Frequency部分內，選擇Occurs Every並且指明1Hour(s)。點擊close來關閉Task Schedule對話框。

最後，在Manage Scheduled Tasks對話框內，點擊Add按鈕添加你的計劃任務。此時在對話框內的Task list頁內就會顯示你計劃任務的名稱。你可以在將來的任何時候返回此處來檢查該計劃任務的狀態，觀看該計劃任務最後一次執行的 時間或者看看是否該任務包含錯誤。

你可以使用Manage Scheduled Tasks對話框在你創建一個計劃任務後刪除之。如果你想刪除該計劃任務，只需要點擊Delete Task 按鈕（它看起來像一個有一條對角帶的時鐘）。

你同樣可以使用Manage Scheduled Tasks對話框來觀看你計劃任務的歷史。計劃任務的歷史提供了詳細的關於你計劃任務過去活動的信息。點擊Task History按鈕來觀看該信息。

總結

本章介紹了Microsoft SQL Server幾個非常強有力的特性。你學習了如何使用SQL Server Web 嚮導自動從數據庫表中生成Web頁。你同樣也學習了如何創建存儲過程，觸發器和計劃任務。

到目前為止，你已經學習了如何使用腳本，基本和查詢語言來創建HTML網頁，服務器端腳本和數據庫查詢。在本書的下一部分，你將 進一步深入學習這些知識，學習如何把這些語言集合到Active Server Pages內。這可以使你能夠創建非常先進的網站。