ファイヤープロジェクト
トリガ
2004-02-05T23:10+09:00   matsu
トリガはDBへの更新があったときに実行される(CまたはPL/pgSQLで記述した)ユーザ定義関数である.SQL92にはないが,いろろと便利なようなので,調査してみた.
トリガはCREATE TRIGGERで定義する.
firstdb=> \h CREATE TRIGGER
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
    ON table FOR EACH { ROW | STATEMENT }
    EXECUTE PROCEDURE func ( arguments )
各項目について以下に示す.
name
トリガ名.
BEFORE | AFTER
指定したeventの前に実行する(BEFOREトリガ)か,後に実行する(AFTERトリガ)かを指定.
event
トリガを発動するイベント(INSERT,DELETE,UPDATE)を指定する.ORで連結することで,同時に複数のイベントを指定できる.
ON table
対象となるテーブルを指定する.
ROW | STATEMENT
FOR EACH ROWの場合は,SQL文の数にかかわらず対象テーブルのレコードに変更がかかる度にトリガが発動する.例えば1SQL文で3つのレコードがDELETEされれば,3回トリガが発動される.STATEMENTは逆に,対象レコードに変更がかかるSQL文単位でトリガが発動される.例えば1SQL文で4つのレコードがDELETEされても,SQL文は1つなのでトリガは1回しか発動されない.
func
トリガが発動したときに実行する関数.
arguments
トリガへの引数
トリガの削除はDROP TRIGGERで行なう.
firstdb=> \h DROP TRIGGER 
Command:     DROP TRIGGER
Description: remove a trigger
Syntax:
DROP TRIGGER name ON table
nameはトリガ名で,tableはそのトリガが対象とするテーブルである.トリガは対象テーブルが削除された際には自動的に削除される.
トリガに登録できる関数はいくつかの条件を満たしたユーザ定義関数である.その条件を以下に示す.
  • 返り値のデータ型はOPQUE型を指定.
  • トランザクション制御処理(BEGIN,END,COMMIT,ROLLBACK,ABORT)がない.
ではトリガのサンプルを作成してみる.まずテーブルhogeを作成する.
firstdb=> CREATE TABLE hoge (
firstdb(> a INTEGER);
CREATE
このテーブルhogeへ更新をかけたときに,aの値やユーザ名,タイムスタンプを記録するテーブルhoge_logを作成する.
firstdb=> CREATE TABLE hoge_log (
firstdb(> old_a INTEGER,
firstdb(> new_a INTEGER,
firstdb(> uname TEXT,
firstdb(> op TEXT,
firstdb(> execTime TIMESTAMP);
CREATE
テーブルhogeに更新がかかったときにテーブルhoge_logへのINSERTを実行する関数hoge_loggerを作成する.
firstdb=> CREATE FUNCTION hoge_logger()
firstdb-> RETURNS OPAQUE AS '
firstdb'> BEGIN
firstdb'>   INSERT INTO hoge_log
firstdb'>   VALUES (OLD.a, NEW.a, CURRENT_USER, TG_OP, ''NOW'');
firstdb'>   RETURN NEW;
firstdb'> END;
firstdb'> ' LANGUAGE 'plpgsql';
CREATE
テーブルhogeにUPDATEがかけられたときに関数hoge_loggerを呼び出すトリガを作成する.
firstdb=> CREATE TRIGGER hoge_trig 
firstdb-> AFTER UPDATE
firstdb-> ON hoge FOR EACH ROW
firstdb-> EXECUTE PROCEDURE hoge_logger();
CREATE
ここでテーブルhogeにUPDATEすると,テーブルhoge_logに新旧の値とデータ操作をしたユーザ名,データ操作,タイムスタンプが挿入される.
firstdb=> INSERT INTO hoge VALUES (1);
INSERT 50598 1
firstdb=> UPDATE hoge SET a = 2 WHERE a = 1;
UPDATE 1
firstdb=> SELECT * FROM hoge_log;
 old_a | new_a | uname |   op   |           exectime            
-------+-------+-------+--------+-------------------------------
     1 |     2 | matsu | UPDATE | 2004-02-05 23:03:40.683079+09
(1 row)
matsu(C)
Since 2002
Mail to matsu