PostgreSQL 影片

Loading...
Google

2007-08-31

PostgreSQL 觸發器(Trigger) 入門

Trigger - 觸發器,與交易資料鎖定一樣,是商用資料庫系統不可或缺的重要功能之一。你可曾要插入資料到資料庫的時候,同時又需要連帶更新其他表單的資料?在過去,你一定也是在前端應用程式,使用 INSERT INTO 然後再用 UPDATE,或是用一句句的 INSERT 命令來完成這任務。許多諸如此類的同步操作,假若都在資料庫系統外一步步下達命令完成,不僅是程式設計上繁複,效能上也不彰。觸發器的功能就是為了解決這類問題而設計的,當你更新或查詢某個資料表時會觸動觸發器,觸發器就會照您所設計的流程,同步去插入、更新、刪除其他資料,你不再需要重復下達多次的 SQL命令就能達成一連串資料的同步處理。


深入了解觸發器函數之前,如果能有個情境會比較好了解其中運作,你可以先想像一下有兩個資料表 mytable 和 mytable_counter 還有其中的各項欄位如下:


  1. mytable - 客戶清單
    • mytable_id - 新增客戶的月份
  2. mytable_counter - 每月份的客戶數量統計表
    • mc_mytable_id - 統計的月份
    • mc_count - 當月客戶總數


如果現在要設計一個機制統計每月客戶數量,你會怎麼做呢?以往傳統做法應該都是在 INSERT INTO mytable 之後,再 UPDATE mytable_counter。可是今天我們要把 UPDATE 的動作寫成觸發器函數,讓日後只要我們 INSERT 資料進去 mytable,PostgreSQL就會自動同步幫我們去 UPDATE mytable_counter 的 mc_count欄位,不需要我們再另外下達 UPDATE 的指令。


以下是一個簡單的觸發器函數例子:

CREATE OR REPLACE FUNCTION pg_mytrigger()
RETURNS "trigger" AS
$BODY$BEGIN
UPDATE mytable_counter SET mc_count = (mc_count + 1) WHERE mc_
mytable_id = NEW.mytable_id;
RETURN NEW;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION
pg_mytrigger() OWNER TO somebody;

其中要注意的,NEW.mytable_id 代表新增加到 mytable 的資料列,其 mytable_id 欄位。在這例子中用來找到當月的統計紀錄,並予以統計數量加一。


套用觸發器函數

到此,觸發器函數到這階段已經建立好了,下一步要決定套用在哪一個資料表,也就是我們要設定哪一個資料表被 INSERT 時,PostgreSQL會去更新統計表的記錄。我們可以用以下指令套用觸發器函數到目標資料表上:

CREATE TRIGGER pg_mytrigger
BEFORE INSERT
ON mytable
FOR EACH ROW
EXECUTE PROCEDURE pg_mytrigger();

這 段命令需要解釋的是 BEFORE 這參數,觸發器雖說是『同時』照你的流程執行,但實際上是分為 BEFORE 和 AFTER,差別在於 BEFORE 是在『更新、查詢資料表之前』執行觸發器預訂的函數,而 AFTER 是在『之後』執行。也就是 BEFORE是先執行觸發器再新增或刪除客戶資料,AFTER是先新增、刪除資料再去執行觸發器。


後記

上 面的例子只是為了說明觸發器 (Trigger) 的功用和使用方法,已經簡化了很多內容,實際上還有許多需要考慮的地方,例如當統計資料表找不到當月的統計資料怎麼辦﹝因為該客戶可能是當月第一個客戶 ﹞?所以必須還要修改觸發器函數去判斷修正這一部份。另外一方面,目前只談到 INSERT 時執行觸發器函數,我們也可以在 DELETE 時執行觸發器函數為統計數量減一,這些都是觸發器可以做得到的,以後再花時間進一步討論。

1 則留言:

pch 提到...

請問某個資料表的serial欄位,我希望在每個年度的開始就歸0重新計算要怎麼做?用trigger可以做嗎?還是有其他方法?

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)