Oracle PL/SQL觸發程式

若想在資料異動時觸發某些動作就可以透過觸發程式達成此目的，Oracle觸發程序可分為以下三種類型。

BEFORE TRIGGER：於資料被異動前會觸發。
AFTER TRIGGER：於資料異動後會觸發。
INSTEAD OF TRIGGER：於資料被異動前會觸發，並可取代原本的異動。

使用語法如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE [OR REPLACE ] TRIGGER 觸發程是名稱
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF 欄位名稱]  
{ON 資料表名稱 | VIEW 名稱}
[FOR EACH ROW]  
[ENABLE | DISABLE]
[WHEN condition]
DECLARE
   .....
BEGIN 
   .....
[EXCEPTION 
   .....]
END;

Oracle觸發程式，可以同時定義多個觸發事件，上多個觸發事件使用同一支觸發程式，帶給開發者取多方便性，Oracle提供以下觸發條件使用語句供使用者於觸發程式內進行判斷。

INSERTING：由 INSERT 觸發。
UPDATING：由 UPDATE 觸發。
UPDATING(‘欄位’)：由 UPDATE 某特定欄位時觸發。
DELETING：由 DELETE 觸發。

於資料異動前後，觸發程式提供NEW以及OLD可以讓開發者取得新舊值，不過先決條件是必須使用「FOR EACH ROW」進行列級別的觸發才行。

以下整理新舊值可以使用的狀態：

INSERT：NEW
UPDATE：NEW、OLD
DELETE：OLD

以下進行相關範例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 定義 TRIGGER
CREATE TRIGGER ARTICLE_TRIGGER
AFTER INSERT OR UPDATE OR DELETE
ON ARTICLE
FOR EACH ROW
BEGIN
    -- 判斷式類型
    CASE
        WHEN INSERTING THEN
            INSERT INTO ARTICLE_LOG(ID, NEW_TITLE, ACTION_TYPE, ACTION_TIME ) VALUES (:NEW.ID, :NEW.TITLE, 'INSERT', SYSDATE);
        WHEN UPDATING THEN
            INSERT INTO ARTICLE_LOG(ID, NEW_TITLE, OLD_TITLE, ACTION_TYPE, ACTION_TIME ) VALUES (:OLD.ID, :NEW.TITLE, :OLD.TITLE, 'UPDATE', SYSDATE);
        WHEN DELETING THEN
            INSERT INTO ARTICLE_LOG(ID, OLD_TITLE, ACTION_TYPE, ACTION_TIME ) VALUES (:OLD.ID, :OLD.TITLE, 'DELETE', SYSDATE);
    END CASE;
END;

-- 進行測試
INSERT INTO ARTICLE (ID, TITLE, CREATE_DATE) VALUES (SYS_GUID(), 'JAVA ThreadLocal 使用', SYSDATE);
COMMIT;
UPDATE ARTICLE SET TITLE = '楊藝的洋溢生活 - JAVA ThreadLocal 使用' WHERE TITLE = 'JAVA ThreadLocal 使用';
COMMIT;
DELETE FROM ARTICLE WHERE TITLE = '楊藝的洋溢生活 - JAVA ThreadLocal 使用';
COMMIT;

查看ARTICLE_LOG資料表內容

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SQL> SELECT * FROM ARTICLE_LOG;

ID
    --------------------------------
NEW_TITLE
--------------------------------------------------------------------------------
OLD_TITLE
--------------------------------------------------------------------------------
ACTION_TYPE          ACTION_TIME
-------------------- ------------------
7CA41C50E91DE0AAE050A8C0FD890E9D

楊藝的洋溢生活 - JAVA ThreadLocal 使用
DELETE               10-DEC-18


ID
--------------------------------
NEW_TITLE
--------------------------------------------------------------------------------
OLD_TITLE
--------------------------------------------------------------------------------
ACTION_TYPE          ACTION_TIME
-------------------- ------------------
7CA41C50E91DE0AAE050A8C0FD890E9D
JAVA ThreadLocal 使用

INSERT               10-DEC-18

ID
    --------------------------------
NEW_TITLE
--------------------------------------------------------------------------------
OLD_TITLE
--------------------------------------------------------------------------------
ACTION_TYPE          ACTION_TIME
-------------------- ------------------
7CA41C50E91DE0AAE050A8C0FD890E9D
楊藝的洋溢生活 - JAVA ThreadLocal 使用
JAVA ThreadLocal 使用
UPDATE               10-DEC-18