PostgreSQL（トリガー）

トリガーが実行する関数

PL/pgSQL, C, PL/Perl, PL/Tcl で定義可能。

    create function 関数名() returns trigger as  /* returns trigger 戻り値型 */
    $$
    declare
        宣言部分
    begin
        処理部分
    return 戻り値
    end;
    $$ language plpgsql;

    DB=> \dt

    DB=> drop function 関数名();

トリガー

データベースへの更新（insert/update/delete）があったとき、指定した関数を呼び出すことができる。

    create trigger  トリガー名  実行タイミング  対象命令  on  対象テーブル
    for each row execute procedure 関数名(引数);

    実行タイミング : before（前）, after（後）
    対象命令       : insert, update, delete（or を使用して、複数指定できる）

    DB=> select * from pg_trigger;

    DB=> drop trigger トリガー名 on テーブル名;

テスト

    /* メンバーテーブル */
    create table member_tbl (
        no serial not null primary key,
        reg_day timestamp(0) not null,
        name varchar(30) not null
    );

    /* バックアップテーブル */
    create table backup_tbl (
        no int not null constraint member_tbl_no
            references member_tbl(no) on update cascade on delete cascade,
        reg_day timestamp(0) not null,
        name varchar(30) not null
    );

    /* 手続き言語 plpgsql を定義 */
    create language plpgsql;

    /* トリガーが実行する関数 */
    create function backup() returns trigger as
    $$
    declare
    begin
        insert into backup_tbl (no, reg_day, name)
        values(old.no, old.reg_day, old.name);
        /* insert into backup_tbl values(old.*); と同じ、old は更新前の値 */
    return new;  /* new は更新後の値 */
    end;
    $$
    language plpgsql;

    /* トリガー（関数が存在しないと作成できない）*/
    /* メンバーテーブルを更新する前に、バックアップテーブルへデータを移行 */
    create trigger backup_trg before update on member_tbl
    for each row execute procedure backup();

    DB=> \d

    DB=> select tgname from pg_trigger;

               tgname            
    ------------------------------
     backup_trg

    DB=> drop trigger backup_trg on member_tbl;

    DB=> \df

    DB=> select prosrc from pg_proc where proname = 'backup';

                            prosrc                         
    --------------------------------------------------------

     declare
     begin
         insert into backup_tbl (no, reg_day, name)
         values(old.no, old.reg_day, old.name);
     return new;
     end;

    DB=> drop function backup();

    DB=> select * from member_tbl;

    DB=> update member_tbl set
                    reg_day = current_timestamp, name = 'ichiro' where name = 'suzuki';

    DB=> select * from member_tbl order by no;

    DB=> select * from backup_tbl;

    DB=> select * from member_tbl where no = 2
                union
                select * from backup_tbl where no = 2 order by reg_day desc;

    DB=> select reg_day from member_tbl where no = 2
                union
                select reg_day from backup_tbl where no = 2 order by reg_day desc;

    DB=> select * from member_tbl where no = 2 and reg_day = '2017-02-11 13:24:38'
                union
                select * from backup_tbl where no = 2 and reg_day = '2017-02-11 13:24:38';