觸發器、預存程序和函數三者有何區別？)

觸發器是特殊的預存程序，預存程序需要程式調用，而觸發器會自動執行；你所說的函數是自訂函數吧，函數是根據輸入產生輸出，自訂只不過輸入輸出的關係由使用者來定義。在什麼時候用觸發器？要求系統根據某些操作自動完成相關任務，比如，根據買掉的產品的輸入數量自動扣除該產品的庫存量。什麼時候用預存程序？預存程序就是程式，它是經過語法檢查和編譯的SQL語句，所以運行特別快。

 

預存程序和使用者自訂函數具體的區別

先看定義：

預存程序

預存程序可以使得對資料庫的管理、以及顯示關於資料庫及其使用者資訊的工作容易得多。預存程序是 SQL 陳述式和可選控制流程語句的先行編譯集合，以一個名稱儲存並作為一個單元處理。預存程序儲存在資料庫內，可由應用程式通過一個調用執行，而且允許使用者聲明變數、有條件執行以及其它強大的編程功能。

預存程序可包含程式流、邏輯以及對資料庫的查詢。它們可以接受參數、輸出參數、返回單個或多個結果集以及傳回值。

可以出於任何使用 SQL 陳述式的目的來使用預存程序，它具有以下優點：

• 可以在單個預存程序中執行一系列 SQL 陳述式。

• 可以從自己的預存程序內引用其它預存程序，這可以簡化一系列複雜語句。
• 預存程序在建立時即在伺服器上進行編譯，所以執行起來比單個 SQL 陳述式快。

使用者定義函數

函數是由一個或多個 Transact-SQL 陳述式組成的子程式，可用於封裝代碼以便重新使用。Microsoft? SQL Server? 2000 並不將使用者限制在定義為 Transact-SQL 語言一部分的內建函數上，而是允許使用者建立自己的使用者定義函數。

可使用 CREATE FUNCTION 語句建立、使用 ALTER FUNCTION 語句修改、以及使用 DROP FUNCTION 語句除去使用者定義函數。每個完全合法的使用者定義函數名 (database_name.owner_name.function_name) 必須唯一。

必須被授予 CREATE FUNCTION 許可權才能建立、修改或除去使用者定義函數。不是所有者的使用者在 Transact-SQL 陳述式中使用某個函數之前，必須先給此使用者授予該函數的適當許可權。若要建立或更改在 CHECK 條件約束、DEFAULT 子句或計算資料行定義中引用使用者定義函數的表，還必須具有函數的 REFERENCES 許可權。

在函數中，區別處理導致刪除語句並且繼續在諸如觸發器或預存程序等模式中的下一語句的 Transact-SQL 錯誤。在函數中，上述錯誤會導致停止執行函數。接下來該操作導致停止喚醒調用該函數的語句。

使用者定義函數的類型

SQL Server 2000 支援三種使用者定義函數：

• 純量涵式

• 內嵌資料表值函式
• 多語句資料表值函式

使用者定義函數採用零個或更多的輸入參數並返回標量值或表。函數最多可以有 1024 個輸入參數。當函數的參數有預設值時，調用該函數時必須指定預設 DEFAULT 關鍵字才能擷取預設值。該行為不同於在預存程序中含有預設值的參數，而在這些預存程序中省略該函數也意味著省略預設值。使用者定義函數不支援輸出參數。

純量涵式返回在 RETURNS 子句中定義的類型的單個資料值。可以使用所有純量資料型別，包括 bigint 和 sql_variant。不支援 timestamp 資料類型、使用者定義資料類型和非標量類型（如 table 或 cursor）。在 BEGIN...END 塊中定義的函數主體包含返回該值的 Transact-SQL 陳述式系列。傳回型別可以是除 text、ntext、image、cursor 和 timestamp 之外的任何資料類型。

資料表值函式返回 table。對於內嵌資料表值函式，沒有函數主體；表是單個 SELECT 語句的結果集。對於多語句資料表值函式，在 BEGIN...END 塊中定義的函數主體包含 TRANSACT-SQL 陳述式，這些語句可產生行並將行插入將返回的表中。有關內嵌資料表值函式的更多資訊，請參見內嵌使用者定義函數。有關資料表值函式的更多資訊，請參見返回 table 資料類型的使用者定義函數。

BEGIN...END 塊中的語句不能有任何副作用。函數副作用是指對具有函數外範圍（例如資料庫表的修改）的資源狀態的任何永久性更改。函數中的語句唯一能做的更改是對函數上的局部對象（如局部遊標或局部變數）的更改。不能在函數中執行的操作包括：對資料庫表的修改，對不在函數上的局部遊標進行操作，寄送電子郵件，嘗試修改目錄，以及產生返回至使用者的結果集。

