SqlServer觸發器，函數

Create      FUNCTION  InsertComp (@in varchar(30)) 
RETURNS varchar(30)
AS 
BEGIN
declare
              @pos int,
              @count int,
              @result varchar(30),
              @len int
       set @result=@in
       --點前面的位置
       set @pos=CHARINDEX('.',@result)-1
       if (@pos=-1) set @pos=len(@result)
    
       set @count=@pos-3
       while(@count>=1)
              begin
                     set @len=len(@result)-@count
                     set @result=left(@result,@count)+','+substring(@result,@count+1,@len)
                     set @count=@count-3
              end
       return @result
END
 

觸發器:

Sql內、外、左、右串連

在資料庫應用中，經常要涉及從兩個或更多的表中查詢資料，這就需要使用串連查詢。
              其格式如下：
              SELECT COLUMN_NAME，C O L U M N _ N A M E [ , . . . n ]
              FROM TA B L E _ N A M E，TA B L E _ N A M E [ , . . . n ]
              WHERE TABLE_NAME.COLUMN_NAME JOIN_OPERATOR TA B L E _ N A M E . C O L U M N _ N A M E
             上述的串連操作符（J O I N _ O P E R ATO R）可以是：=、>、<、> =、< =、! =、< >、! >、! <、= *、* =。
             在A N S I中，“=”串連應該寫成Inner Join；“* =”串連應該寫成LEFT OUT JOIN；“= *”串連應該寫成“ RIGHT OUT JOIN”。
            在SQL SERVER中，這些寫法都可以用。但是，A N S I規定的寫法是INNER JOIN等標誌。一般而言，在任何串連中，都使用“主鍵=外鍵”的串連查詢條件。
---------------------------------------------------------------------------------

            非限制串連（CROSS JOIN），就是指不帶W H E R E子句的查詢。在數學上，就是表的笛卡爾積。若R表和S表非限制串連，而且R表有X行，S表有Y行，那麼結果集是X * Y行。即：R表的一行對應著S表的所有行。在應用中，非限制串連產生的是無意義結果集，但在資料庫的數學模式上有重要的作用。
-------------------------------------------------------------------------
            自然串連（INNER JOIN）也叫內串連。我們先看下面的例子：

            SELECT PUB_NAME,TITLE
            FROM TITLES,PUBLISHERS
            WHERE TITLES.PUB_ID=PUBLISHERS.PUB_ID
            或寫成：
            SELECT PUB_NAME,TITLE
            FROM TITLES INNER JOIN PUBLISHERS
            ON TITLES.PUB_ID=PUBLISHERS.PUB_ID
            其中，INNER JOIN是SQL Server的預設串連，可簡寫為J O I N。在J O I N後面指定哪些表作串連。O N後面指定了串連的條件。
            以上操作的過程如下：
                 1) 從T I T L E S表中取出一條符合其條件的記錄。
                 2) 掃描P U B L I S H E R S表，分別檢查每條記錄是否在串連屬性上同表T I T L E S取出的記錄相
                 3) 相等就顯示資訊。繼續從表T I T L E S中取下一條記錄，重複步驟2。其實，兩個或多個表要做串連，那麼這些表之間必然存在著主鍵和外鍵的關係。所以，只需要將這些鍵的關係列出，就可以得出表串連的結果。如上例中， P U B _ I D是P U B L I S H E R S表的主鍵， P U B_ I D又是TI T L E S表的外鍵，參照P U B L I S H E R S表中的P U B _ I D .所以，這兩個表的串連條件就是T I T L E S.PUB_ID=PUBLISHERS. PUB_ID。
-----------------------------------------------------------------------
            外串連（OUTER JOIN）允許限制一張表中的行，而不限制另一張表中的行。
            下面舉兩個例子來說明外串連的用法。請比較這兩個例子。
            例顯示所有的書名（無銷售記錄的書也包括在內，“*”在左邊表示不限制左邊表的數
            SELECT TITLES.TITLE_ID,title=convert(char(38),TITLE),QTY
            FROM TITLES,SALES
            WHERE TITLES.TITLE_ID *= SALES.TITLE_ID
            或寫為：
            SELECT TITLES.TITLE_ID,title=convert(char(38),TITLE),QTY
            FROM TITLES LEFT OUTER JOIN SALES
            ON TITLES.TITLE_ID = SALES.TITLE_ID

            外串連的方法有兩種:
            • A *= B 包括第一張表A的所有行在內，而不考慮語句限制（如： A . t i t l e _ i d = B . t i t l e _ i d）。
            相當於LEFT OUT JOIN。
            • A =* B 包括第二張表B的所有行在內，而不考慮語句限制（如： A . t i t l e _ i d = B . t i t l e _ i d）。
            相當於RIGHT OUT JOIN。
            建立外串連規則：
                 1) 外串連顯示外部表格中的所有行，包括與相關表不相配的行在內。
                 2) 外串連只能在兩張表之間進行。
                 3) 不能在內部表上使用IS NULL檢索條件。

