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,可以參考用。
2010年6月7日 星期一
遠端桌面連線數已滿的解決方式
當遠端桌面連線只有2個連線數的限制後,兩個連線都被佔滿之後,在使用mstsc登入時,會出現已超過連線限制的訊息:『終端機伺服器已經超過允許的最大連線數目』,
這時我使用mstsc /console也無法登入,後來上網查詢了一下其它網友分享的解決方式如下:
1.在遠端桌面登入輸入伺服器名稱的地方再加上 /admin ,
原本是/console-->但這個方式在xp sp3後,就無法再使用了。
電腦的地方,請輸入 『xxx.xxx.xxx.xxx /admin』,按連線,接著輸入登入帳號,即可順利登入嘍!!
連線後,盡快登入”終端機服務管理員,將逾時連線的user登出,以免之後再繼續佔滿。
以前常常被這種連線數困擾,有此方式,就不需要請客戶端經常幫我踢掉那些佔用的連線了!^^
如果常常有使用者佔著連線不作事,可以在群組原則裡設定三個限制項目,
利用 gpedit.msc
至於每個項目會影響到使用者的使用方式及結果,就要靠自己評估了!
ps: 看到有一篇文章也在解這個問題,
http://ns2.ublink.org/phpbb/viewtopic.php?p=756
但這個方式連線後,還是會出現連線數滿的狀況。建議用上述第一種方式。
2010年5月7日 星期五
Oracle不區分字母大小寫的文章
在Oracle 10G R2 版本new feature中,有提到oracle在字母區分大小寫的部分,有作了一些enhancements,以看官方文件中的此主題:『Linguistic Sorting and String Searching』
URL:http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch5lingsort.htm#i1009059
主要是ap可能在進行這類型的需求時,需要先改變session層級設定:
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
2010年5月5日 星期三
SQLNET.ora 遇domain naming時的參數設定
Oracle DB Server若有domain 設定時,
用netca 新建命名規則,sqlnet.ora會自動幫您新建一個參數
NAMES.DEFAULT_DOMAIN = <domain name>
若mark掉,在connect時會出現tns error:
必需要帶sqlplus user/password@SID.domain才能連線。
設定此參數後,即可利用Sqlplus user/password@SID 連線成功。
2010年4月30日 星期五
LOG_ARCHIVE_FORMAT參數如何設定
%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it』s own archivelog
%T thread number, zero filled, needed when running RAC as each node creates it』s own archivelog
Format options available on 10g
%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it』s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it』s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.
In 10g, %s, %t, %r are required to be present in the parameter, if it doesn』t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r. Using this format makes it the archive log filename unique for that instance.
2010年3月25日 星期四
在Oracle 10g之後安裝Oracle9i ,DBCA會啟動失敗
最近重新在裝了Oracle10G Client的機器上為了安裝oracle 9i Database Server,安裝完後,卻出現dbca啟動失敗。
後來重灌了2次oracle 9i (還以為是安裝元件出了錯),錯誤好像跟JRE連結有關,原本以為是jre版本相衝突,到環境變數修改path,將jre舊的版本提前,結果還是錯誤。
錯誤畫面
最後Google了一些關鍵字,終於找到一個相同的案例,說是jre環境變數的原因,但不是動jre的版本順序,而是要直接將oracle 10g的home path先移除,等到啟動了dbca後,再恢復環境變數。
解法:
環境變數修改>>
原本的:
C:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.1.8\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;c:\Oracle\product\10.1.0\Client_1\BIN;C:\Program Files\Oracle\jre\1.3.1\bin;
換成:
C:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
啟動dbca>>
成功!!正在安裝資料庫呢!
趕緊記下來,免得以後又忘了類似的問題~~參考的url:http://www.100test.com/html/142/s_142717_14.htm
等到dbca運行正常後,請記得把oracle 10g的path再加回去環境變數中哦!