2010年9月30日 星期四

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,可以參考用。