Sql server利用完整性條件約束機制防止無效的資料進入資料庫的基表，如果任何DML執行結果破壞完整性條件約束，該語句被復原並返回一上個錯誤。Sql server實現的完整性條件約束完全遵守ANSI X3。135-1989和ISO9075-1989標準。 
 
 用完整性條件約束實施資料完整性規則的優點
利用完整性條件約束實施資料完整性規則有下列優點：  
 
◆定義或更改表時，不需要程式設計，便很容易地編寫程式並可消除程式性錯誤，其功能是由Sql server控制。所以說明性完整性條件約束優於應用代碼和資料庫觸發器。  
 
◆對錶所定義的完整性條件約束是儲存在資料字典中，所以由任何應用進入的資料都必須遵守與表相關聯的完整性條件約束。  
 
◆具有最大的開發能力。當由完整性條件約束所實施的事務規則改變時，管理員只需改變完整性條件約束的定義，所有應用自動地遵守所修改的約束。  
 
◆由於完整性條件約束儲存在資料字典中，資料庫應用可利用這些資訊，在SQL語句執行之前或由Sql server檢查之前，就可立即反饋資訊。  
 
◆由於完整性條件約束說明的語義是清楚地定義，對於每一指定說明規則可實現效能最佳化。 
 
◆由於完整性條件約束可臨時地使不能，以致在裝入大量資料時可避免約束檢索的開銷。當資料庫裝入完成時，完整性條件約束可容易地使其能，任何破壞完整性條件約束的任何新行在例外表中列出。 
 
Sql server的DBA和應用開始者對列的值輸入可使用的完整性條件約束有下列類型：  
 
◆NOT NULL約束:如果在表的一列的值不允許為空白，則需在該列指定NOT NULL約束。 
 
◆UNIQUE碼約束：在表指定的列或組列上不允許兩行是具有重複值時，則需要該列或組列上指定UNIQUE碼完整性條件約束。在UNIQUE碼約束定義中的列或組列稱為唯一碼。所有唯一完整性條件約束是用索引方法實施。 
 
◆PRIMARY KEY約束：在資料庫中每一個表可有一個PRIMARY KEY約束。包含在PRIMARY KEY完整性條件約束的列或組列稱為主碼，每個表可有一個主碼。Sql server使用索引實施PRIMARY KEY約束。 
 
◆FOREIGN KEY約束（可稱參考條件約束）：在關聯式資料庫中表可通過公用列相關聯，該 規則控制必須維護的列之間的關係。包含在參考完整性約束定義的列或組列稱為外來碼。由外來碼所引用的表中的唯一碼或方碼，稱為引用碼。包含有外來碼的表稱為子表或從屬表。由子表的外來碼所引用的表稱為雙親表或參考資料表。如果對錶的每一行，其外來碼的值必須與主碼中一值相匹配，則需指定參考完整性約束。 
 
◆CHECK約束：表的每行對一指定的條件必須是TRUE或未知，則需在一列或列組上指定CHECK完整性條件約束。如果在發出一個DML語句時，CHECK約束的條件計算得FALSE時，該語句被復原

SQL Server 2000中的觸發器使用

觸發器是資料庫應用中的重用工具，它的應用很廣泛。這幾天寫一個化學資料統計方面的軟體，需要根據採樣，自動計算方差，在這裡，我使用了觸發器。

　　下面我摘錄了SQL Server官方教程中的一段關於觸發器的文字，確實有用的一點文字描述。

　　可以定義一個無論何時用INSERT語句向表中插入資料時都會執行的觸發器。

　　當觸發INSERT觸發器時，新的資料行就會被插入到觸發器表和inserted表中。inserted表是一個邏輯表，它包含了已經插入的資料行的一個副本。inserted表包含了INSERT語句中已記錄的插入動作。inserted表還允許引用由初始化INSERT語句而產生的日誌資料。觸發器通過檢查inserted表來確定是否執行觸發器動作或如何執行它。inserted表中的行總是觸發器表中一行或多行的副本。

