PostgreSQLでトリガーを作成・置き換える「CREATE OR REPLACE TRIGGER」の使い方
PostgreSQLにおける「CREATE OR REPLACE TRIGGER」の解説
CREATE OR REPLACE TRIGGER は、PostgreSQLデータベースにおいて、トリガーの作成または置き換えを行うためのDDL文です。トリガーとは、データベーステーブルに対する特定の操作（INSERT、UPDATE、DELETEなど）が実行された際に、自動的に実行される一連のSQLステートメントのことです。
構文
CREATE OR REPLACE TRIGGER トリガー名
AFTER 動作タイミング
ON テーブル名
FOR EACH ROW
[WHEN 条件式]
EXECUTE PROCEDURE トリガー関数名([引数]);
解説
CREATE OR REPLACE: 既存のトリガーを置き換えるかどうかを指定します。CREATEのみの場合は、同名のトリガーが存在する場合にエラーとなります。トリガー名: 作成するトリガーの名前を自由に指定します。AFTER 動作タイミング: トリガーが実行されるタイミングを指定します。AFTER以外にも、BEFOREやINSTEAD OFなどのオプションがあります。ON テーブル名: トリガーを適用するテーブル名を指定します。FOR EACH ROW: トリガーが実行される対象となる行を指定します。省略することもできますが、指定することでパフォーマンスが向上する場合があります。WHEN 条件式: トリガーが実行される条件を指定します。オプションであり、指定しない場合はすべての行に対してトリガーが実行されます。EXECUTE PROCEDURE トリガー関数名([引数]): トリガー実行時に呼び出す関数を指定します。この関数は、トリガーのロジックを実装します。
例
以下の例では、customersテーブルに新しいレコードが挿入された際に、そのレコードのcreated_atカラムに自動的に現在時刻を挿入するトリガーを作成します。
CREATE OR REPLACE TRIGGER customer_insert_trigger
AFTER INSERT
ON customers
FOR EACH ROW
WHEN NEW.id IS NOT NULL
EXECUTE PROCEDURE insert_created_at(NEW.id);
この例では、以下のトリガー関数 insert_created_at が呼び出されます。
CREATE OR REPLACE FUNCTION insert_created_at(customer_id INT)
RETURNS TRIGGER AS $$
BEGIN
UPDATE customers
SET created_at = NOW()
WHERE id = customer_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
補足
- トリガーは、データベース操作の自動化、監査、データの整合性維持などに役立ちます。
- トリガーは複雑なロジックを実装できるため、慎重に設計する必要があります。
- トリガーはデータベースのパフォーマンスに影響を与える可能性があるため、適切に使用することが重要です。
PostgreSQLにおけるトリガーのサンプルコード
このセクションでは、PostgreSQLにおけるトリガーの使用方法を理解するために役立つ、いくつかのサンプルコードを紹介します。
例 1: 新規顧客の created_at カラムに自動的に現在時刻を挿入するトリガー
CREATE OR REPLACE TRIGGER customer_insert_trigger
AFTER INSERT
ON customers
FOR EACH ROW
WHEN NEW.id IS NOT NULL
EXECUTE PROCEDURE insert_created_at(NEW.id);
CREATE OR REPLACE FUNCTION insert_created_at(customer_id INT)
RETURNS TRIGGER AS $$
BEGIN
UPDATE customers
SET created_at = NOW()
WHERE id = customer_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
例 2: 商品の在庫数を更新するトリガー
この例では、products テーブルの在庫数を更新するトリガーを作成します。このトリガーは、products テーブルに対して INSERT、UPDATE、DELETE 操作が行われた際に実行されます。
CREATE OR REPLACE TRIGGER product_stock_update_trigger
AFTER INSERT OR UPDATE OR DELETE
ON products
FOR EACH ROW
EXECUTE PROCEDURE update_product_stock(NEW.id, NEW.stock);
CREATE OR REPLACE FUNCTION update_product_stock(product_id INT, new_stock INT)
RETURNS TRIGGER AS $$
BEGIN
UPDATE products
SET stock = COALESCE(NEW.stock, 0)
WHERE id = product_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
例 3: 注文履歴を記録するトリガー
CREATE OR REPLACE TRIGGER order_history_trigger
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
EXECUTE PROCEDURE record_order_history(NEW.id);
CREATE OR REPLACE FUNCTION record_order_history(order_id INT)
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO order_history (order_id, status, updated_at)
VALUES (NEW.id, NEW.status, NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
説明
これらの例は、PostgreSQLにおけるトリガーの基本的な使用方法を示しています。トリガーは、データベース操作の自動化、監査、データの整合性維持などに役立ちます。
REPLACE オプションを使用しない
CREATE OR REPLACE オプションを使用しない場合は、同名のトリガーが存在する場合にエラーとなります。既存のトリガーを置き換える必要がない場合は、この方法を使用できます。
CREATE TRIGGER customer_insert_trigger
AFTER INSERT
ON customers
FOR EACH ROW
WHEN NEW.id IS NOT NULL
EXECUTE PROCEDURE insert_created_at(NEW.id);
INSTEAD OF オプションを使用すると、トリガーが元の操作の代わりに実行されます。これは、元の操作を完全に置き換える必要がある場合に役立ちます。
CREATE TRIGGER customer_insert_trigger
INSTEAD OF INSERT
ON customers
FOR EACH ROW
EXECUTE PROCEDURE insert_customer(NEW.name, NEW.email, NEW.created_at);
この例では、insert_customer 関数は、新しい顧客レコードを customers テーブルに挿入します。元の INSERT 操作は実行されません。
トリガー関数を別のファイルに格納して、トリガー定義から分離することができます。これにより、トリガー定義が読みやすくなり、保守しやすくなります。
CREATE OR REPLACE TRIGGER customer_insert_trigger
AFTER INSERT
ON customers
FOR EACH ROW
WHEN NEW.id IS NOT NULL
EXECUTE PROCEDURE insert_created_at(NEW.id);
この例では、insert_created_at 関数は insert_created_at.sql という別のファイルに格納されています。
CREATE OR REPLACE FUNCTION insert_created_at(customer_id INT)
RETURNS TRIGGER AS $$
BEGIN
UPDATE customers
SET created_at = NOW()
WHERE id = customer_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
トリガーを無効化または削除するには、以下のコマンドを使用します。
DISABLE TRIGGER customer_insert_trigger;
DROP TRIGGER customer_insert_trigger;
sql postgresql ddl
