SQLiteでストアドプロシージャとトリガーを活用する方法

SQLiteは軽量で高速なリレーショナルデータベースですが、多くの人がその高度な機能、特にストアドプロシージャとトリガーについてあまり知りません。この記事では、SQLiteでのストアドプロシージャとトリガーの基本から、その応用例までを詳しく説明します。

目次

ストアドプロシージャとは

ストアドプロシージャはデータベース内で実行される一連のSQLステートメントの集まりです。一度定義されると、単一の命令で何度も呼び出すことができます。

SQLiteでのストアドプロシージャの制限

SQLiteには、他の多くのリレーショナルデータベースシステムに存在するようなストアドプロシージャの機能はありません。しかし、SQL関数を作成することで似たような機能を実装できます。

トリガーとは

トリガーは特定のイベントが発生したときに自動的に実行されるプロシージャです。主に、データの整合性を保つためや、特定の操作に対する監視を行うために使用されます。

SQLiteでのトリガーの例

SQLiteでは、AFTERやBEFOREといったキーワードを用いてトリガーを定義します。

-- 例: employeesテーブルに新しいレコードが追加された後に実行されるトリガー
CREATE TRIGGER after_employee_insert
AFTER INSERT
ON employees
BEGIN
  -- 何らかの処理
END;

応用例1: データの監査ログを自動生成

テーブルにデータが追加または変更されたときに、その内容を監査ログとして別のテーブルに保存するトリガーを作成します。

-- 監査ログ用のテーブル作成
CREATE TABLE audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT,
    table_name TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- employeesテーブルにINSERTが行われた後に、監査ログを記録
CREATE TRIGGER insert_audit_log
AFTER INSERT
ON employees
BEGIN
    INSERT INTO audit_logs (action, table_name) VALUES ('INSERT', 'employees');
END;

応用例2: 売上データに基づく自動計算

売上データが追加されたときに、そのデータに基づいて自動で月間売上を更新するトリガーを作成します。

-- 売上データ用のテーブル作成
CREATE TABLE sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    amount INTEGER,
    date DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 月間売上用のテーブル作成
CREATE TABLE monthly_sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    month TEXT,
    total_amount INTEGER
);

-- salesテーブルにINSERTが行われた後に、月間売上を計算・更新
CREATE TRIGGER update_monthly_sales
AFTER INSERT
ON sales
BEGIN
    -- 月を取得
    DECLARE @month TEXT;
    SET @month = strftime('%Y-%m', NEW.date);
  
    -- 月間売上を更新
    UPDATE monthly_sales
    SET total_amount = total_amount + NEW.amount
    WHERE month = @month;
END;

まとめ

SQLiteはストアドプロシージャの機能は限定的ですが、トリガーを使って高度なデータ操作を自動化することができます。この記事で紹介したような応用例を参考に、自分自身のプロジェクトでSQLiteの高度な機能を有効に活用してみてください。

コメント

コメントする

目次