　　日誌記錄了所有修改資料的動作(INSERT、UPDATE和DELETE語句)，但在交易記錄中的資訊是不可讀的。然而，inserted表允許你引用由INSERT語句引起的日誌變化，這樣就可以將插入資料與發生的變化進行比較，來驗證它們或採取進一步的動作。也可以直接引用插入的資料，而不必將它們儲存到變數中。

　　樣本

　　在本例中，將建立一個觸發器。無論何時訂購產品（無論何時向Order Details表中插入一條記錄），這個觸發器都將更新Products表中的一列(UnitsInStock)。用原來的值減去訂購的數量值即為新值。
USE Northwind
CREATE TRIGGER OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = P.UnitsInStock – I.Quantity
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID

　　DELETE觸發器的工作過程

　　當觸發DELETE觸發器後，從受影響的表中刪除的行將被放置到一個特殊的deleted表中。deleted表是一個邏輯表，它保留已被刪除資料行的一個副本。deleted表還允許引用由初始化DELETE語句產生的日誌資料。

　　使用DELETE觸發器時，需要考慮以下的事項和原則：

　　•當某行被添加到deleted表中時，它就不再存在於資料庫表中；因此，deleted表和資料庫表沒有相同的行。

　　•建立deleted表時，空間是從記憶體中分配的。deleted表總是被儲存在快取中。

　　•為DELETE動作定義的觸發器並不執行TRUNCATE TABLE語句，原因在於日誌不記錄TRUNCATE TABLE語句。

　　樣本

　　在本例中，將建立一個觸發器，無論何時刪除一個產品類別（即從Categories表中刪除一條記錄），該觸發器都會更新Products表中的Discontinued列。所有受影響的產品都標記為1，標示不再使用這些產品了。
USE Northwind
CREATE TRIGGER Category_Delete
ON Categories
FOR DELETE
AS
UPDATE P SET Discontinued = 1
FROM Products AS P INNER JOIN deleted AS d
ON P.CategoryID = d.CategoryID

　　UPDATE觸發器的工作過程

　　可將UPDATE語句看成兩步操作：即捕獲資料前像(before image)的DELETE語句，和捕獲資料後像(after image)的INSERT語句。當在定義有觸發器的表上執行UPDATE語句時，原始行（前像）被移入到deleted表，更新行（後像）被移入到inserted表。

　　觸發器檢查deleted表和inserted表以及被更新的表，來確定是否更新了多行以及如何執行觸發器動作。

　　可以使用IF UPDATE語句定義一個監視指定列的資料更新的觸發器。這樣，就可以讓觸發器容易的隔離出特定列的活動。當它檢測到指定列已經更新時，觸發器就會進一步執行適當的動作，例如發出錯誤資訊指出該列不能更新，或者根據新的更新的列值執行一系列的動作語句。

　　文法
IF UPDATE (<column_name>)

　　例1

　　本例阻止使用者修改Employees表中的EmployeeID列。
USE Northwind
GO
CREATE TRIGGER Employee_Update
ON Employees
FOR UPDATE
AS
IF UPDATE (EmployeeID)
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Employee ID number cannot be modified.', 10, 1)
ROLLBACK TRANSACTION
END

　　INSTEAD OF觸發器的工作過程

　　可以在表或視圖上指定INSTEAD OF觸發器。執行這種觸發器就能夠替代原始的觸發動作。INSTEAD OF觸發器擴充了視圖更新的類型。對於每一種觸發動作(INSERT、UPDATE或 DELETE)，每一個表或視圖只能有一個INSTEAD OF觸發器。

　　INSTEAD OF觸發器被用於更新那些沒有辦法通過正常方式更新的視圖。例如，通常不能在一個基於串連的視圖上進行DELETE操作。然而，可以編寫一個INSTEAD OF DELETE觸發器來實現刪除。上述觸發器可以訪問那些如果視圖是一個真正的表時已經被刪除的資料行。將被刪除的行儲存在一個名為deleted的工作表中，就像AFTER觸發器一樣。相似地，在UPDATE INSTEAD OF觸發器或者INSERT INSTEAD OF觸發器中，你可以訪問inserted表中的新行。
不能在帶有WITH CHECK OPTION定義的視圖中建立INSTEAD OF觸發器

