iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 26
0

Trigger 觸發功能,在接收到事件後執行想觸發的Function,例如:在動物資料插入資料時,新增一筆記錄到 操作記錄的資料表。

  • 事件指的就是 INSERTUPDATEDELETE 如剛剛提到的 插入動物資料時
  • 方法就是插入一筆操作記錄到資料表中。

甚至可以設定 事件發生前、或發生後再執行,要每筆記錄都執行,或是SQL語法執行完再執行觸發都可以。

直接來個範例吧!


一樣用我之前建立的資料表,animals資料表,欄位很簡單,就這幾個內容!

資料表內容

假設現在的需求是,動物的名稱,若有修改動物名稱的動作都要記錄下來!

我們必須要有兩個動作步驟完成這件事!

  1. 寫入資料的 Function
  2. 觸發條件設定

寫Function

先來建立一個可以寫入紀錄資料的方法。這邊我們缺少兩樣東西第一個是 要寫入紀錄的資料表,第二個 Function 邏輯

建立一張表

CREATE TABLE animals_log (
    animals_id bigint NOT NULL,
    change_time timestamp with time zone NOT NULL,
    old_name varchar NOT NULL,
    new_name varchar NOT NULL,
PRIMARY KEY (animals_id, change_time)
);  

創建一張表 名稱為 animals_log ,存放動物修改的紀錄, 建立四個欄位,如上SQL 語法所示,並且指定主鍵為 animals_id以及 change_time 複合鍵

建立寫入資料的Function

CREATE OR REPLACE FUNCTION animal_name_if_changed()
    RETURNS trigger AS
$$
BEGIN
    IF NEW.name <> OLD.name THEN
    INSERT INTO animals_log (
        animals_id,
        change_time,
        old_name,
        new_name)
    VALUES
        (OLD.id,
         now(),
         OLD.name,
         NEW.name);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

跟我們昨天嘗試新建Function 方法差不多,先替方法取一個名字animal_name_if_changed,第二行可以看到 RETURNS trigger 回傳的資料類型設定為 trigger

接上 $$ 表示程式的開始與結束,後面再加上BEGIN...END; 包住程式碼,因為我們的程式碼是使用 PL/pgSQL的語言, 裡面就是我們的程式碼 IF...THEN陳述句 中有看到兩個關鍵字,前綴詞 NEWOLD 可以拿到預計寫入的資料,以及舊的欄位資料。 如果兩個<> 不同時,會執行IF...END IF; 之間的程式碼,插入資料到 animals_log 資料表。

至於最後的 RETURN NEW; 執行完這些程式會回傳 NEW(新的資料) 就是預計寫入的資料,但這裡用不到。 昨天的程式碼,有需要輸出值得才用得到。

執行成功輸出結果

觸發條件設定

CREATE TRIGGER animals_update
  AFTER UPDATE
  ON animals
  FOR EACH ROW
  EXECUTE PROCEDURE animal_name_if_changed();

創建一個 TRIGGER 觸發條件,設定一個名稱給它,叫做animals_update ,設定關鍵字 AFTER UPDATE 在更新之後執行,綁定於 animals 資料表,並且FOR EACH ROW 在每一行資料更新時動作,最後附上關鍵字EXECUTE PROCEDURE 接上綁定的function 就可以完成觸發建立囉!

執行成功輸出結果

測試看看囉!

目前我的animals資料表有以下三筆動物資料

執行成功輸出結果

嘗試更新一筆資料的動物名稱,打算把id:3的 安娜 -> 桑尼。

UPDATE animals  
SET name = '桑尼'
WHERE id=3;  

執行成功輸出結果

疑?好像一樣呀!有發生什麼事嗎?

執行的後的結果跟一般更新資料的結果都一樣。

但它已經有觸發了我們寫好的程式!現在來看看 animals_log 這張表!

執行成功輸出結果

多了一筆資料,2019-10-12 下午 3點半左右 把安娜修改為桑尼!

成功!這可以讓我們自動化的執行對應的操作,像是需要追蹤資料的變化就非常適合。

可以把紀錄資料表以不同權限管理員限制管理,或是存在其他紀錄檔的資料庫,確保資料沒有被惡意人士竄改。

補充

FOR EACH ROW 是每一行都會執行
FOR EACH STATEMENT 預設的方式,執行這次SQL觸發一次!不管變更多少行資料!


上一篇
PostgreSQL 內建/自訂 Function
下一篇
PostgreSQL 黑畫面操作 - MAC 初探psql紀錄 (1)
系列文
後端前進PostgreSQL30

尚未有邦友留言

立即登入留言