PolarDB:CREATE TRIGGER

説明

CREATE TRIGGERは新しいトリガーを作成します。 CREATE OR REPLACE TRIGGERは、新しいトリガーを作成するか、既存のトリガーを置き換えます。 トリガーは、指定されたテーブル、ビュー、または外部テーブルに関連付けられ、特定の操作がそのテーブルで実行されたときに、指定された関数function_nameを実行します。

トリガーは、行で操作が試行される前 (制約がチェックされ、INSERT、UPDATE、またはDELETEが試行される前) 、または操作が完了した後 (制約がチェックされ、INSERT、UPDATE、またはDELETEが完了した後) 、または操作の代わり (挿入の場合、ビューで更新または削除します。 イベントの前または代わりにトリガーが起動した場合、トリガーは現在の行の操作をスキップするか、挿入されている行を変更できます (INSERTおよびUPDATE操作のみ) 。 イベント後にトリガーが起動すると、他のトリガーの効果を含むすべての変更がトリガーに「表示」されます。

FOR EACH ROWとマークされたトリガーは、操作が変更する行ごとに1回呼び出されます。 たとえば、10行に影響を与えるDELETEでは、ターゲットリレーションのON DELETEトリガーが、削除された行ごとに1回、10回呼び出されます。 対照的に、FOR EACH STATEMENTとマークされたトリガーは、それが変更する行の数に関係なく、任意の特定の操作に対して1回だけ実行されます (特に、0行を変更する操作は、適用可能なfor EACH STATEMENTトリガーの実行になります) 。

INSTEAD OFを起動するように指定されたトリガーは、FOR EACH ROWとマークする必要があり、ビューでのみ定義できます。 ビューのBEFOREおよびAFTERトリガーは、FOR EACH STATEMENTとしてマークする必要があります。

さらに、トリガは、for EACH STATEMENTのみであるが、TRUNCATEに対して発火するように定義することができる。

次の表は、テーブル、ビュー、および外部テーブルで使用できるトリガーの種類をまとめたものです。

また、トリガー定義では、トリガーを起動するかどうかを確認するためにテストされるブールWHEN条件を指定できます。 行レベルのトリガーでは、WHEN条件は、行の列の古い値および /または新しい値を調べることができます。 ステートメントレベルのトリガーにはWHEN条件を設定することもできますが、条件はテーブル内の値を参照できないため、この機能はあまり役に立ちません。

同じイベントに対して同じ種類のトリガーが複数定義されている場合、名前のアルファベット順にトリガーが発生します。

CONSTRAINTオプションを指定すると、このコマンドは制約トリガーを作成します。 これは、SET CONSTRAINTSを使用してトリガー発射のタイミングを調整できることを除いて、通常のトリガーと同じです。 制約トリガーは、プレーンテーブル (外部テーブルではない) のAFTER ROWトリガーである必要があります。 それらは、トリガーイベントを引き起こすステートメントの最後、または含まれているトランザクションの最後のいずれかで発生させることができます。後者の場合、それらは延期されると言われます。 保留中の延期トリガー発火は、SET CONSTRAINTSを使用して直ちに発生させることもできます。 制約トリガーは、実装する制約に違反した場合に例外を発生させると予想されます。

REFERENCINGオプションを使用すると、現在のSQLステートメントによって挿入、削除、または変更されたすべての行を含む行セットである遷移関係を収集できます。 この機能により、トリガーは、一度に1行だけでなく、ステートメントが行った内容のグローバルビューを表示できます。 このオプションは、制約トリガーではないAFTERトリガーにのみ使用できます。また、トリガーがUPDATEトリガーの場合、colume_nameリストを指定しないでください。 OLD TABLEは1回のみ指定でき、UPDATEまたはDELETEで起動できるトリガーに対してのみ指定できます。ステートメントによって更新または削除されたすべての行のbefore-imagesを含む遷移関係を作成します。 同様に、NEW TABLEは、UPDATEまたはINSERTで起動できるトリガーに対してのみ、1回だけ指定できます。ステートメントによって更新または挿入されたすべての行のfter-imagesを含む遷移関係を作成します。

SELECTは行を変更しないため、SELECTトリガーを作成できません。 ルールとビューは、SELECTトリガーが必要と思われる問題に対する実行可能なソリューションを提供します。

概要

CREATE [ CONSTRAINT] トリガー名 {BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
        ON table_name
        [referenced_table_nameから]
        [NOT DEFERRABLE | [ DEFERRABLE]
        [参照 { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
        [FOR [ EACH ] { ROW | ステートメント}]
        [いつ (条件)]
        EXECUTE { FUNCTION | PROCEDURE } function_name (引数)

    ここで、イベントは次のいずれかになります。

        INSERT
        UPDATE [のcolumn_name [, ... ] ]
        DELETE
        トランケート 

パラメーター

新しいトリガーを与える名前。The name to give the new trigger. これは、同じテーブルの他のトリガーの名前とは異なる必要があります。 名前はschema-qualifiedできません。トリガーはテーブルのスキーマを継承します。 制約トリガーの場合、これはSET CONSTRAINTSを使用してトリガーの動作を変更するときに使用する名前でもあります。

BEFORE AFTER INSTEAD OF: イベントの前、後、または代わりに関数を呼び出すかどうかを指定します。 制約トリガーはAFTERとしてのみ指定できます。

event: INSERT、UPDATE、DELETE、TRUNCATEのいずれか。トリガーを起動するイベントを指定します。 遷移関係が要求される場合を除き、複数のイベントはORを使用して指定できます。

UPDATEイベントの場合、次の構文を使用して列のリストを指定できます。

UPDATE OF column_name1 [, column_name2 ... ]

トリガーは、リストされた列の少なくとも1つがUPDATEコマンドのターゲットとして言及されている場合、またはリストされた列の1つがUPDATEのターゲットである列に依存する生成された列である場合にのみ発生します。

INSTEAD OF UPDATEイベントでは、列のリストは許可されません。 列リストは、遷移関係を要求するときにも指定できません。

table_name: トリガーの対象となるテーブル、ビュー、または外部テーブルの名前 (オプションでスキーマ修飾) 。

referenced_table_name: 制約によって参照される別のテーブルの (おそらくスキーマ修飾された) 名前。 このオプションは外部キーの制約に使用され、一般的な使用には推奨されません。 これは、制約トリガーに対してのみ指定できます。

DEFERRABLE NOT DEFERRABLE初期初期定義: トリガーのデフォルトのタイミング。 これらの制約オプションの詳細については、CREATE TABLEのドキュメントを参照してください。 これは、制約トリガーに対してのみ指定できます。

REFERENCING: このキーワードは、トリガーステートメントの遷移リレーションへのアクセスを提供する1つまたは2つのリレーション名の宣言の直前に実行されます。

OLD TABLE NEW TABLE: この句は、次の関係名が、画像遷移前関係に対するものであるか、画像遷移後関係に対するものであるかを示す。

transition_relation_name: この遷移リレーションのトリガー内で使用される (非修飾) 名前。

FOR EACH ROW FOR EACH STATEMENT: トリガーイベントの影響を受けるすべての行に対してトリガー関数を1回実行するか、SQL文ごとに1回実行するかを指定します。 どちらも指定されていない場合、デフォルトはFOR EACH STATEMENTです。 制約トリガーはFOR EACH ROWのみ指定できます。

条件: トリガー関数が実際に実行されるかどうかを決定するブール式。 WHENが指定されている場合、関数は条件がtrueを返した場合にのみ呼び出されます。 FOR EACH ROWトリガーでは、WHEN条件は、それぞれold. column_nameまたはnew. column_nameを書き込むことによって、古い行値および /または新しい行値の列を参照できます。 もちろん、INSERTトリガーはOLDを参照できず、DELETEトリガーはNEWを参照できません。

INSTEAD OFトリガーはWHEN条件をサポートしません。

現在、WHEN式にサブクエリを含めることはできません。

制約トリガの場合、WHEN条件の評価は延期されず、行更新操作が実行された直後に行われることに注意してください。 条件が真と評価されない場合、トリガは遅延実行のためにキューに入れられない。

function_name: 引数を取らず、型triggerを返すと宣言されたユーザー指定の関数。

CREATE TRIGGERの構文では、キーワードFUNCTIONとPROCEDUREは同等ですが、参照される関数はいずれの場合もプロシージャではなく関数でなければなりません。 ここでのキーワードPROCEDUREの使用は歴史的で非推奨です。

arguments: トリガーが実行されたときに関数に提供されるオプションの引数のコンマ区切りリスト。 引数はリテラル文字列定数です。 単純な名前と数値定数もここに記述できますが、それらはすべて文字列に変換されます。 トリガー関数の実装言語の説明を確認して、関数内でこれらの引数にアクセスする方法を確認してください。通常の関数引数とは異なる場合があります。

注

テーブルでトリガーを作成または置換するには、ユーザーがテーブルに対してtrigger権限を持っている必要があります。 ユーザーは、トリガー機能に対するEXECUTE権限も持っている必要があります。

DROP TRIGGERを使用してトリガーを削除します。

列固有のトリガー (UPDATE OF column_name構文を使用して定義されたトリガー) は、その列のいずれかがUPDATEコマンドのSETリストにターゲットとしてリストされている場合に発生します。 更新前のトリガーによって行の内容に加えられた変更は考慮されないため、トリガーが起動されていなくても列の値が変更される可能性があります。 逆に、UPDATEなどのコマンド... SET x = x... 列の値が変更されていない場合でも、は列xでトリガーを起動します。

独自のトリガーコードを記述することなく、一般的な問題を解決するために使用できる組み込みのトリガー関数がいくつかあります。

BEFOREトリガーでは、関数が実行される、または実行される直前にWHEN条件が評価されるため、WHENを使用することは、トリガー関数の開始時に同じ条件をテストすることと大きく異なりません。 特に、条件によって見られるNEW行は、以前のトリガによって修正される可能性がある現在の値であることに留意されたい。 また、BEFOREトリガーのWHEN条件は、NEW行のシステム列 (ctidなど) を調べることはできません。

AFTERトリガーでは、行の更新が発生した直後にWHEN条件が評価され、ステートメントの最後にトリガーを起動するイベントがキューにあるかどうかが判断されます。 したがって、AFTERトリガーのwhen条件がtrueを返さない場合、イベントをキューに入れたり、ステートメントの最後に行を再フェッチしたりする必要はありません。 これにより、いくつかの行に対してトリガーを実行する必要がある場合、多くの行を変更するステートメントの大幅な高速化が発生する可能性があります。

場合によっては、単一のSQLコマンドが複数の種類のトリガーを起動する可能性があります。 たとえば、ON CONFLICT DO UPDATE句を持つINSERTは、挿入操作と更新操作の両方を引き起こす可能性があるため、必要に応じて両方の種類のトリガーを起動します。 トリガーに提供される遷移関係はイベントタイプに固有です。したがって、INSERTトリガーは挿入された行のみを表示し、UPDATEトリガーは更新された行のみを表示します。

ON UPDATE CASCADEやON DELETE SET NULLなどの外部キーの強制アクションによって引き起こされた行の更新または削除は、それらを引き起こしたSQLコマンドの一部として扱われます (このようなアクションは決して延期されません) 。 影響を受けるテーブルの関連するトリガーが起動されるため、SQLコマンドがその型に直接一致しないトリガーを起動する可能性がある別の方法が提供されます。 単純なケースでは、遷移関係を要求するトリガーは、単一の元のSQLコマンドによってテーブルに発生したすべての変更を単一の遷移関係として表示します。 しかしながら、遷移関係を要求するAFTER ROWトリガの存在が、単一のSQLコマンドによってトリガされる外部キー実施アクションを、それぞれがそれ自体の遷移関係を有する複数のステップに分割させる場合がある。 このような場合、存在するステートメントレベルのトリガーは、遷移関係セットの作成ごとに1回起動され、トリガーが遷移関係の影響を受ける各行を1回だけ確認するようにします。

ビューに対するステートメントレベルのトリガーは、ビューに対するアクションが行レベルのINSTEAD OFトリガーによって処理される場合にのみ発生します。 アクションがINSTEADルールによって処理される場合、ルールによって出力されたステートメントは、ビューに名前を付ける元のステートメントの代わりに実行されるため、トリガーは置換ステートメントで名前が付けられたテーブルのトリガーになります。 同様に、ビューが自動的に更新可能な場合、アクションは、ステートメントをビューのベーステーブル上のアクションに自動的に書き換えることによって処理されるため、ベーステーブルのステートメントレベルのトリガーが起動されます。

パーティションテーブルで行レベルのトリガーを作成すると、既存のすべてのパーティションで同じトリガーが作成され、将来作成またはマウントされるパーティションにも同じトリガーが含まれます。 パーティションが親パーティションから分離されている場合、トリガーは削除されます。 パーティションテーブルのトリガーは、AFTERのみ可能です。

パーティションテーブルまたは継承子を持つテーブルを変更すると、明示的に名前が付けられたテーブルにアタッチされたステートメントレベルのトリガーが起動されますが、そのパーティションまたは子テーブルのステートメントレベルのトリガーは起動されません。 対照的に、行レベルのトリガーは、クエリで明示的に名前が付けられていない場合でも、影響を受けるパーティションまたは子テーブルの行で実行されます。 ステートメントレベルのトリガーがREFERENCING句で指定された遷移関係で定義されている場合、影響を受けるすべてのパーティションまたは子テーブルから行の前後のイメージが表示されます。 継承子の場合、行イメージには、トリガーがアタッチされているテーブルに存在する列のみが含まれます。現在、遷移関係を持つ行レベルのトリガーは、パーティションまたは継承子テーブルで定義できません。

例

テーブルaccountsの行が更新されるたびに、関数check_accounts_updateを実行します。

CREATE TRIGGER check_update
        アカウントを更新する前に
        FOR EACH ROW
        EXECUTE関数check_account_update(); 

UPDATEコマンドでcolumn balanceがターゲットとして指定されている場合にのみ関数を実行するようにそのトリガー定義を変更します。

CREATE TRIGGER check_update
        アカウントの残高を更新する前に
        FOR EACH ROW
        EXECUTE関数check_account_update(); 

このフォームは、column balanceが実際に値を変更した場合にのみ関数を実行します。

CREATE TRIGGER check_update
        アカウントを更新する前に
        FOR EACH ROW
        いつ (OLD.balanceはNEW.balanceから離れています)
        EXECUTE関数check_account_update(); 

アカウントの更新をログに記録する関数を呼び出します。

トリガーの作成log_update
        アカウントの更新後
        FOR EACH ROW
        いつ (古い。* 新しいものから離れています。*)
        EXECUTE関数log_account_update(); 

行ごとに関数view_insert_rowを実行して、ビューの基礎となるテーブルに行を挿入します。

トリガーの作成view_insert
        my_viewの挿入の代わりに
        FOR EACH ROW
        EXECUTE FUNCTION view_insert_row(); 

各ステートメントに対して関数check_transfer_balances_to_zeroを実行して、transfer行がゼロのネットにオフセットしていることを確認します。

CREATE TRIGGER transfer_insert
        転送の挿入後
        挿入される参照の新しいテーブル
        各声明のために
        EXECUTE機能check_transfer_balances_to_zero(); 

各行に対して関数check_matching_pairsを実行して、一致するペアに同時に変更が加えられていることを確認します (同じステートメントによって) 。

トリガーの作成paired_items_update
        paired_itemsの更新後
        新しいテーブルをnewtabとして参照古いテーブルとしてoldtab
        FOR EACH ROW
        EXECUTE関数check_matching_pairs();