PostgreSQL トリガーによる自動化

はじめに

この実験（Lab）では、トリガーを使用して PostgreSQL でタスクを自動化する方法を学びます。この実験（Lab）では、PL/pgSQL を使用してトリガー関数を作成し、employeesテーブルの給与変更を別のsalary_changesテーブルに自動的に記録することに焦点を当てます。

トリガー関数の作成、テーブルイベント（特にemployeesテーブルの更新）へのバインド、トリガーの動作のテスト、そして最後にトリガーの無効化と削除について説明します。

Skills Graph

テーブルとトリガー関数の作成

このステップでは、employeesテーブルとsalary_changesテーブルを作成し、PL/pgSQL を使用してlog_salary_change関数を定義します。

まず、postgresユーザーとして PostgreSQL データベースに接続します。

sudo -u postgres psql

次に、employeesテーブルを作成します。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

このコマンドは、id（主キー）、name、およびsalaryカラムを持つテーブルを作成します。

次に、給与変更の履歴を保存するためのsalary_changesテーブルを作成します。

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

次に、PL/pgSQL を使用してトリガー関数を作成します。この関数は、employeesテーブルの更新操作の前に実行されます。古い給与と新しい給与の値をsalary_changesテーブルに記録します。

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

このコードは、log_salary_changeという名前の関数を定義します。給与が変更されたかどうかを確認します。変更された場合、従業員 ID、古い給与、新しい給与、および現在のタイムスタンプ（timestamp）を含む新しい行をsalary_changesテーブルに挿入します。

トリガーの作成

このステップでは、給与の更新が発生したときに自動的に実行されるように、log_salary_change関数をemployeesテーブルにバインドします。

次の SQL コマンドを使用してトリガーを作成します。

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

このコマンドは、salary_change_triggerという名前のトリガーを作成します。トリガーは、employeesテーブルに対するUPDATE操作の前にアクティブ化されるように指定します。FOR EACH ROW句は、更新される各行に対してトリガー関数が実行される必要があることを示します。

トリガーのテスト

このステップでは、トリガーの動作をテストします。employeesテーブルにデータを挿入し、給与を更新して、トリガーがsalary_changesテーブルに変更を正しく記録するかどうかを確認します。

まず、初期データをemployeesテーブルに挿入します。

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

次に、Alice の給与を更新します。

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

トリガーが正しく機能したことを確認するには、salary_changesテーブルをクエリ（query）します。

SELECT * FROM salary_changes;

salary_changesテーブルに、Alice の給与変更に関する情報を含む行が表示されるはずです。

次に、Bob の給与をすでに持っているのと同じ値に更新します。

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

給与が実際には変更されていないため、トリガーはsalary_changesテーブルに新しい行を挿入しないはずです。これを確認しましょう。

SELECT * FROM salary_changes;

Alice の給与変更に関連する 1 行のみが表示されるはずです。

最後に、新しい従業員を挿入します。

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

新しい従業員を挿入しても、log_salary_changeトリガーはトリガーされないはずです。これは、UPDATEイベントでのみ起動するように設定されているためです。salary_changesテーブルを再度確認して、これを確認しましょう。

SELECT * FROM salary_changes;

Alice の給与変更に関連する 1 行のみが表示されるはずです。

トリガーの無効化と削除

このステップでは、PostgreSQL でトリガーを無効化および削除する方法を学びます。

まず、salary_change_triggerを無効にします。

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

次に、Alice の給与を再度更新します。

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

salary_changesテーブルを確認します。

SELECT * FROM salary_changes;

以前の更新からの 1 行のみが表示されるはずです。これは、トリガーが無効になっていることを確認します。

トリガーを再度有効にするには、次のコマンドを使用します。

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

最後に、トリガーを完全に削除するには、次のコマンドを使用して削除（drop）します。

DROP TRIGGER salary_change_trigger ON employees;

まとめ

この実験（Lab）では、トリガーを使用して PostgreSQL でタスクを自動化する方法を学びました。PL/pgSQL を使用してトリガー関数を作成し、employeesテーブルの給与変更をsalary_changesテーブルに記録しました。関数を作成し、テーブルイベントにバインドし、テストし、無効化/削除しました。