執行UPDATE時自動記錄更新時間

若需要用到「依照更新順序排列資料」功能時，則會需要在表格被變動時在某個欄位紀錄該筆資料的變更時間以進行排序，這篇筆記在table中新增update_at欄位並使用TRIGGER及FUNCTION對其進行更新，以達成在更新紀錄時自動記錄更新時間的功能

環境

• macOS Big Sur 11.6.5
• PostgreSQL 14

建立更新函式

要更新時間欄位就必須有一個函式負責此工作，新增一個函式，當被呼叫時就會返回目前的時間，以達成更新時間的目的

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

建立資料表

在建立資料表時需要在資料表中新增一個update_at記錄更新時間，如果需要知道建立時間也可以建立一個create_at欄位順便記錄建立時間

CREATE TABLE product (
    product_id BIGSERIAL PRIMARY KEY,--商品編號
    product_name VARCHAR(256) NOT NULL,--商品名稱
    stocks INTEGER NOT NULL,--庫存
    create_at timestamp(6) default current_timestamp,--建立時間
    update_at timestamp(6) default current_timestamp,--更新時間
);

以product資料表為例，需在資料表中新增資料型態為timestamp(6)的create_at及update_at欄位。後面的default current_timestamp參數可以在資料表新增資料時就寫入目前的時間，以正確記錄create_at的數值，而update_at則會在更新資料時被改變

建立觸發器

當函式及欄位都準備好後，需要在執行UPDATE語句時觸發剛才建立好的update_modified_column函式。建立一個名稱為product_update_at的TRIGGER，當product資料表的某個資料被更新時就自動執行update_modified_column函式，自動更新該筆資料的update_at欄位。

CREATE TRIGGER product_update_at BEFORE UPDATE ON product FOR EACH ROW EXECUTE PROCEDURE update_modified_column();