学ぶトリガ | トリガー、ウィンドウ関数、DCL

メニューを表示するにはスワイプしてください

データベースにおけるトリガーは、特定のイベントやアクションがデータベース内で発生した際に自動的に実行される特別な種類のストアドプロシージャです。
これらのイベントには、テーブルへの挿入、更新、データ削除、またはテーブルやビューなどのデータベーススキーマオブジェクトの変更が含まれます。

DMLトリガー

DMLトリガー（データ操作言語トリガー）は、テーブルやビューで特定のイベントが発生した際に指定されたアクションを自動的に実行するデータベースオブジェクトです。
DMLトリガーは、ビジネスルールの強制、データ整合性の維持、監査タスクの実行などによく利用されます。

DMLトリガーの種類

AFTERトリガー：これらのトリガーは、テーブルでトリガーとなるアクション（挿入、更新、削除）が実行された後に発火します。
BEFOREトリガー：これらのトリガーは、トリガーとなるアクションの前に発火します。元のアクションをインターセプトし、独自のロジックを実行した上で、元のアクションを実行するかどうかを決定できます。

AFTERトリガーの例

本コースの最初のセクションで、トランザクションを使用してBankAccountsテーブルとUserLogsテーブルに値を追加する例を紹介しました。
今回はトランザクションの代わりに、AFTERテーブルのINSERT操作に対してBankAccountsトリガーを作成することで同様の処理を実現できます。
これにより、新しいアカウントの挿入のみが可能となり、対応するログ値はトリガーによって自動的に追加されます。


              12345678910111213141516
            
-- Create an after-insert trigger for UserLogs
CREATE FUNCTION after_insert_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Perform some action, like logging the insert
    INSERT INTO UserLogs (account_number, action)
    VALUES (NEW.account_number, CONCAT('New record inserted for ', NEW.account_holder));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger to the BankAccounts table
CREATE TRIGGER after_insert_trigger
AFTER INSERT ON BankAccounts
FOR EACH ROW
EXECUTE PROCEDURE after_insert_trigger_function();

クエリの説明

user_logsテーブルへのAFTER INSERTトリガーの作成：
- afterinserttriggerfunction()という名前の関数を作成。
- この関数はトリガーを返すように定義されている。
- 関数の本体はBEGINブロックで始まる。
- BEGINブロック内で、userlogsテーブルにレコードを挿入するアクションが実行される。
- 挿入されるレコードには、トリガーイベントのNEW行から値が取得される。
- 挿入される値にはaccountnumberと、アクション内容を示す文字列が含まれる。
- 関数はNEW行を返す。
- 関数はplpgsql言語で記述されている。
bankaccountsテーブルへのトリガーのアタッチ：
- afterinserttriggerという名前のトリガーを作成。
- このトリガーはbankaccountsテーブルのINSERT操作後に実行されるように設定されている。
- トリガーは影響を受けた各行ごとにafterinserttriggerfunction関数を実行するように設定されている。

注意

PostgreSQLのトリガーにおいて、NEWはテーブルに挿入または更新された新しい行を表す特別なレコード変数。トリガー関数内で新しい行のカラム値にアクセス可能。削除された行を扱う場合は、OLDの代わりにNEWレコード変数を使用。

BEFOREトリガーの例

負の口座残高を持つ値の追加を防止したい場合を想定。これを実現するためにBEFOREトリガーを作成可能。このトリガーは操作前に残高を確認し、負の場合は操作が実行されない。


              12345678910111213141516171819202122232425
            
-- Create the trigger function
CREATE FUNCTION before_insert_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    -- Check if the account balance is less than 0
    IF (NEW.balance < 0) THEN
        -- If balance is less than 0, do nothing and return
        RAISE NOTICE 'Balance is less than 0. Action not logged.';
    ELSE
        -- If balance is greater than or equal to 0, log the action
        RAISE NOTICE 'Logging insert into UserLogs';
        -- Inserting into the UserLogs table
        INSERT INTO UserLogs (account_number, action)
        VALUES (NEW.account_number, CONCAT('Account created for ', NEW.account_holder));
    END IF;

    -- Indicate that the trigger function has completed successfully
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON BankAccounts
FOR EACH ROW
EXECUTE PROCEDURE before_insert_trigger_function();

クエリの説明

トリガー関数の作成：
- before_insert_userlogs() という名前の関数を作成または置換。
- この関数はトリガーを返すように定義。
- 関数の本体は BEGIN ブロックで開始。
- BEGIN ブロック内で、口座残高が0未満かどうかを条件で判定。
- 残高が0未満の場合、通知を発行し、アクションは記録されない。挿入を防ぐために関数はNULLを返す。
- 残高が0以上の場合、アクションが記録されたことを示す通知を発行。
- その後、NEW行の値を含むレコードを userlogs テーブルに挿入。
- 挿入される値には accountnumber と、アクションを示す連結文字列が含まれる。
- 挿入を許可するために関数は NEW を返す。
- 関数は plpgsql 言語で記述。
トリガーの作成：
- before_insert_userlogs_trigger という名前のトリガーを作成。
- このトリガーは bankaccounts テーブルへの INSERT 操作の前に実行されるように設定。
- トリガーは影響を受ける各行ごとに before_insert_userlogs() 関数を実行するように設定。

トリガー作成パターン

トリガーを作成する際の一般的なパターンとして、まず特定のロジックを操作の前後で実行する関数を定義します。その後、この関数を指定したテーブルとアクションにトリガーとして関連付けます。

次の文にご注意ください：

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
EXECUTE PROCEDURE proc_name;

この場合、UPDATE 操作の代わりに DELETE や INSERT を利用することも可能です。
ただし、注意点として、SELECT 文はテーブル内の行を変更しないため、SELECT トリガーは作成できません！

すべて明確でしたか？

フィードバックありがとうございます！

セクション 3. 章 1

AIに質問する

何でも質問するか、提案された質問の1つを試してチャットを始めてください

セクション 3. 章 1