データベーストリガー

データベーストリガーは、データベース内の特定のテーブルまたはビューで特定のイベントが発生したときに自動的に実行される手続き型コードです。トリガーは主に、データベースの情報の整合性を維持するために使用されます。たとえば、従業員テーブルに新しいレコード (新しい従業員を表す) が追加されると、税金、休暇、給与のテーブルにも新しいレコードが作成されます。トリガーは、従業員の以前の給与を追跡するなど、履歴データをログに記録するためにも使用できます。

以下では、いくつかの一般的なDBMSサポートがどのようにトリガーされるかについて、一連の説明を示します。

データが変更されたときに起動する (およびPL/SQLコードを実行する) トリガーに加えて、 Oracle 10gでは、スキーマ レベルのオブジェクト (つまり、テーブル) が変更されたとき、およびユーザーのログオンまたはログオフ イベントが発生したときに起動するトリガーもサポートされています。

• 創造後
• 改造前
• 改造後
• ドロップ前
• ドロップ後
• 挿入前

トリガーには主に次の 4 つの種類があります。

1. 行レベルのトリガー: これは、行の列の値が変更される前または変更された後に実行されます。
2. 列レベルのトリガー:指定された列が変更される前または後に実行されます。
3. 行タイプごとに: このトリガーは、挿入/更新/削除の影響を受ける結果セットの行ごとに 1 回実行されます。
4. 各ステートメント タイプについて: このトリガーは結果セット全体に対して 1 回だけ実行されますが、ステートメントが実行されるたびにも起動されます。

Oracle 8i以降では、データベースイベント（ログオン、ログオフ、起動）によってOracleトリガーを起動できます。^[1]

Microsoft SQL ServerのDDLトリガーで利用可能なすべての発動イベントのリストは、Microsoft Docsで入手できます。^[2]

トリガーでの条件付きアクションの実行 (または変更後のデータのテスト) は、一時的なInsertedテーブルとDeletedテーブルにアクセスすることによって行われます。

1997 年にトリガーのサポートが導入されました。SQL:2003の次の機能は、以前は PostgreSQL に実装されていませんでした。

• SQL では、特定の列の更新時にトリガーを起動できます。PostgreSQL バージョン 9.0 以降では、この機能も PostgreSQL に実装されています。
• この標準では、トリガーアクションとしてSELECT、INSERT、UPDATE以外の多くのSQL文（CREATE TABLEなど）の実行が許可されています。これは、CREATE TABLEを呼び出すストアドプロシージャまたは関数を作成することで実行できます。^[3]

