SQLite トリガーの自動化

はじめに

この実験 (lab) では、SQLite トリガーの自動化について学びます。トリガーの作成、検証、管理方法を習得し、テーブル上の特定のイベントに応じて SQL ステートメントを自動的に実行する方法を学びます。この実験 (lab) では、監査ログの作成とトリガーの効果的な管理について説明します。

テーブルの作成：employees と employee_audit

このステップでは、従業員データを格納する employees テーブルと、給与の更新を記録する employee_audit テーブルの 2 つを作成します。

まず、ターミナルで SQLite シェルを開きます。

sqlite3 /home/labex/project/test.db

このコマンドは SQLite シェルを開き、test.db という名前のデータベースファイルに接続します。ファイルが存在しない場合、SQLite が作成します。

次に、次の構造で employees テーブルを作成します。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

この SQL コマンドは、employees という名前のテーブルを 3 つの列で作成します。

id: 各従業員の主キーとして機能する整数 (INTEGER)。
name: 従業員の名前を格納するテキストフィールド (TEXT)。
salary: 従業員の給与を格納する実数 (REAL)。

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

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

このテーブルは、給与の変更ログを格納します。列は次のとおりです。

id: 各監査エントリの一意の識別子。自動的にインクリメントされます。
employee_id: 給与が変更された従業員の ID。
old_salary: 更新前の給与。
new_salary: 更新後の給与。
updated_at: 更新が発生した日時を示すタイムスタンプ。

employees へのサンプルデータの挿入

このステップでは、いくつかのサンプルデータを employees テーブルに挿入します。このデータは、次のステップで作成するトリガーをテストするために使用されます。

次の SQL コマンドを実行して、3 つの従業員レコードを挿入します。

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

これらのコマンドは、指定された id、name、および salary の値を持つ 3 つの行を employees テーブルに追加します。

データが正しく追加されたことを確認するには、次のコマンドを実行して、テーブル内のすべてのレコードを表示します。

SELECT * FROM employees;

期待される出力：

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

この出力は、各レコードの id、name、および salary を示しています。

トリガーの作成：salary_update_trigger

次に、給与の更新を自動的に employee_audit テーブルに記録するトリガーを作成します。

次の SQL コマンドを実行して、トリガーを作成します。

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

このコマンドは、salary_update_trigger という名前のトリガーを作成します。このトリガーは、employees テーブルの salary 列が更新された後に自動的に実行されます。

AFTER UPDATE OF salary ON employees: トリガーが employees テーブルの salary 列の更新後にアクティブ化されることを指定します。
BEGIN ... END: トリガーがアクティブ化されたときに実行される SQL ステートメントを囲みます。
INSERT INTO employee_audit ...: 従業員の ID、古い給与、および新しい給与を使用して、新しいレコードを employee_audit テーブルに挿入します。
- OLD.id: 更新前の行の id を参照します。
- OLD.salary: 更新前の salary を参照します。
- NEW.salary: 更新後の salary を参照します。

トリガーのテスト

このステップでは、従業員の給与を更新し、新しいレコードが employee_audit テーブルに追加されたことを確認して、トリガーをテストします。

Bob の給与を更新してみましょう。

UPDATE employees SET salary = 65000.00 WHERE id = 2;

このコマンドは、id が 2 (Bob) の従業員の salary 列を 65000.00 に更新します。

次に、employee_audit テーブルをクエリして、トリガーが機能したかどうかを確認します。

SELECT * FROM employee_audit;

期待される出力：

1|2|60000.0|65000.0|...

employee_audit テーブルに、Bob の従業員 ID、古い給与、および新しい給与を含む行が表示されるはずです。 updated_at 列には、更新のタイムスタンプが表示されます。正確なタイムスタンプは、更新を実行したタイミングによって異なります。

トリガーの削除

このステップでは、SQLite データベースからトリガーを削除する方法を学びます。

salary_update_trigger を削除するには、次のコマンドを実行します。

DROP TRIGGER salary_update_trigger;

このコマンドは、データベースから salary_update_trigger を削除します。

トリガーが削除されたことを確認するには、sqlite_master テーブルをクエリできます。

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

このクエリは空の結果セットを返すはずであり、トリガーが存在しないことを示します。

次に、Alice の給与を更新して、トリガーが実際に削除されたことを確認しましょう。

UPDATE employees SET salary = 55000.00 WHERE id = 1;

次に、employee_audit テーブルを確認します。

SELECT * FROM employee_audit;

新しいエントリが employee_audit テーブルに追加されていないことがわかり、トリガーが正常に削除されたことが確認できます。

最後に、SQLite シェルを終了します。

.exit

まとめ

この実験（Lab）では、特定のテーブルの更新イベントに応じて SQL ステートメントを自動的に実行する SQLite トリガーを作成する方法を学びました。 employees テーブルと employee_audit テーブルを作成しました。次に、給与の更新を employee_audit テーブルに記録する salary_update_trigger という名前のトリガーを作成しました。最後に、トリガーを削除する方法を学びました。