Trigger 觸發功能,在接收到事件後執行想觸發的Function,例如:在動物資料插入資料時,新增一筆記錄到 操作記錄的資料表。
事件
指的就是 INSERT
、UPDATE
、DELETE
如剛剛提到的 插入動物資料時方法
就是插入一筆操作記錄到資料表中。甚至可以設定 事件發生前
、或發生後
再執行,要每筆記錄都執行
,或是SQL語法執行完再執行
觸發都可以。
直接來個範例吧!
一樣用我之前建立的資料表,animals資料表,欄位很簡單,就這幾個內容!
假設現在的需求是,動物的名稱,若有修改動物名稱的動作都要記錄下來!
我們必須要有兩個動作步驟完成這件事!
先來建立一個可以寫入紀錄資料的方法。這邊我們缺少兩樣東西第一個是 要寫入紀錄的資料表,第二個 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
複合鍵
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陳述句
中有看到兩個關鍵字,前綴詞 NEW
、OLD
可以拿到預計寫入的資料,以及舊的欄位資料。 如果兩個<>
不同時,會執行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觸發一次!不管變更多少行資料!