あらすじ:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON TABLE [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname (引数)              
                
         

Firebird は、テーブルごとに複数の行レベル、BEFORE または AFTER、INSERT、UPDATE、DELETE (またはそれらの任意の組み合わせ) トリガーをサポートします。これらのトリガーは常にデフォルトのテーブル変更に加えて実行され、トリガーの相対的な順序は、あいまいな場合 (POSITION 句) に指定できます。トリガーはビューにも存在できます。これらのトリガーは常にトリガーの「代わり」であり、デフォルトの更新可能なビュー ロジックを置き換えます。(バージョン 2.1 より前では、更新可能とみなされるビューのトリガーは、デフォルトのロジックに加えて実行されていました。)

Firebird は変更テーブル例外 (Oracle など) を発生せず、トリガーはデフォルトで必要に応じてネストと再帰の両方を実行します (SQL Server ではネストは許可されますが、再帰は許可されません)。Firebird のトリガーは、NEW および OLD コンテキスト変数 (挿入されたテーブルと削除されたテーブルではない) を使用し、トリガーの現在の使用状況を示すために UPDATING、INSERTING、および DELETING フラグを提供します。

{ CREATE | RECREATE | CREATE OR ALTER } TRIGGER name FOR {テーブル名|ビュー名} [ ACTIVE | INACTIVE ] { BEFORE | AFTER } { INSERT [ OR UPDATE ] [ OR DELETE ] | UPDATE [ OR INSERT ] [ OR DELETE ] | DELETE [ OR UPDATE ] [ OR INSERT ] } [ POSITION n ] AS BEGIN .... END              
   
   
                  
   

 

バージョン 2.1 以降、Firebird は次のデータベース レベルのトリガーもサポートします。

• CONNECT (ここで例外が発生すると接続が完了しません)
• 切断
• 取引開始
• TRANSACTION COMMIT (ここで発生した例外により、トランザクションのコミットが妨げられ、2 フェーズ コミットが関係している場合は準備が妨げられます)
• トランザクションのロールバック

データベース レベルのトリガーは、複数テーブルの制約を適用したり、マテリアライズド ビューをエミュレートしたりするのに役立ちます。TRANSACTION COMMIT トリガーで例外が発生した場合、トリガーによってこれまでに加えられた変更はロールバックされ、クライアント アプリケーションに通知されますが、トランザクションは COMMIT が要求されなかったかのようにアクティブなままです。クライアント アプリケーションは変更を続行し、COMMIT を再要求できます。

データベース トリガーの構文:

{ CREATE | RECREATE | CREATE OR ALTER }トリガー名[ ACTIVE | INACTIVE ] ON { CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK } [ POSITION n ] AS BEGIN ..... END        
    
            
   

 

MySQL/MariaDB DBMSのトリガーに対する限定的なサポートは、2005年にリリースされたMySQLのバージョン5.0で追加されました。^[4]

バージョン 8.0 では、DDL (データ定義言語) トリガーと DML (データ操作言語) トリガーが使用可能です。また、どちらのタイプの DDL トリガー (AFTER または BEFORE) を使用してもトリガーを定義できます。トリガーはCREATE TRIGGER句を使用して作成され、 DROP TRIGGER句を使用して削除されます。イベントが発生したときに呼び出されるステートメントは、FOR EACH ROW句の後に定義され、その後に続くのがそれぞれ式かステートメントかを示すキーワード ( SETまたはBEGIN ) が続きます。 ^[5]

分散システム用の IBM DB2 は DB2 for LUW (LUW は Linux、Unix、Windows の略) として知られ、 Beforeトリガー、Afterトリガー、Instead of トリガーの 3 つのトリガー タイプをサポートしています。ステートメント レベルと行レベルの両方のトリガーがサポートされています。テーブルで同じ操作に対して複数のトリガーがある場合、トリガーの起動順序はトリガー作成データによって決定されます。バージョン 9.7 以降、IBM DB2 は自律トランザクションをサポートしています。^[6]

Before トリガーは、データをチェックし、操作を許可するかどうかを決定するためのものです。Before トリガーから例外がスローされた場合、操作は中止され、データは変更されません。DB2 では、Before トリガーは読み取り専用です。つまり、Before トリガーでデータを変更することはできません。After トリガーは、要求された変更が実行された後の事後処理用に設計されています。After トリガーはテーブルにデータを書き込むことができ、他の一部の^{[どの? ]}データベースとは異なり、トリガーが動作するテーブルを含む任意のテーブルに書き込むことができます。Instead of トリガーは、ビューを書き込み可能にするためのものです。

トリガーは通常、 SQL PL言語でプログラムされます。

CREATE [ TEMP | TEMPORARY ] TRIGGER [ IF NOT EXISTS ] [ database_name .] trigger_name [ BEFORE | AFTER | INSTEAD OF ] { DELETE | INSERT | UPDATE [ OF column_name [, column_name ]...] } ON { table_name | view_name } [ FOR EACH ROW ] [ WHEN条件は必須] BEGIN ... END          
               
   
          

   

SQLite は、ステートメント レベルのトリガーではなく、行レベルのトリガーのみをサポートします。

SQLite ではサポートされていない更新可能なビューは、INSTEAD OF トリガーを使用してエミュレートできます。

非リレーショナル データベースでのトリガーの実装例としては、 XQueryに基づくトリガーのサポートを提供するSednaが挙げられます。 Sedna のトリガーは、 SQL:2003トリガーに類似するように設計されていますが、ネイティブでは XML クエリおよび更新言語 ( XPath、XQuery、および XML 更新言語) に基づいています。

Sedna のトリガーは、データベースに保存されている XML ドキュメントの任意のノードに設定されます。これらのノードが更新されると、トリガーは自動的に XQuery クエリを実行し、その本体で指定された更新を行います。たとえば、次のトリガーは、この人物が参照するオープン オークションがある場合に、人物ノードの削除をキャンセルします。

doc( "auction" )/ site // personに対して、DELETEの前にトリガー"trigger3"を作成します。各ノードで、次の操作を実行します。if ( exists ( $ WHERE // open_auction / bidder / personref / @person = $ OLD / @id )) then ( ) else $ OLD ; }  
     
     
      
    
    
         
          
         
    

トリガーの動作を理解するには、行レベル トリガーとステートメント レベル トリガーという 2 つの主なトリガーの種類について知っておく必要があります。この 2 つの違いは、トリガー内のコードが実行される回数とタイミングです。

あるテーブルへの UPDATE で呼び出されるトリガーがあるとします。行レベルのトリガーは、UPDATE の影響を受ける行ごとに 1 回実行されます。UPDATE コマンドの影響を受ける行がない場合、トリガーはトリガー内のコードを一切実行しないことに留意してください。ステートメント レベルのトリガーは、UPDATE の影響を受ける行の数に関係なく1 回呼び出されます。ここで重要なのは、UPDATE コマンドがどの行にも影響を与えなかったとしても、トリガー内のコードは 1 回実行されることです。

BEFORE および AFTER オプション^[7]を使用して 、トリガーが呼び出されるタイミングを決定します。特定のテーブルへの INSERT で呼び出されるトリガーがあるとします。トリガーが BEFORE オプションを使用している場合、トリガー内のコードはテーブルへの INSERT が発生する前に実行されます。BEFORE トリガーの一般的な使用法は、INSERT の入力値を検証するか、それに応じて値を変更することです。今度は、代わりに AFTER を使用するトリガーがあるとします。トリガー内のコードは、テーブルへの INSERT が発生した後に実行されます。このトリガーの使用例としては、データベースに挿入を行ったユーザーの監査履歴を作成し、行われた変更を追跡することが挙げられます。これらのオプションを使用する場合は、いくつかの点に留意する必要があります。BEFORE オプションではテーブルを変更できないため、入力検証が実用的な使用法です。AFTER トリガーを使用すると、監査履歴テーブルへの挿入など、テーブルを変更できます。

トリガーを作成するときに、それがステートメント レベルか行レベルかを判断するには、行レベルの場合は FOR EACH ROW 句を含めるか、ステートメント レベルの場合は句を省略します。トリガー内で追加のINSERT / UPDATE / DELETEコマンドを使用する場合は注意してください。トリガーの再帰が発生する可能性があり、望ましくない動作が発生する可能性があるためです。以下の例では、各トリガーが異なるテーブルを変更しています。変更内容を確認することで、さまざまなトリガー タイプが使用される一般的なアプリケーションを確認できます。

以下は、更新が影響を受ける各行の後に呼び出される行レベル トリガーの Oracle 構文例です。このトリガーは、電話帳データベースの更新時に呼び出されます。トリガーが呼び出されると、phone_book_audit という別のテーブルにエントリが追加されます。トリガーがシーケンスなどのスキーマ オブジェクトを利用できることにも注意してください。^[8]この例では、audit_id_sequence.nexVal を使用して phone_book_audit テーブルに一意のプライマリ キーが生成されます。

各行のphone_bookの更新後に、トリガーphone_book_auditを作成または置き換えます。BEGIN INSERT INTO phone_book_audit ( audit_id 、audit_change 、audit_l_name 、audit_f_name 、audit_old_phone_number 、audit_new_phone_number 、audit_date ) VALUES ( audit_id_sequence.nextVal 、' Update ' 、: OLD.last_name 、: OLD.first_name 、: OLD.phone_number 、: NEW.phone_number 、SYSDATE ) ; END ;​​​​    
        

     
          
    
         

現在、姓が「Jones」である人に対して、phone_book テーブルで UPDATE を呼び出しています。

電話帳を更新します。電話番号を'111-111-1111'に設定し、 last_nameを'Jones'に設定します。         

phone_number_audit テーブルに 2 つのエントリが設定されていることに注意してください。これは、データベースに姓が「Jones」のエントリが 2 つあるためです。更新によって 2 つの別々の行の値が変更されたため、作成されたトリガーは変更のたびに 1 回ずつ、合計 2 回呼び出されました。

phone_book テーブルへの UPDATE の後に呼び出される Oracle 構文ステートメント トリガー。トリガーが呼び出されると、phone_book_edit_history テーブルに挿入が行われます。

phone_bookのUPDATE後に、トリガーphone_book_historyを作成または置き換えます。BEGIN INSERT INTO phone_book_edit_history ( audit_history_id 、username 、modification 、edit_date ) VALUES ( audit_history_id_sequence.nextVal 、USER 、' Update' 、SYSDATE ) ; END ;    
     

     
        
    
      

ここで、上記の例とまったく同じ更新を実行しますが、今回はステートメント レベルのトリガーを使用します。

電話帳を更新します。電話番号を'111-111-1111'に設定し、 last_nameを'Jones'に設定します。         

結果は、更新によって 2 行が変更されたにもかかわらず、トリガーが 1 回だけ呼び出されたことを示しています。

この例では、WHEN条件を使用してINSERTを変更するBEFORE EACH ROWトリガーを示しています。姓が10文字より大きい場合は、SUBSTR関数^[9]を使用してlast_name列の値を略語に変更します。

各行のphone_bookに対してINSERTする前に、トリガーphone_book_insertを作成または置き換えます。WHEN ( LENGTH ( new.last_name ) > 10 ) BEGIN : new.last_name : = SUBSTR ( : new.last_name , 0 , 1 ) ; END ;    
        
     

      

現在、大きな名前を持つ人物の INSERT を実行しています。

phone_bookにVALUES ( 6 、'VeryVeryLongLastName' 、'Erin' 、'Minneapolis' 、'MN' 、'989 University Drive' 、'123-222-4456' 、55408 、TO_DATE ( '11/21/1991' 、'MM/DD/YYYY' )を挿入します。   
         

トリガーは上記の結果に従って動作し、実行 前にINSERT の値を変更しました。

BEFORE文トリガーの使用は、データベース制限を適用する場合に特に便利です。^[10]この例では、phone_bookテーブルで「SOMEUSER」という名前のユーザーに対して制限を適用する方法を示しています。

SOMEUSERにINSERT する前に、トリガーhauschbcを作成または置き換えます。phone_book BEGIN RAISE_APPLICATION_ERROR ( num => - 20050 、msg = > 'ここにエラー メッセージが表示されます。' ); END ;     
     

     
           
           

ここで、「SOMEUSER」が INSERT を試行した後にログインすると、次のエラー メッセージが表示されます。

SQL エラー: ORA-20050: ここにエラー メッセージが表示されます。

このようなカスタム エラーでは、num 変数を定義できる内容に制限があります。他の多数の定義済みエラーがあるため、この変数は -20000 から -20999 の範囲でなければなりません。

• Microsoft SQL Server DROP トリガー
• MySQL データベース トリガー
• MySQL DB トリガーの作成
• DB2 CREATE TRIGGER ステートメント
• Oracle CREATE TRIGGER
• PostgreSQL トリガーの作成
• Oracle の DELETE CASCADE によるテーブル変更の問題
• SQLite クエリ言語: CREATE TRIGGER
• トリガーに関する Oracle のドキュメント