SQL Server 兩個觸發器例子
--當在“出貨表_明細”增、刪、改時，相應地在“訂單表_明細”對已交貨數量和未交貨數量作出修改。
Create trigger tr_出貨表_明細
on 出貨表_明細
For DELETE,INSERT,UPDATE
as
Declare @JiaoHuo varchar(20)
Begin
 Set @JiaoHuo = (Select Sum(交貨數量) from 出貨表_明細 Group by 序號 Having 序號 = (Select 序號 From Deleted))
 Set @JiaoHuo = ISNULL(@JiaoHuo,(Select Sum(交貨數量) from 出貨表_明細 Group by 序號 Having 序號 = (Select 序號 From Inserted)))
        Set @JiaoHuo = ISNULL(@JiaoHuo,0)
 UPDATE N SET 已交貨數量 = @JiaoHuo, 未交貨數量 = N.訂購數量 - @JiaoHuo
                From 訂單表_明細 AS N INNER Join Deleted AS D
      ON N.序號 = D.序號
End

--當“訂單表_明細”中增、刪、改時、判斷該筆訂單的未交貨數量合計是否為0，修改“訂單表”的訂單狀態為“已完成”，否則為“未完成”
Create trigger tr_Status
ON 訂單表_明細
FOR INSERT,UPDATE,DELETE
AS
Declare @Order varchar(20)
 Begin
         Set @Order = (Select 訂單號 from Deleted)
         Set @Order = ISNULL(@Order,(Select 訂單號 from Inserted))
         IF(Select Sum(未交貨數量) From 訂單表_明細 Group by 訂單號 Having 訂單號 = @Order)= 0
              Begin
                   UPDATE 訂單表 Set 訂單狀態 = '已完成',完成日期 = GetDate() Where 訂單號 = @Order
              End
         Else
              Begin 
                   UPDATE 訂單表 Set 訂單狀態 = '未完成',完成日期 = NUll Where 訂單號 = @Order
              End
 End

