2010年9月28日 星期二
SQL SERVER Trigger +cursor 及參考資料
SQL SERVER Trigger裡使用CURSOR查詢資料
參考URL: http://www.dotblogs.com.tw/jain/archive/2010/04/21/14728.aspx
如何判斷TRIGGER目前是INSERT、UPDATE、DELETE的狀態:
http://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10553.aspx
在master table及dtl table的條件下，又要加上放行機制(在下範例*_LOG為放行table)，如果在MASTER_LOG需要觸發到MASTER TABLE及DTL的所有相關TABLE一併更新及新增，可用下列的範例去達成。
1．建立TABLE:
1: USE [DB]
2: GO
3: 4: /****** Object: Table [dbo].[TB_CBRP_EXPT] Script Date: 09/29/2010 17:24:46 ******/
5: SET ANSI_NULLS ON
6: GO
7: 8: SET QUOTED_IDENTIFIER ON
9: GO
10: 11: --建立主檔 12: 13: CREATE TABLE [dbo].[TB_CBRP_EXPT](
14: [CASE_ID] [nvarchar](12) NOT NULL,
15: [CASE_TYPE] [nvarchar](1) NULL,
16: [CASE_GROUP] [nvarchar](1) NULL,
17: [CARD_NO] [nvarchar](19) NULL,
18: [BRANCH_ID] [nvarchar](5) NULL,
19: [CONFIRM_ID] [nvarchar](12) NULL,
20: [CONFIRM_DATETIME] [datetime] NULL,
21: [CREATE_ID] [nvarchar](12) NULL,
22: [CREATE_DATETIME] [datetime] NULL,
23: [UPDATE_ID] [nvarchar](12) NULL,
24: [UPDATE_DATETIME] [datetime] NULL,
25: CONSTRAINT [PK_CBRP_EXPT] PRIMARY KEY CLUSTERED ([CASE_ID] ASC)
26: ) ON [PRIMARY]
27: 28: --建立主檔放行表 29: 30: /****** Object: Table [dbo].[TB_CBRP_EXPT_LOG] Script Date: 09/29/2010 17:26:22 ******/
31: CREATE TABLE [dbo].[TB_CBRP_EXPT_LOG](
32: [CASE_ID] [nvarchar](12) NOT NULL,
33: [CASE_TYPE] [nvarchar](1) NULL,
34: [CASE_GROUP] [nvarchar](1) NULL,
35: [CARD_NO] [nvarchar](19) NULL,
36: [BRANCH_ID] [nvarchar](5) NULL,
37: [LOG_ID] [int] NOT NULL,
38: [IS_CONFIRM] [bit] NOT NULL,
39: [CONFIRM_TYPE] [nvarchar](1) NULL,
40: [CONFIRM_ID] [nvarchar](12) NULL,
41: [CONFIRM_DATETIME] [datetime] NULL,
42: [CREATE_ID] [nvarchar](12) NULL,
43: [CREATE_DATETIME] [datetime] NULL,
44: [UPDATE_ID] [nvarchar](12) NULL,
45: [UPDATE_DATETIME] [datetime] NULL,
46: CONSTRAINT [PK_CBRP_EXPT_LOG] PRIMARY KEY CLUSTERED ([CASE_ID] ASC,[LOG_ID] ASC)
47: ) ON [PRIMARY]
48: 49: 50: --建立明細主檔 51: 52: /****** Object: Table [dbo].[TB_CBRP_EXPT_DTL] Script Date: 09/29/2010 17:27:30 ******/
53: CREATE TABLE [dbo].[TB_CBRP_EXPT_DTL](
54: [CASE_ID] [nvarchar](12) NOT NULL,
55: [CASE_SEQ] [numeric](3, 0) NOT NULL,
56: [MERCH_ID] [nvarchar](15) NULL,
57: [CASE_STATUS] [nvarchar](2) NULL,
58: [SETL_FLAG] [nvarchar](1) NULL,
59: [CONFIRM_ID] [nvarchar](12) NULL,
60: [CONFIRM_DATETIME] [datetime] NULL,
61: [CREATE_ID] [nvarchar](12) NULL,
62: [CREATE_DATETIME] [datetime] NULL,
63: [UPDATE_ID] [nvarchar](12) NULL,
64: [UPDATE_DATETIME] [datetime] NULL,
65: CONSTRAINT [PK_CBRP_EXPT_DTL] PRIMARY KEY CLUSTERED ([CASE_ID] ASC,[CASE_SEQ] ASC)
66: ) ON [PRIMARY]
67: 68: --建立明細放行表 69: 70: /****** Object: Table [dbo].[TB_CBRP_EXPT_DTL_LOG] Script Date: 09/29/2010 17:28:01 ******/
71: CREATE TABLE [dbo].[TB_CBRP_EXPT_DTL_LOG](
72: [CASE_ID] [nvarchar](12) NOT NULL,
73: [CASE_SEQ] [numeric](3, 0) NOT NULL,
74: [MERCH_ID] [nvarchar](15) NULL,
75: [CASE_STATUS] [nvarchar](2) NULL,
76: [SETL_FLAG] [nvarchar](1) NULL,
77: [LOG_ID] [int] NOT NULL,
78: [IS_CONFIRM] [bit] NULL,
79: [CONFIRM_TYPE] [nvarchar](1) NULL,
80: [CONFIRM_ID] [nvarchar](12) NULL,
81: [CONFIRM_DATETIME] [datetime] NULL,
82: [CREATE_ID] [nvarchar](12) NULL,
83: [CREATE_DATETIME] [datetime] NULL,
84: [UPDATE_ID] [nvarchar](12) NULL,
85: [UPDATE_DATETIME] [datetime] NULL,
86: CONSTRAINT [PK_CBRP_EXPT_DTL_LOG] PRIMARY KEY CLUSTERED ([CASE_ID] ASC,[CASE_SEQ] ASC,[LOG_ID] ASC)
87: ) ON [PRIMARY]
88: 89: GO
2．以下是我所建立的trigger
所執行的目的是
1．MASTER_LOG放行表如果異動了IS_CONFIRM欄位為1(允許)時，要同時觸發新增一筆資料至MASTER主檔，及將DTL_LOG明細放行表裡該筆交易也UPDATE IS_CONFIRM欄位視為放行，並將DTL_LOG放行內容，INSERT記錄至DTL_TABLE。
等於可以把放行的行為一次到位。但因為我作的範例綁定了欄位名稱，這樣作有好處是效能較快一點，但不夠彈性！
1: ALTER TRIGGER [dbo].[TRI_UPDATE] --Trigger名稱
2: ON [dbo].TB_CBRP_EXPT_LOG --附掛在那張Table
3: FOR UPDATE --觸發時機(INSERT/DELETE/UPDATE)
4: AS
5: IF (select is_confirm from inserted )=1
6: BEGIN
7: Declare @key nVarChar(30) --宣告要使用的暫時變數
8: Declare @key1 nVarChar(30) --宣告要使用的暫時變數
9: Declare @dtl_key nVarChar(30) --宣告要使用的暫時變數
10: Declare @dtl_key1 nVarChar(30) --宣告要使用的暫時變數
11: 12: BEGIN --IF Exists --SQL處理
13: Select @key=CASE_ID,@key1=log_id From Inserted ;
14: IF Exists (Select case_id,log_id From TB_CBRP_EXPT_LOG where case_id=@key and log_id=@key1)
15: BEGIN--IF Exists --SQL處理
16: insert into TB_CBRP_EXPT (case_id,case_type,case_group,card_no,branch_id,confirm_id,confirm_datetime,create_id,create_datetime,update_id,update_datetime) (select case_id,case_type,case_group,card_no,branch_id,confirm_id,confirm_datetime,create_id,create_datetime,update_id,update_datetime from tb_cbrp_expt_log where case_id=@key and log_id=@key1);
17: print 'insert into cbrp_expt table ';
18: update tb_cbrp_expt_dtl_log set is_confirm='1' where case_id=@key;
19: END--IF Exists
20: ELSE
21: begin
22: print 'nothing';
23: end
24: 25: END --IF Exists --SQL處理
26: SELECT @dtl_key=CASE_ID FROM TB_CBRP_EXPT_DTL_LOG WHERE CASE_ID=@key and is_confirm=1;
27: 28: BEGIN
29: 30: Declare TRI_CBRP_DTL_CURSOR CURSOR For
31: SELECT case_id,case_seq ,merch_id,case_status,setl_flag,confirm_id,confirm_datetime,create_id,create_datetime,update_id,update_datetime FROM TB_CBRP_EXPT_DTL_LOG WHERE CASE_ID=@dtl_key ;
32: --宣告CURSOR名字及資料 33: Open TRI_CBRP_DTL_CURSOR
34: declare @case_id nvarchar(12),@case_seq numeric(3),@merch_id nvarchar(15),@case_status nvarchar(2);
35: declare @setl_flag nvarchar(1),@confirm_id nvarchar(12),@confirm_datetime datetime,@create_id nvarchar(12);
36: declare @create_datetime datetime,@update_id nvarchar(12),@update_datetime datetime ;
37: 38: fetch next from TRI_CBRP_DTL_CURSOR Into @case_id,@case_seq ,@merch_id,@case_status,@setl_flag,@confirm_id,@confirm_datetime,@create_id,@create_datetime,@update_id,@update_datetime
39: 40: While (@@FETCH_STATUS<>-1) --迴圈
41: BEGIN--While
42: insert into TB_CBRP_EXPT_DTL VALUES(@case_id,@case_seq ,@merch_id,@case_status,@setl_flag,@confirm_id,@confirm_datetime,@create_id,@create_datetime,@update_id,@update_datetime) ;
43: Fetch Next From TRI_CBRP_DTL_CURSOR Into @case_id,@case_seq ,@merch_id,@case_status,@setl_flag,@confirm_id,@confirm_datetime,@create_id,@create_datetime,@update_id,@update_datetime
44: End--While
45: Deallocate TRI_CBRP_DTL_CURSOR --釋放CURSOR
46: END
47: 48: END未來在作SQL SERVER trigger，可以參考用。
訂閱：
文章 (Atom)
