為確保資料安全性與歸責，AP(應用程式)可直接將異動人員及異動時間等資料一併儲存，並希望能將資料異動歷程寫入Log，常見以Trigger方式於資料庫端主動處理，以下將ORACLE及MSSQL兩種資料庫執行DML(Data Manipulation Language)指令時，在Trigger中所能取得更新前、後之資料整理如下表，以MSSQL當執行新增、修改動作時可使用INSERTED（更新後）、DELETED（更新前）虛擬資料表取得必要資訊，但對於刪除而言，因異動後記錄已不復存在，而無法藉此取得刪除人員，為解決此問題，在此將介紹MSSQL2016新功能SESSION_CONTEXT，藉此可於目前工作階段（Session）中指定及取回索引鍵值方式，即可達成隱匿傳遞參數功能以解決此難題(參數先寫入Session中，Trigger再由所屬Session中取得)，相對ORACLE則可使用DBMS_APPLICATION_INFO套件(Package)完成。
更新前 | 更新後 | |
ORACLE | :OLD | :NEW |
MSSQL | DELETED | INSERTED |
新增（INSERT） | 未定義，所有欄位均為NULL | 新增後資料值 |
刪除（DELETE） | 資料列刪除前（原始值） | 未定義，所有欄位均為NULL |
修改（UPDAE） | 資料列更新前（原始值） | 更新後新資料值（更新值） |
兩種資料庫於工作階段（Session）設定及取回之函數及基本用法如下（以傳遞員編A825為例）：
資料庫 | I/O | 參數設定/讀取 |
ORACLE | 寫入 | EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO('A825') |
讀取 | SELECT USERENV('CLIENT_INFO') FROM DUAL SELECT CLIENT_INFO FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID') | |
MSSQL | 寫入 | EXEC sp_set_session_context 'user_id', 'A825'; |
讀取 | SELECT SESSION_CONTEXT(N'user_id'); |
ORACLE 8i起提供DBMS_APPLICATION_INFO套件(Package)，已歷史悠久不多作著墨，因此僅針對MSSQL2016所提供函數說明如下:
SESSION_CONTEXT: 工作階段（Session）取得索引鍵/值組
SESSION_CONTEXT(N'key') |
sp_set_session_context: 工作階段（Session）設定索引鍵/值組
sp_set_session_context [ @key= ] N'key', [ @value= ] 'value' [ , [ @read_only = ] { 0 | 1 } ] [ ; ] |
以下將進行三項測試
測試(1): 更新作業，透過取得更新後(:NEW或 INSERTED)等相資關料已可滿足整所需。
測試(2): 刪除作業，於資料庫端模擬T-SQL或PL/SQL之應用。
測試(3): 刪除作業，於AP端模擬開發程式運行(以C#為例)。
# | 測試 | 異動人員 | 說明 |
1 | 更新資料(PMT:A, Val:10->11) | A802 | 一般常見模式。 |
2 | 刪除資料(PMT:B) | A803 | 兩者雷同。 |
3 | 刪除資料(PMT:C)-以C#為例 | A804 | 略不同。 ORACLE需透過資料庫連線物件CONNECTION之參數傳遞， |
測試資料、Trrigger建立，以及後續測試如下:
ORACLE | MSSQL | |
建立 | ------------------------------- --0.1. T1 ------------------------------- --DROP TABLE T1 CREATE TABLE T1 ( PMT VARCHAR2(10), VAL INT, UPD_USER VARCHAR2(10), DATE_UPD DATE DEFAULT (SYSDATE) ) ------------------------------- --0.2. T1 ------------------------------- --DROP TABLE T1_LOG CREATE TABLE T1_LOG ( ACT VARCHAR2(10), PMT VARCHAR2(10), VAL INT, UPD_USER VARCHAR2(10), DATE_CREATE DATE DEFAULT(SYSDATE) ) ------------------------------- --0.3. TRG_T1 ------------------------------- CREATE OR REPLACE TRIGGER TRG_T1 BEFORE UPDATE OR DELETE on T1 FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO T1_LOG (ACT, PMT, VAL, UPD_USER) VALUES ('UPDATING' , :NEW.PMT , :NEW.VAL , :NEW.UPD_USER); END IF; IF DELETING THEN--刪除 INSERT INTO T1_LOG (ACT, PMT, VAL, UPD_USER) VALUES ('DELETING' , :OLD.PMT , :OLD.VAL , USERENV('CLIENT_INFO') ) END IF; END; | ------------------------------- --0.1. T1 ------------------------------- --DROP TABLE IF EXISTS T1 CREATE TABLE T1 ( PMT VARCHAR(10), VAL INT, UPD_USER varchar(10), DATE_UPD date DEFAULT (GETDATE()) ) ------------------------------- --0.2. T1_LOG ------------------------------- ---DROP TABLE IF EXISTS T1_LOG CREATE TABLE T1_LOG ( ACT varchar(10), PMT varchar(10), VAL int, UPD_USER varchar(10), DATE_CREATE datetime DEFAULT (GETDATE()) ) ------------------------------- --0.3. TRG_T1 ------------------------------- --DROP TRIGGER IF EXISTS TRG_T1 CREATE TRIGGER TRG_T1 ON T1 FOR UPDATE, DELETE AS BEGIN IF EXISTS(SELECT * FROM INSERTED) --UPDATE INSERT INTO T1_LOG (ACT, PMT, VAL, UPD_USER) SELECT 'UPDATING' , I.PMT, I.VAL , I.UPD_USER FROM INSERTED I ELSE --DELETE INSERT INTO T1_LOG (ACT, PMT, VAL, UPD_USER) SELECT 'DELETING' , D.PMT, D.VAL , CAST(SESSION_CONTEXT(N'user_id') AS varchar(10)) --SESSION_CONTEXT回傳為sql_variant需轉型 FROM DELETED D END |
測試 | ------------------------------- --1.0. 測試資料 ------------------------------- /* TRUNCATE TABLE T1; TRUNCATE TABLE T1_LOG; */ INSERT INTO T1 (PMT, VAL, UPD_USER) VALUES ('A', 10, 'A801'); INSERT INTO T1 (PMT, VAL, UPD_USER) VALUES ('B', 20, 'A801'); INSERT INTO T1 (PMT, VAL, UPD_USER) VALUES ('C', 30, 'A801); ------------------------------- --1.1. 測試(1) ------------------------------- UPDATE T1 SET VAL = 11 , UPD_USER = 'A802' , DATE_UPD = SYSDATE WHERE PMT='A' --SELECT * FROM T1_LOG ------------------------------- --1.2. 測試(2) ------------------------------- CALL DBMS_APPLICATION_INFO.SET_CLIENT_INFO('A803'); --SELECT USERENV('CLIENT_INFO') FROM DUAL DELETE T1 WHERE PMT='B' | ------------------------------- --1.0. 測試資料 ------------------------------- /* TRUNCATE TABLE T1 TRUNCATE TABLE T1_LOG */ INSERT INTO T1 (PMT, VAL, UPD_USER) VALUES('A', 10, 'A801'); INSERT INTO T1 (PMT, VAL, UPD_USER) VALUES('B', 20, 'A801'); INSERT INTO T1 (PMT, VAL, UPD_USER) VALUES('C', 30, 'A801'); ------------------------------- --1.1. 測試(1) ------------------------------- UPDATE T1 SET VAL = 11 , UPD_USER = 'A802' , DATE_UPD = GETDATE() WHERE PMT='A' --SELECT * FROM T1_LOG ------------------------------- --1.2. 測試(2) ------------------------------- EXEC sp_set_session_context 'user_id', 'A803' --SELECT CAST(SESSION_CONTEXT(N'user_id') AS varchar(10)) DELETE T1 WHERE PMT='B' |
測試(3)-C#
DB | C# |
ORACLE | using Oracle.ManagedDataAccess.Client; (略) string sConn = "Data Source=TestDB; User Id=Dev; Password=admin;"; string sSQL = "DELETE T1 WHERE PMT='C'"; using (OracleConnection conn = new OracleConnection(sConn)) { conn.Open(); conn.ClientInfo = "A804"; //寫入員編(透過資料庫連線元件參數) OracleCommand cmd = new OracleCommand(sSQL); cmd.Connection = conn; int nRet = cmd.ExecuteNonQuery(); } |
MSSQL | using System.Data.SqlClient; (略) string sConn = "Data Source=TestDB;Initial Catalog=DevDB;User ID=dev;Password=admin;"; string sSQL = @"EXEC sp_set_session_context 'user_id', 'A804'; //寫入員編 DELETE T1 WHERE PMT='C'"; using (SqlConnection conn = new SqlConnection(sConn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sSQL, conn); int nRet = cmd.ExecuteNonQuery(); } |
執行觸發記錄(T1_LOG)如下所示。其中最重要的在於測試(2)及測試(3)，此解決過去之難題。
ACT | PMT | VAL | UPD_USER | DATE_CREATE | |
1 | UPDATING | A | 11 | A802 | 2020/05/30 06:31:04 |
2 | DELETING | B | 20 | A803 | 2020/05/30 06:31:16 |
3 | DELETING | C | 30 | A804 | 2020/05/30 06:32:46 |
本文所介紹如何在目前工作階段（Session）中利用隱匿傳遞參數之功能，除可解決前述記錄刪除人員外，先前也曾用以解決VIEW執行效能不佳問題，先將重要篩選條件以此方式傳入VIEW中，VIEW中即中即可藉此有效限縮資料空間進而提升效能，尤其是VIEW之底層原始建構資料表資料量稍大或組成複雜時，資料庫效能難以提升時，此可為另一項解決選項。
參考資料:
