PostgreSQLトリガー・関数入門 - ビジネスロジックをDBに実装する

はじめに

アプリケーション開発において、「データの整合性をどこで担保するか」は重要な設計判断です。すべてのビジネスロジックをアプリケーション層で実装すると、複数のアプリケーションからデータベースにアクセスする場合に整合性を保つことが難しくなります。また、監査ログの記録や自動計算といった処理を各アプリケーションで重複実装することになり、保守性も低下します。

PostgreSQLのトリガーとユーザー定義関数を使えば、これらの課題をデータベース層で解決できます。PL/pgSQLという手続き型言語を使って複雑なロジックを記述し、トリガーによってINSERT、UPDATE、DELETE操作に応じて自動的に実行させることができます。

本記事では、PL/pgSQLの基本構文、ユーザー定義関数の作成方法、トリガーの仕組みと実装例（監査ログ、自動計算、データ検証）、そして注意点とベストプラクティスについて、実践的なサンプルコードとともに解説します。

この記事を読むことで、以下のことができるようになります。

PL/pgSQLの基本構文を理解し、ユーザー定義関数を作成できる
トリガーの仕組み（BEFORE/AFTER、行レベル/文レベル）を理解できる
監査ログ、自動計算、データ検証など実践的なトリガーを実装できる
トリガー利用時の注意点とベストプラクティスを把握できる

前提条件

PostgreSQL 14以降がインストールされていること
psqlまたはGUIツール（pgAdmin、DBeaver等）でデータベースに接続できること
基本的なSQLの構文（SELECT、INSERT、UPDATE、DELETE）を理解していること

基本的なSQL操作については、以下の関連記事を参照してください。

サンプルデータの準備

本記事で使用するサンプルテーブルを準備します。従業員管理システムを想定したデータ構成です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


-- 部署テーブル
CREATE TABLE departments (
    department_id   SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    budget          NUMERIC(15, 2) DEFAULT 0
);

