預存程序Stored Procedure & 觸發程序Trigger Procedure範例

---------------------------------------------------------------------- 預存程序Stored Procedure
-- ===============================================
-- 名稱：[預存程序_值回TABLE]
-- 說明：
-- 參數：char(15), int
-- 擲回：TABLE
-- 範例：EXEC 預存程序_值回TABLE '變數1', 變數2
-- ===============================================
CREATE PROCEDURE [dbo].[預存程序] @變數1 char(15), @變數2 int
AS
BEGIN
 -- 宣告TABLE
 DECLARE @新資料表 TABLE(欄位名稱1 char(15), 欄位名稱2 int)

 -- 處理程序
 INSERT INTO ....
 UPDATE ....
 DELETE ....

 -- 值回TABLE
 SELECT * FROM @新資料表
 -- OR 值回實體TABLE
 SELECT * FROM 資料表
END
-- ===============================================
-- 名稱：[預存程序]
-- 說明：使用指標回圈來將資料一筆一筆取出處理
-- 參數：char(15), int
-- 擲回：TABLE
-- 範例：EXEC 預存程序 '變數1', 變數2
-- ===============================================
CREATE PROCEDURE [dbo].[預存程序] @變數1 char(15), @變數2 int
AS
BEGIN
 -- 處理程序
 INSERT INTO ....
 UPDATE ....
 DELETE ....

 -- 宣告 指標
 DECLARE 指標名稱 CURSOR FOR
  SELECT 欄位1, 欄位2
  FROM 資料表
  WHERE 欄位1 = '@變數1' AND 欄位2 = @變數2

 -- 開啟指標
 OPEN 指標名稱

 -- 宣告接收指標內的資料變數
 DECLARE @變數3 INT
 DECLARE @變數4 CHAR(20)

 -- 迴圈列出資料(接收指標內的數量必須與SELECT出來的欄位數量型態相同)
 FETCH NEXT FROM 指標名稱 INTO @變數3, @變數4
 WHILE @@FETCH_STATUS = 0
 BEGIN

  -- 可中途離開回圈
  IF TRUE BEGIN BREAK END

  -- 在迴圈內移動到下一筆資料
  FETCH NEXT FROM 指標名稱 INTO @變數3, @變數4
 END

 -- 關閉指標
 CLOSE 指標名稱

 -- 釋放指標
 DEALLOCATE 指標名稱
END

-- ===============================================
-- 名稱：[純量值函數]
-- 說明：
-- 參數：char(15), int
-- 擲回：INT
-- 範例：SELECT 函數名稱 @變數1 char(15), @變數2 int
-- ===============================================
CREATE FUNCTION [dbo].[函數名稱](@變數1 char(15),@變數2 int)
RETURNS INT
AS
BEGIN
-- 需要指定擲回變數
RETURN @變數2
END
-- ===============================================
-- 名稱：[資料表值函數]
-- 說明：
-- 參數：char(15), int
-- 擲回：TABLE
-- 範例：SELECT 資料表值函數 @變數1 char(15), @變數2 int
-- ===============================================
CREATE FUNCTION [dbo].[資料表值函數](@變數1 char(15),@變數2 int)
RETURNS @新資料表 TABLE(欄位名稱1 char(15), 欄位名稱2 int)
AS
BEGIN
-- 處理
INSERT INTO @新資料表(欄位名稱1, 欄位名稱2) VALUE('', 0)
UPDATE @新資料表 SET 欄位名稱1 = '' 欄位名稱2 = 0 WHERE 欄位名稱1 = ''
DELETE FROM @新資料表 WHERE 欄位名稱1 = ''

-- 擲回 不用指定值回變數
RETURN
END
---------------------------------------------------------------------- 預存程序Stored Procedure
------------------------------------------------------------------------------------------------- 這是分隔線
---------------------------------------------------------------------- 觸發程序Trigger Procedure
-- ===============================================
-- 名稱：[觸發程序]
-- 說明：當 [dbo].[資料表] 觸發 INSERT, UPDATE, DELETE 時處理該程序
-- 事件：INSERT, UPDATE, DELETE
-- ===============================================
CREATE TRIGGER [dbo].[觸發程序]
   ON  [dbo].[資料表]
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
 DECLARE @變數1 varchar(20)

 -- 觸發 INSERT 的條件
 IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) = 0
 BEGIN
  SELECT @變數1 = @欄位 FROM INSERTED
 END

 -- 觸發 DELETE 的條件
 IF (SELECT COUNT(*) FROM INSERTED) = 0 AND (SELECT COUNT(*) FROM DELETED) > 0
 BEGIN
  SELECT @變數1 = @欄位 FROM DELETED
 END

 -- 觸發 UPDATE 的條件
 IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT(*) FROM DELETED) > 0
 BEGIN
 -- 變更前資料
 SELECT @變數1 = @欄位 FROM DELETED

 -- 變更後資料
 SELECT @變數1 = @欄位 FROM INSERTED
 END
END

-- ===============================================
-- 名稱：[觸發程序_INSERT]
-- 說明：當 [dbo].[資料表] 觸發 INSERT, UPDATE, DELETE 時處理該程序
-- 事件：INSERT
-- ===============================================
CREATE TRIGGER [dbo].[觸發程序_INSERT]
   ON  [dbo].[資料表]
   AFTER INSERT
AS
BEGIN
 DECLARE @變數1 varchar(20)
 SELECT @變數1 = @欄位 FROM INSERTED
END
---------------------------------------------------------------------- 觸發程序Trigger Procedure