查詢語言（SQL）函數
SQL 函數執行一個任意 SQL 查詢的列表，返回列表裡最後一個查詢的結果。 它必須是一條 SELECT．在比較簡單的情況下(非集合的情況)， 返回最後一條查詢結果的第一行．(請記住多行結果的"第一行" 是不明確的，除非你用 ORDER BY 對結果排序．) 如果最後一個查詢碰巧不返回行，那麼返回 NULL．
另外，一個 SQL 函數可以聲明為返回一個集合，方法是把該函數的 傳回型別聲明為 SETOF sometype． 這個時候最後一條查詢結果的所有行都會被返回．更多的細節在下面講．
SQL 函數的函數體應該是一個用分號分隔的一條或多條 SQL 陳述式的列表． 請注意，因為 CREATE FUNCTION 命令的文法要求 函數體要封閉在單引號裡面，所以在函數體中使用的單引號 (') 必須逃逸，方法是寫兩個單引號(') 或者 在需要逃逸的單引號之前放一個反斜扛 (\')．
SQL 函數的參數在查詢裡可以用 $n 文法引用： $1指第一個參數，$2 指第二個參數，以此類推。 如果參數是 複合類型，那麼可以用點標記法， 例如，"$1.emp"，訪問參數裡的欄位。
12.2.1. 例子
看看下面這個簡單的 SQL 函數的例子， 它將用於對一個銀行帳號做扣款（借記消費 debit）動作：
CREATE FUNCTION tp1 (integer, numeric) RETURNS integer AS '
    UPDATE bank
        SET balance = balance - $2
        WHERE acctountno = $1;
        SELECT 1;'
LANGUAGE 'sql';
    
一個使用者可以象下面這樣用這個函數給帳戶 17 扣款 $100.00：
SELECT tp1( 17,100.0);
    
實際上我們可能喜歡函數有一個比常量 "1" 更有用一些的結果． 所以更有可能的定義是
CREATE FUNCTION tp1 (integer, numeric) RETURNS numeric AS '
    UPDATE bank
        SET balance = balance - $2
        WHERE accountno = $1;
    SELECT balance FROM bank WHERE accountno = $1;
' LANGUAGE SQL;
它修改餘額並返回新的餘額．
SQL 裡面的任何命令集都可以打成一個包， 做成一個函數．這些命令可以包含資料修改(也就是說， INSERT，UPDATE， 和DELETE)以及 SELECT 查詢． 不過，最後的命令必須是一條返回函式宣告的傳回型別的 SELECT．
CREATE FUNCTION clean_EMP () RETURNS integer AS '
    DELETE FROM EMP
        WHERE EMP.salary <= 0;
    SELECT 1 AS ignore_this;
' LANGUAGE SQL;

SELECT clean_EMP();
 x
---
 1
12.2.2. 基本類型的 SQL 函數
最簡單的 SQL 函數可能是不帶參數，只是返回一個基本類型如 integer 的函數：
CREATE FUNCTION one()
    RETURNS integer
    AS 'SELECT 1 as RESULT;'
    LANGUAGE 'sql';

SELECT one();
 one
-----
   1
 
注意我們給函數定義了目標列（名稱為 RESULT）， 但是啟用函數的查詢語句的目標列覆蓋了函數的目標 列．因此，結果的標記是one 而不是RESULT．
定義以基本類型為參數的 SQL 函數幾乎一樣簡單， 注意我們在函數內如何用$1和$2使用參數：
CREATE FUNCTION add_em(integer, integer)
    RETURNS integer
    AS 'SELECT $1 + $2;'
    LANGUAGE 'sql';

SELECT add_em(1, 2) AS answer;

+-------+
|answer |
+-------+
|3      |
+-------+
    
12.2.3. 複合類型的SQL函數
當我們聲明的函數用複合類型做參數時， 我們不僅要聲明我們需要哪個參數（像上面我們使用 $1和$2一樣），而且要聲明參數的欄位．比如， 假設 EMP 是一個包含僱員資訊的表，並且因此也是該表每行 的複合類型的名字．這裡就是一個函數 double_salary，它計算你薪水翻番之後的數值：
CREATE FUNCTION double_salary(EMP) RETURNS integer AS '
    SELECT $1.salary * 2 AS salary;
'LANGUAGE SQL;

SELECT name, double_salary(EMP) AS dream
    FROM EMP
    WHERE EMP.cubicle ~= point '(2,1)';
 name | dream
------+-------
 Sam  |  2400
請注意這裡使用 $1.salary 的文法 選擇參數行數值的一個欄位．還要注意SELECT命令是如何 使用一個表的名字表示該表的整個當前行作為複合數值．
我們也可以寫一個返回複合類型的函數．(不過，我們下面將看到， 在這些函數的使用上有一些不幸的限制．) 下面是一個返回一行 EMP 函數的例子∶
CREATE FUNCTION new_emp() RETURNS EMP AS '
    SELECT text ''None'' AS name,
        1000 AS salary,
        25 AS age,
        point ''(2,2)'' AS cubicle'
    LANGUAGE 'sql';
在這個例子中我們給每個欄位都賦予了一個常量， 當然我們可以用任何計算或運算式來代替這些常量． 注意定義這樣的函數的兩個重要的問題∶
• 目標列表的順序必須和與該複合類型相關的表中欄位的順序完全一樣．
• 你必須對錶達式進行類型轉換以匹配複合類型的定義． 否則你將看到下面的錯誤資訊：
ERROR:  function declared to return emp returns varchar instead of text at column 1
在目前的 PostgreSQL 版本裡有一些讓人 不快的限制，這些限制約束了我們可以使用複合類型傳回值的方法． 簡單說，在調用一個返回一行的函數的時候，我們無法檢索整行． 我們要麼是把某個欄位對應出該行，要麼是把整行傳遞給另外一個函數． (試圖顯示整行數值將產生沒有意義的數字．)比如，
SELECT name(new_emp());
 name
------
 None
這個例子使用了函數概念進列欄位抽取．解釋這些問題的簡單方法是 我們通常互動使用attribute(table)和 table.attribute 的概念∶
--
-- 下面的與這句話相同∶
--  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
    FROM EMP
    WHERE age(EMP) < 30;
 youngster
-----------
 Sam
通常，我們必須使用函數文法映射作為函數傳回值的欄位的原因 是在和函數調用接合在一起時，分析器不能理解用於映射的點分文法．
SELECT new_emp().name AS nobody;
ERROR:  parser: parse error at or near "."
另外一個使用函數返回行結果的方法是聲明另外一個函數， 該函數接受一個行型別參數，然後把函數結果傳遞給這個第二個函數∶
CREATE FUNCTION getname(emp) RETURNS text AS
'SELECT $1.name;'
LANGUAGE SQL;
SELECT getname(new_emp());
 getname
---------
 None
(1 row)
12.2.4. 返回集合的 SQL 函數
如前所述，一個 SQL 函數可以聲明為返回 SETOF sometype． 這時候，該函數的最後的SELECT查詢一直執行到結束，並且它 輸出的每行都當做該集合的一個元素返回．
返回集合的函數只能在一個SELECT查詢的目標列表中調用． 對於SELECT自己產生的每個行都會調用這個返回集合的函數， 並且為該函數的結果集的每個元素產生一個輸出行．比如∶
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
'SELECT name FROM nodes WHERE parent = $1'
LANGUAGE SQL;
SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)
在最後的SELECT裡，請注意沒有出現Child2， Child3等的行． 這是因為listchildren 為這些輸入返回一個空集合， 因此不產生任何輸出行．