-- 従業員テーブル
CREATE TABLE employees (
    employee_id     SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(255) UNIQUE,
    department_id   INTEGER REFERENCES departments(department_id),
    salary          NUMERIC(10, 2) NOT NULL CHECK (salary >= 0),
    hire_date       DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 初期データ挿入
INSERT INTO departments (name, budget) VALUES
    ('エンジニアリング', 50000000),
    ('営業', 30000000),
    ('人事', 15000000);

INSERT INTO employees (name, email, department_id, salary, hire_date) VALUES
    ('田中太郎', 'tanaka@example.com', 1, 450000, '2023-04-01'),
    ('佐藤花子', 'sato@example.com', 1, 520000, '2022-01-15'),
    ('鈴木一郎', 'suzuki@example.com', 2, 380000, '2024-07-01');

PL/pgSQLの基礎

PL/pgSQLとは

PL/pgSQL（Procedural Language/PostgreSQL）は、PostgreSQLに組み込まれた手続き型言語です。SQLの宣言的な記述に加えて、変数、条件分岐、ループ、例外処理といったプログラミング言語の機能を使用できます。

PL/pgSQLを使う主なメリットは以下のとおりです。

メリット	説明
パフォーマンス	複数のSQL文をサーバー側でまとめて実行でき、クライアント・サーバー間の通信オーバーヘッドを削減
再利用性	共通のロジックを関数として定義し、複数箇所から呼び出せる
セキュリティ	SECURITY DEFINER機能により、特定の権限でのみ実行可能な処理をカプセル化できる
データ整合性	トリガーと組み合わせることで、アプリケーション非依存でデータ整合性を保証できる

基本的な構文

PL/pgSQLの基本構造は以下のとおりです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE OR REPLACE FUNCTION function_name(parameters)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- 変数宣言
    variable_name data_type;
BEGIN
    -- 処理本体
    RETURN result;
END;
$$;

各部分の役割を見ていきましょう。

flowchart TD
    A["CREATE OR REPLACE FUNCTION"] --> B["関数名と引数定義"]
    B --> C["RETURNS: 戻り値の型"]
    C --> D["LANGUAGE plpgsql"]
    D --> E["$$ ... $$: 関数本体"]
    E --> F["DECLARE: 変数宣言"]
    F --> G["BEGIN ... END: 処理ブロック"]
    G --> H["RETURN: 戻り値"]

変数と代入

PL/pgSQLでは、DECLARE節で変数を宣言し、:=演算子またはSELECT INTOで値を代入します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


CREATE OR REPLACE FUNCTION calculate_annual_salary(monthly_salary NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    annual_salary NUMERIC;
    bonus_rate NUMERIC := 0.15;  -- 初期値を設定
BEGIN
    -- 年収 = 月給 × 12 + ボーナス（月給 × ボーナス率 × 12）
    annual_salary := monthly_salary * 12 * (1 + bonus_rate);
    RETURN annual_salary;
END;
$$;

-- 実行例
SELECT calculate_annual_salary(450000);
-- 結果: 6210000

データベースから値を取得して変数に代入する場合は、SELECT INTO構文を使います。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


CREATE OR REPLACE FUNCTION get_department_employee_count(dept_id INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    emp_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = dept_id;
    
    RETURN emp_count;
END;
$$;

-- 実行例
SELECT get_department_employee_count(1);
-- 結果: 2

条件分岐

IF文を使って条件分岐を記述できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


CREATE OR REPLACE FUNCTION get_salary_grade(salary NUMERIC)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
    IF salary >= 700000 THEN
        RETURN 'S';
    ELSIF salary >= 500000 THEN
        RETURN 'A';
    ELSIF salary >= 350000 THEN
        RETURN 'B';
    ELSE
        RETURN 'C';
    END IF;
END;
$$;

-- 実行例
SELECT name, salary, get_salary_grade(salary) AS grade
FROM employees;

CASE式も使用できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


CREATE OR REPLACE FUNCTION get_employment_status(hire_date DATE)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    years_employed INTEGER;
BEGIN
    years_employed := EXTRACT(YEAR FROM age(CURRENT_DATE, hire_date));
    
    RETURN CASE
        WHEN years_employed >= 10 THEN 'ベテラン'
        WHEN years_employed >= 5 THEN '中堅'
        WHEN years_employed >= 1 THEN '一般'
        ELSE '新入社員'
    END;
END;
$$;

ループ処理

PL/pgSQLでは複数のループ構文を使用できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


-- 基本的なFORループ
CREATE OR REPLACE FUNCTION sum_to_n(n INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    total INTEGER := 0;
    i INTEGER;
BEGIN
    FOR i IN 1..n LOOP
        total := total + i;
    END LOOP;
    
    RETURN total;
END;
$$;

-- クエリ結果をループで処理
CREATE OR REPLACE FUNCTION list_employees_in_department(dept_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    emp RECORD;
    result TEXT := '';
BEGIN
    FOR emp IN 
        SELECT name, salary FROM employees WHERE department_id = dept_id
    LOOP
        result := result || emp.name || ': ' || emp.salary || E'\n';
    END LOOP;
    
    RETURN result;
END;
$$;

-- 実行例
SELECT list_employees_in_department(1);

例外処理

BEGIN … EXCEPTION … END構文でエラーをキャッチし、適切に処理できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'ゼロ除算が発生しました。NULLを返します。';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE '予期しないエラー: %', SQLERRM;
        RETURN NULL;
END;
$$;

-- 実行例
SELECT safe_divide(10, 0);
-- NOTICE: ゼロ除算が発生しました。NULLを返します。
-- 結果: NULL

RAISEを使ってエラーや通知メッセージを出力できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


CREATE OR REPLACE FUNCTION validate_salary(salary NUMERIC)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    IF salary < 0 THEN
        RAISE EXCEPTION '給与は0以上である必要があります。入力値: %', salary;
    ELSIF salary > 10000000 THEN
        RAISE WARNING '給与が異常に高い値です: %', salary;
    END IF;
    
    RETURN TRUE;
END;
$$;

RAISEのレベルには以下の種類があります。

レベル	説明
DEBUG	デバッグ用メッセージ
LOG	ログ出力用メッセージ
INFO	情報メッセージ
NOTICE	通知メッセージ（デフォルト）
WARNING	警告メッセージ
EXCEPTION	エラー（処理を中断）

ユーザー定義関数の作成

関数の種類と用途

PostgreSQLでは、用途に応じて様々な関数を作成できます。

種類	用途	例
スカラー関数	単一の値を返す	計算、変換、検証
テーブル関数	複数行を返す	検索、集計
トリガー関数	トリガーから呼び出される	監査、自動更新

スカラー関数の例

業務でよく使う計算ロジックを関数化する例を示します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


-- 税込み価格計算関数
CREATE OR REPLACE FUNCTION calculate_tax_included_price(
    price NUMERIC,
    tax_rate NUMERIC DEFAULT 0.10
)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE  -- 同じ引数なら常に同じ結果を返す
AS $$
BEGIN
    RETURN ROUND(price * (1 + tax_rate), 0);
END;
$$;

-- 実行例
SELECT 
    calculate_tax_included_price(1000) AS with_default_tax,
    calculate_tax_included_price(1000, 0.08) AS with_reduced_tax;
-- 結果: 1100, 1080

関数の属性（IMMUTABLE、STABLE、VOLATILE）は最適化に影響します。

属性	説明	使用例
IMMUTABLE	同じ引数で常に同じ結果。DBを参照しない	数学計算、文字列変換
STABLE	同一トランザクション内で同じ結果	現在時刻参照、設定値参照
VOLATILE	結果が毎回変わりうる（デフォルト）	乱数生成、シーケンス参照

テーブル関数の例

複数行を返す関数はRETURNS TABLEまたはRETURNS SETOFを使用します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


-- 部署別従業員サマリーを返す関数
CREATE OR REPLACE FUNCTION get_department_summary()
RETURNS TABLE (
    department_name VARCHAR,
    employee_count BIGINT,
    total_salary NUMERIC,
    avg_salary NUMERIC
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        d.name,
        COUNT(e.employee_id),
        COALESCE(SUM(e.salary), 0),
        COALESCE(ROUND(AVG(e.salary), 0), 0)
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    GROUP BY d.department_id, d.name
    ORDER BY d.name;
END;
$$;

-- 実行例
SELECT * FROM get_department_summary();

RETURN QUERYを使うことで、クエリ結果をそのまま関数の戻り値として返せます。

OUT引数の活用

複数の値を返す場合は、OUT引数を使う方法もあります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


CREATE OR REPLACE FUNCTION get_employee_stats(
    dept_id INTEGER,
    OUT min_salary NUMERIC,
    OUT max_salary NUMERIC,
    OUT avg_salary NUMERIC,
    OUT employee_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT 
        MIN(salary),
        MAX(salary),
        ROUND(AVG(salary), 0),
        COUNT(*)::INTEGER
    INTO min_salary, max_salary, avg_salary, employee_count
    FROM employees
    WHERE department_id = dept_id;
END;
$$;

-- 実行例
SELECT * FROM get_employee_stats(1);

トリガーの基本

トリガーとは

トリガーは、テーブルに対する操作（INSERT、UPDATE、DELETE、TRUNCATE）が発生したときに、自動的に特定の関数を実行する仕組みです。

sequenceDiagram
    participant App as アプリケーション
    participant DB as PostgreSQL
    participant Trigger as トリガー関数
    
    App->>DB: INSERT/UPDATE/DELETE
    DB->>Trigger: トリガー起動
    Trigger->>Trigger: ビジネスロジック実行
    Trigger->>DB: 処理結果返却
    DB->>App: 操作完了

トリガーの種類

トリガーは実行タイミングと粒度で分類されます。

分類	種類	説明
タイミング	BEFORE	操作の実行前に起動
	AFTER	操作の実行後に起動
	INSTEAD OF	操作の代わりに起動（ビュー用）
粒度	FOR EACH ROW	影響を受ける各行で起動
	FOR EACH STATEMENT	SQL文ごとに1回起動

BEFOREトリガーとAFTERトリガーの使い分けを図で示します。

flowchart LR
    subgraph BEFORE["BEFOREトリガー"]
        B1["データ検証"]
        B2["値の自動設定"]
        B3["操作のキャンセル"]
    end
    
    subgraph Operation["データ操作"]
        O1["INSERT/UPDATE/DELETE"]
    end
    
    subgraph AFTER["AFTERトリガー"]
        A1["監査ログ記録"]
        A2["他テーブル更新"]
        A3["通知送信"]
    end
    
    BEFORE --> Operation --> AFTER

トリガー関数の特殊変数

トリガー関数では、以下の特殊変数が自動的に利用可能になります。

変数	型	説明
NEW	RECORD	INSERT/UPDATEでの新しい行データ
OLD	RECORD	UPDATE/DELETEでの古い行データ
TG_OP	TEXT	操作種別（INSERT、UPDATE、DELETE、TRUNCATE）
TG_NAME	NAME	トリガー名
TG_TABLE_NAME	NAME	トリガーが設定されたテーブル名
TG_TABLE_SCHEMA	NAME	テーブルのスキーマ名
TG_WHEN	TEXT	BEFORE、AFTER、INSTEAD OF
TG_LEVEL	TEXT	ROWまたはSTATEMENT

基本的なトリガーの作成

トリガーの作成は2ステップで行います。

トリガー関数を作成する
トリガーを定義してテーブルに関連付ける

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- ステップ1: トリガー関数の作成
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

-- ステップ2: トリガーの定義
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

これにより、employeesテーブルの行が更新されるたびに、updated_atカラムが自動的に現在時刻に更新されます。

1
2
3


-- 動作確認
UPDATE employees SET salary = 480000 WHERE employee_id = 1;
SELECT employee_id, name, salary, updated_at FROM employees WHERE employee_id = 1;

実践的なトリガー実装例

監査ログの自動記録

データの変更履歴を自動的に記録する監査ログトリガーを実装します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43


-- 監査ログテーブルの作成
CREATE TABLE audit_log (
    audit_id        SERIAL PRIMARY KEY,
    table_name      VARCHAR(100) NOT NULL,
    operation       CHAR(1) NOT NULL,  -- I: INSERT, U: UPDATE, D: DELETE
    old_data        JSONB,
    new_data        JSONB,
    changed_by      VARCHAR(100) NOT NULL,
    changed_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- インデックスの作成（検索性能向上）
CREATE INDEX idx_audit_log_table ON audit_log(table_name);
CREATE INDEX idx_audit_log_changed_at ON audit_log(changed_at);

-- 監査ログトリガー関数
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_log (table_name, operation, old_data, changed_by)
        VALUES (TG_TABLE_NAME, 'D', to_jsonb(OLD), current_user);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'U', to_jsonb(OLD), to_jsonb(NEW), current_user);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_log (table_name, operation, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'I', to_jsonb(NEW), current_user);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$;

-- employeesテーブルに監査ログトリガーを設定
CREATE TRIGGER employees_audit
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_changes();

動作を確認してみましょう。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


-- データ操作
INSERT INTO employees (name, email, department_id, salary, hire_date)
VALUES ('山田次郎', 'yamada@example.com', 2, 400000, '2025-01-01');

UPDATE employees SET salary = 420000 WHERE name = '山田次郎';

DELETE FROM employees WHERE name = '山田次郎';

-- 監査ログの確認
SELECT 
    audit_id,
    table_name,
    operation,
    old_data->>'name' AS old_name,
    old_data->>'salary' AS old_salary,
    new_data->>'name' AS new_name,
    new_data->>'salary' AS new_salary,
    changed_by,
    changed_at
FROM audit_log
ORDER BY audit_id;

自動計算フィールドの更新

部署の従業員数や給与合計を自動的に更新するトリガーを実装します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59


-- 部署テーブルに集計カラムを追加
ALTER TABLE departments 
ADD COLUMN employee_count INTEGER DEFAULT 0,
ADD COLUMN total_salary NUMERIC(15, 2) DEFAULT 0;

-- 部署統計更新トリガー関数
CREATE OR REPLACE FUNCTION update_department_stats()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    affected_dept_ids INTEGER[];
BEGIN
    -- 影響を受ける部署IDを収集
    IF (TG_OP = 'DELETE') THEN
        affected_dept_ids := ARRAY[OLD.department_id];
    ELSIF (TG_OP = 'INSERT') THEN
        affected_dept_ids := ARRAY[NEW.department_id];
    ELSIF (TG_OP = 'UPDATE') THEN
        IF OLD.department_id = NEW.department_id THEN
            affected_dept_ids := ARRAY[NEW.department_id];
        ELSE
            affected_dept_ids := ARRAY[OLD.department_id, NEW.department_id];
        END IF;
    END IF;
    
    -- 影響を受ける部署の統計を再計算
    UPDATE departments d
    SET 
        employee_count = (
            SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id
        ),
        total_salary = (
            SELECT COALESCE(SUM(salary), 0) FROM employees e WHERE e.department_id = d.department_id
        )
    WHERE department_id = ANY(affected_dept_ids);
    
    IF (TG_OP = 'DELETE') THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

-- トリガーの作成
CREATE TRIGGER sync_department_stats
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION update_department_stats();

-- 初期データの同期
UPDATE departments d
SET 
    employee_count = (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id),
    total_salary = (SELECT COALESCE(SUM(salary), 0) FROM employees e WHERE e.department_id = d.department_id);

-- 動作確認
SELECT department_id, name, employee_count, total_salary FROM departments;

データ検証トリガー

CHECK制約では表現できない複雑なビジネスルールを実装します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43


-- 給与変更制限トリガー関数
-- ルール: 一度の給与変更は現在給与の20%以内
CREATE OR REPLACE FUNCTION validate_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    max_change_rate NUMERIC := 0.20;
    change_rate NUMERIC;
BEGIN
    -- 新規登録時は制限なし
    IF TG_OP = 'INSERT' THEN
        RETURN NEW;
    END IF;
    
    -- 給与が変更されていない場合はスキップ
    IF OLD.salary = NEW.salary THEN
        RETURN NEW;
    END IF;
    
    -- 変更率を計算
    change_rate := ABS(NEW.salary - OLD.salary) / OLD.salary;
    
    IF change_rate > max_change_rate THEN
        RAISE EXCEPTION '給与変更は現在給与の20%%以内である必要があります。変更率: %.1f%%', 
            change_rate * 100;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER check_salary_change
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION validate_salary_change();

-- 動作確認（エラーになる例）
-- UPDATE employees SET salary = 700000 WHERE employee_id = 1;
-- ERROR: 給与変更は現在給与の20%以内である必要があります。変更率: 55.6%

-- 動作確認（成功する例）
UPDATE employees SET salary = 470000 WHERE employee_id = 1;

タイムスタンプの自動更新

作成日時と更新日時を自動管理するトリガーを実装します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


-- 汎用タイムスタンプトリガー関数
CREATE OR REPLACE FUNCTION manage_timestamps()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.created_at := COALESCE(NEW.created_at, CURRENT_TIMESTAMP);
        NEW.updated_at := CURRENT_TIMESTAMP;
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.updated_at := CURRENT_TIMESTAMP;
        -- created_atは変更させない
        NEW.created_at := OLD.created_at;
    END IF;
    
    RETURN NEW;
END;
$$;

-- トリガーの作成
DROP TRIGGER IF EXISTS set_updated_at ON employees;
CREATE TRIGGER manage_employee_timestamps
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION manage_timestamps();

条件付きトリガー（WHEN句）

特定の条件でのみトリガーを実行することで、パフォーマンスを最適化できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


-- 給与変更時のみログを記録するトリガー
CREATE TABLE salary_change_log (
    log_id          SERIAL PRIMARY KEY,
    employee_id     INTEGER NOT NULL,
    old_salary      NUMERIC(10, 2),
    new_salary      NUMERIC(10, 2),
    changed_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO salary_change_log (employee_id, old_salary, new_salary)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary);
    
    RETURN NEW;
END;
$$;

-- WHEN句で条件を指定
CREATE TRIGGER track_salary_changes
    AFTER UPDATE ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
    EXECUTE FUNCTION log_salary_change();

WHEN句を使うことで、条件に合致しない行ではトリガー関数が実行されないため、パフォーマンスが向上します。

トリガーの管理

トリガーの一覧確認

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


-- 特定テーブルのトリガー一覧
SELECT 
    trigger_name,
    event_manipulation AS event,
    action_timing AS timing,
    action_orientation AS level
FROM information_schema.triggers
WHERE event_object_table = 'employees';

-- システムカタログから詳細情報を取得
SELECT 
    t.tgname AS trigger_name,
    CASE t.tgtype & 2 WHEN 2 THEN 'BEFORE' ELSE 'AFTER' END AS timing,
    CASE t.tgtype & 4 WHEN 4 THEN 'INSERT' END ||
    CASE t.tgtype & 8 WHEN 8 THEN ' DELETE' END ||
    CASE t.tgtype & 16 WHEN 16 THEN ' UPDATE' END AS events,
    p.proname AS function_name,
    t.tgenabled AS enabled
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
JOIN pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'employees'
  AND NOT t.tgisinternal;

トリガーの有効化・無効化

メンテナンス作業時など、一時的にトリガーを無効化したい場合があります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 特定のトリガーを無効化
ALTER TABLE employees DISABLE TRIGGER employees_audit;

-- テーブルの全トリガーを無効化
ALTER TABLE employees DISABLE TRIGGER ALL;

-- 特定のトリガーを有効化
ALTER TABLE employees ENABLE TRIGGER employees_audit;

-- テーブルの全トリガーを有効化
ALTER TABLE employees ENABLE TRIGGER ALL;

大量データ投入時のパフォーマンス向上のためにトリガーを一時無効化する場合は、トランザクション内で行うことを推奨します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


BEGIN;
    -- トリガーを無効化
    ALTER TABLE employees DISABLE TRIGGER ALL;
    
    -- 大量データ投入
    INSERT INTO employees (name, email, department_id, salary, hire_date)
    SELECT ... FROM staging_table;
    
    -- トリガーを有効化
    ALTER TABLE employees ENABLE TRIGGER ALL;
    
    -- 必要に応じて手動で集計値を更新
    UPDATE departments d
    SET 
        employee_count = (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id),
        total_salary = (SELECT COALESCE(SUM(salary), 0) FROM employees e WHERE e.department_id = d.department_id);
COMMIT;

トリガーの削除

1
2
3
4
5


-- トリガーの削除
DROP TRIGGER IF EXISTS employees_audit ON employees;

-- トリガー関数の削除（依存するトリガーがないことを確認）
DROP FUNCTION IF EXISTS log_changes();

注意点とベストプラクティス

パフォーマンスへの影響

トリガーはデータ操作のたびに実行されるため、パフォーマンスへの影響を考慮する必要があります。

考慮点	対策
トリガー関数の処理時間	複雑な処理は避け、シンプルに保つ
大量データ操作時の負荷	バッチ処理時はトリガーを一時無効化
カスケード（連鎖）実行	トリガーから別テーブルを更新する際の連鎖に注意

1
2
3


-- パフォーマンス計測例
EXPLAIN ANALYZE
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 1;

デバッグとテスト

トリガー関数のデバッグにはRAISE NOTICEを活用します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'トリガー発火: %, 操作: %, テーブル: %', 
        TG_NAME, TG_OP, TG_TABLE_NAME;
    RAISE NOTICE 'OLD: %, NEW: %', 
        CASE WHEN OLD IS NULL THEN 'NULL' ELSE OLD::TEXT END,
        CASE WHEN NEW IS NULL THEN 'NULL' ELSE NEW::TEXT END;
    
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

トリガーの設計原則

トリガーを適切に設計するための原則をまとめます。

mindmap
  root((トリガー設計原則))
    単純性
      1つのトリガーに1つの責務
      複雑なロジックは分割
    べき等性
      同じ操作を複数回実行しても同じ結果
      再実行可能な設計
    透明性
      予期しない副作用を避ける
      ドキュメント化
    保守性
      命名規則の統一
      テスト可能な設計

アプリケーション層との役割分担

トリガーとアプリケーション層でロジックを適切に分担することが重要です。

実装場所	適したロジック	例
データベース（トリガー）	データ整合性、監査、自動計算	タイムスタンプ更新、監査ログ、集計値更新
アプリケーション	UI連携、外部API呼び出し、複雑なビジネスルール	メール送信、Webhook、承認フロー

トリガーに向いているケースと向いていないケースを判断する基準は以下のとおりです。

トリガーに向いているケース:

すべてのアプリケーションで一貫して適用したいルール
データベースの整合性に直接関わる処理
監査・ログ記録
派生データの自動計算

トリガーに向いていないケース:

外部システムとの連携（API呼び出し、メール送信など）
長時間実行される処理
ユーザーインタラクションが必要な処理
頻繁に変更されるビジネスルール

セキュリティ考慮事項

トリガー関数は、関数を定義したユーザーの権限ではなく、トリガーを発火させた操作を実行したユーザーの権限で実行されます。必要に応じてSECURITY DEFINERを使用できますが、セキュリティリスクに注意が必要です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- SECURITY DEFINERを使用する場合は、search_pathを固定する
CREATE OR REPLACE FUNCTION secure_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
    -- 監査ログ処理
    INSERT INTO audit_log (table_name, operation, new_data, changed_by)
    VALUES (TG_TABLE_NAME, 'I', to_jsonb(NEW), current_user);
    
    RETURN NEW;
END;
$$;

まとめ

本記事では、PostgreSQLのトリガーと関数を使ったデータベースレベルのビジネスロジック実装について解説しました。

主なポイントを振り返ります。

PL/pgSQLの基礎: 変数、条件分岐、ループ、例外処理などの基本構文を習得することで、複雑なロジックを実装できます
ユーザー定義関数: スカラー関数、テーブル関数を作成し、共通処理を再利用可能な形でカプセル化できます
トリガーの仕組み: BEFORE/AFTER、行レベル/文レベルの違いを理解し、用途に応じて使い分けます
実践的な実装: 監査ログ、自動計算、データ検証など、実務で役立つトリガーパターンを実装できます
ベストプラクティス: パフォーマンス、保守性、セキュリティを考慮した設計が重要です

トリガーと関数を適切に活用することで、データ整合性の向上、アプリケーションコードの簡素化、監査機能の一元化といったメリットを得られます。一方で、過度な使用はデバッグの困難さやパフォーマンス低下を招く可能性があるため、アプリケーション層との適切な役割分担を意識して設計してください。

はじめに#

前提条件#

サンプルデータの準備#

PL/pgSQLの基礎#

PL/pgSQLとは#

基本的な構文#

変数と代入#

条件分岐#

ループ処理#

例外処理#

ユーザー定義関数の作成#

関数の種類と用途#

スカラー関数の例#

テーブル関数の例#

OUT引数の活用#

トリガーの基本#

トリガーとは#

トリガーの種類#

トリガー関数の特殊変数#

基本的なトリガーの作成#

実践的なトリガー実装例#

監査ログの自動記録#

自動計算フィールドの更新#

データ検証トリガー#

タイムスタンプの自動更新#

条件付きトリガー（WHEN句）#

トリガーの管理#

トリガーの一覧確認#

トリガーの有効化・無効化#

トリガーの削除#

注意点とベストプラクティス#

パフォーマンスへの影響#

デバッグとテスト#

トリガーの設計原則#

アプリケーション層との役割分担#

セキュリティ考慮事項#

まとめ#

参考リンク#