函數中的有效語句類型包括：

• DECLARE 語句，該語句可用於定義函數局部的資料變數和遊標。

• 為函數局部對象賦值，如使用 SET 給標量和表局部變數賦值。
• 遊標操作，該操作引用在函數中聲明、開啟、關閉和釋放的局部遊標。不允許使用 FETCH 語句將資料返回到用戶端。僅允許使用 FETCH 語句通過 INTO 子句給局部變數賦值。
• 控制流程語句。
• SELECT 語句，該語句包含帶有運算式的挑選清單，其中的運算式將值賦予函數的局部變數。
• INSERT、UPDATE 和 DELETE 語句，這些語句修改函數的局部 table 變數。
• EXECUTE 語句，該語句調用擴充預存程序。

在查詢中指定的函數的實際執行次數在最佳化器產生的執行計畫間可能不同。樣本為 WHERE 子句中的子查詢喚醒調用的函數。子查詢及其函數執行的次數會因最佳化器選擇的訪問路徑而異。

使用者定義函數中不允許使用會對每個調用返回不同資料的內建函數。使用者定義函數中不允許使用以下內建函數：

@@CONNECTIONS	@@PACK_SENT	GETDATE
@@CPU_BUSY	@@PACKET_ERRORS	GetUTCDate
@@IDLE	@@TIMETICKS	NEWID
@@IO_BUSY	@@TOTAL_ERRORS	RAND
@@MAX_CONNECTIONS	@@TOTAL_READ	TEXTPTR
@@PACK_RECEIVED	@@TOTAL_WRITE

架構綁定函數

CREATE FUNCTION 支援 SCHEMABINDING 子句，後者可將函數綁定到它引用的任何對象（如表、視圖和其它使用者定義函數）的架構。嘗試對架構綁定函數所引用的任何對象執行 ALTER 或 DROP 都將失敗。

必須滿足以下條件才能在 CREATE FUNCTION 中指定 SCHEMABINDING：

• 該函數所引用的所有視圖和使用者定義函數必須是綁定到架構的。

• 該函數所引用的所有對象必須與函數位於同一資料庫中。必須使用由一部分或兩部分構成的名稱來引用對象。
• 必須具有對該函數中引用的所有對象（表、視圖和使用者定義函數）的 REFERENCES 許可權。

可使用 ALTER FUNCTION 刪除架構綁定。ALTER FUNCTION 語句將通過不帶 WITH SCHEMABINDING 指定函數來重新定義函數。

調用使用者定義函數

當調用標量使用者定義函數時，必須提供至少由兩部分組成的名稱：

SELECT *, MyUser.MyScalarFunction()FROM MyTable

可以使用一個部分構成的名稱調用資料表值函式：

SELECT *FROM MyTableFunction()

然而，當調用返回表的 SQL Server 內建函數時，必須將首碼 :: 添加至函數名：

SELECT * FROM ::fn_helpcollations()

可在 Transact-SQL 陳述式中所允許的函數返回的相同資料類型運算式所在的任何位置引用純量涵式，包括計算資料行和 CHECK 條件約束定義。例如，下面的語句建立一個返回 decimal 的簡單函數：

CREATE FUNCTION CubicVolume-- Input dimensions in centimeters   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),    @CubeHeight decimal(4,1) )RETURNS decimal(12,3) -- Cubic Centimeters.ASBEGIN   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )END

然後可以在允許整型運算式的任何地方（如表的計算資料行中）使用該函數：

CREATE TABLE Bricks   (    BrickPartNmbr   int PRIMARY KEY,    BrickColor      nchar(20),    BrickHeight     decimal(4,1),    BrickLength     decimal(4,1),    BrickWidth      decimal(4,1),    BrickVolume AS              (               dbo.CubicVolume(BrickHeight,                         BrickLength, BrickWidth)              )   )

dbo.CubicVolume 是返回標量值的使用者定義函數的一個樣本。RETURNS 子句定義由該函數返回的值的純量資料型別。BEGIN...END 塊包含一個或多個執行該函數的 Transact-SQL 陳述式。該函數中的每個 RETURN 語句都必須具有一個參數，可返回具有在 RETURNS 子句中指定的資料類型（或可隱性轉換為 RETURNS 中指定類型的資料類型）的資料值。RETURN 參數的值是該函數返回的值。