CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
ここでeventは以下のいずれかを取ることができます。
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATECREATE 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で発行するように定義することができます。
以下の表にどの種類のトリガがテーブルとビューに対して使用できるかをまとめます。
| いつ | イベント | 行レベル | 文レベル |
|---|---|---|---|
| BEFORE | INSERT/UPDATE/DELETE | テーブル | テーブルおよびビュー |
| TRUNCATE | — | テーブル | |
| AFTER | INSERT/UPDATE/DELETE | テーブル | テーブルおよびビュー |
| TRUNCATE | — | テーブル | |
| INSTEAD OF | INSERT/UPDATE/DELETE | ビュー | — |
| TRUNCATE | — | — |
またトリガ定義では、トリガを発行すべきかどうかを判定する、論理値のWHEN条件を指定することができます。 行レベルのトリガでは、WHEN条件は行の列の古い値、新しい値、またはその両方で検証することができます。 文レベルのトリガでもWHEN条件を持たせることができますが、条件としてテーブル内の何らかの値を参照することができませんので、この機能はあまり有用ではありません
同一イベントに同じ種類の複数のトリガが定義された場合、名前のアルファベット順で実行されます。
CONSTRAINTオプションが指定された場合、このコマンドは制約トリガを作成します。 これは、SET CONSTRAINTSを使用してトリガを発行するタイミングを調整することができるという点を除き、通常のトリガと同じです。 制約トリガはAFTER ROWトリガでなければなりません。 トリガイベントを引き起こした文の最後、またはそれを含むトランザクションの最後のいずれかで発行することができます。 後者の場合、遅延と呼ばれます。 SET CONSTRAINTSを使用することで、強制的に待機中の遅延トリガの発行を即座に行わせることができます。 制約トリガは、実装する制約に違反した時に例外を発生するものと想定しています。
SELECTはまったく行を変更しないため、SELECTトリガを作成することはできません。 この場合は、ルールやビューの方が適しています。
トリガに関するより詳細については、第36章を参照してください。
新しいトリガに付与する名前です。 同じテーブルの他のトリガと異なる名前にする必要があります。 名前をスキーマ修飾することはできません。 トリガはそのテーブルのスキーマを引き継ぎます。 制約トリガの場合、この名前がSET CONSTRAINTSを使用してトリガの動作を変更する時に使用されます。
関数の呼び出しをイベントの前に行うか後に行うか、それとも代替として行うかを決定します。 制約トリガではAFTERとしてしか指定することができません。
INSERT、UPDATE、DELETE、TRUNCATEのいずれかが入ります。 このパラメータは、トリガを起動するイベントを指定します。 ORを使用して、複数のイベントを指定することができます。
UPDATEイベントでは、以下の構文を使用して列リストを指定することができます。
UPDATE OF column_name1 [, column_name2 ... ]
このトリガはUPDATEコマンドの対象として列挙された列のいずれか少なくとも1つの列が指定された場合に発行されます。
INSTEAD OF UPDATEイベントは列リストをサポートしません。
トリガを作成するテーブルまたはビューの名前です（スキーマ修飾名も可）。
制約で参照される（おそらくスキーマ修飾された）他のテーブルの名前です。 このオプションは外部キー制約で使用されるものであり、一般利用を推奨しません。 これは制約トリガでのみ指定することができます。
トリガのデフォルトのタイミングです。 これらの制約オプションについてはCREATE TABLE文書を参照してください。 これは制約トリガでのみ指定することができます。
このパラメータは、トリガプロシージャを、トリガイベントによって影響を受ける行ごとに1回起動するか、SQL文ごとに1回のみ起動するかを指定します。 どちらも指定されない場合は、FOR EACH STATEMENTがデフォルトです。 制約トリガではFOR EACH ROWのみ指定することができます。
トリガ関数を実際に実行するか否かを決定する論理式です。 WHENが指定された場合、conditionがtrueを返す場合のみ関数が呼び出されます。 FOR EACH ROWトリガでは、WHEN条件で、それぞれOLD.column_name、NEW.column_nameと記述することで、古い行値、新しい行値、またはその両方の列を参照することができます。 当然ながらINSERTトリガではOLDを参照することはできませんし、DELETEトリガではNEWを参照することはできません。
INSTEAD OFトリガはWHEN条件をサポートしません。
現時点ではWHEN条件に副問い合わせを含めることはできません。
制約トリガでは、WHEN条件の評価は遅延されず、行の更新操作が行われた直後に発生することに注意してください。 この条件が真と評価されなかった場合、トリガは遅延実行ようのキューに入りません。
ユーザが提供する関数です。この関数は、引数を取らずtrigger型を返すよう定義します。トリガが起動した時に実行されます。
トリガ実行時に関数に渡される引数をカンマで区切ったリストです。このパラメータは省略可能です。 引数として指定するのは、リテラル文字列定数です。 単純な名前および数値定数を記述できますが、全て文字列に変換されます。 関数内でこれらの引数にアクセスする方法について調べるためには、トリガ関数を実装した言語の説明を参照してください。 通常の関数引数とは異なる場合があります。
テーブルにトリガを作成するには、ユーザがそのテーブルに対しTRIGGER権限を持っている必要があります。 またユーザはトリガ関数に対しEXECUTE権限を持たなければなりません。
トリガを削除するためにはDROP TRIGGERを使用してください。
列指定のトリガ（UPDATE OF column_name構文で定義されたトリガ）は、列挙された列のいずれかかがUPDATEコマンドのSETリスト内に対象として指定された場合に発行されます。 BEFORE UPDATEトリガにより行の内容になされた変更が考慮されないため、トリガが発行されない場合であっても、列の値を変更することができます。 反対に、UPDATE ... SET x = x ...のようなコマンドは、列の値が変更されていない場合であっても、x列に対するトリガは発行されます。
BEFOREトリガにおいてWHEN条件は関数が実行される、またはされそうな場合のみ評価されます。 このためWHENの使用はトリガ関数の先頭で同一の条件を試験することと実質違いはありません。 この条件で確認できるNEW行が現在の値であり、それまでのトリガで変更されている可能性があることに、特に注意して下さい。 またBEFOREトリガのWHEN条件では、まだ設定されていませんので、NEW行のシステム列(oidなど)を検査することができません。
AFTERトリガにおいて、WHEN条件は行の更新を行った直後に評価され、文の最後でトリガを発行するためにイベントを保持すべきかどうかを決定します。 このためAFTERトリガのWHEN条件は真を返さない場合、イベントを保持する必要もありませんし、文の最後の行を再度取り出す必要もありません。 これにより、トリガをわずかな行のみに対して発行する必要がある場合、多くの行を変更する文を非常に高速にすることができます。
PostgreSQL 7.3より前のバージョンでは、トリガ関数の戻り値の型を、trigger型ではなくプレースホルダであるopaque型として宣言する必要がありました。 古いダンプファイルのロードをサポートするため、CREATE TRIGGERではopaque型を返すよう宣言された関数を受け入れます。 しかし、注意を促すメッセージを表示し、宣言された関数の戻り値型をtriggerに変換します。
accountsテーブルの行が更新される直前にcheck_account_update関数を実行します。
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE PROCEDURE check_account_update();上と同じです。 しかし、balance列がUPDATEコマンドの対象として使用された場合のみ実行されます。
CREATE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE PROCEDURE check_account_update();以下の構文では、balance列が実際に変更された場合のみ関数が実行されます。
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE PROCEDURE check_account_update();何か変更された場合のみにaccountsの更新のログを取る関数を呼び出します。
CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE log_account_update();
ビューの背後にあるテーブルに行を挿入するために、各行に対してview_insert_row関数を実行します。
CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE PROCEDURE view_insert_row();
項36.4には、C言語で作成されたトリガ関数の完全な例があります。
PostgreSQLにおけるCREATE TRIGGER文は標準SQLのサブセットを実装したものです ただし、PostgreSQLには、次の機能がありません。
SQLでは、"old"や"new"、トリガによって起動する動作の定義が使用するテーブルに、別名を定義することができます。 （例えば、CREATE TRIGGER ... ON tablename REFERENCING OLD ROW AS somename NEW ROW AS othername ...）。 PostgreSQLでは、複数のユーザ定義言語でトリガプロシージャを作成できるので、データへのアクセスは言語固有の方法で扱われます。
PostgreSQLでは、文レベルのトリガにおいて古いテーブルと新しいテーブル、つまり、古い行のすべて、新しい行のすべてまたはその両方を持つテーブルを参照することができません。 これは標準SQLではOLD TABLEおよびNEW TABLE句により参照されます。
PostgreSQLでは、トリガ動作として、1つのユーザ定義関数の実行しか認めていません。 標準では、他のSQLコマンドを複数実行させることができます。 例えば、トリガ動作としてCREATE TABLEを実行させることも可能です。 この制限を回避する方法は簡単です。必要なコマンドを実行するユーザ定義関数を作成すればよいのです。
SQLでは、複数のトリガは、作成時刻順に起動すべきであると規定しています。 PostgreSQLでは名前順であり、より簡単に判断することができます。
SQLでは、数珠繋ぎの削除に対するBEFORE DELETEは、数珠繋ぎのDELETEが完了した後に発行するものと規定しています。 PostgreSQLでは、BEFORE DELETEは常に削除操作よりも後に行われます。 この方がより一貫性があると考えています。 また、参照整合性に関する動作により引き起こる更新を実行している間に、BEFOREトリガが行を更新し、更新を妨げるような場合の動作は非標準的です。 これは、制約違反となるかもしれませんし、参照整合性制約に合わないデータを格納してしまうかもしれません。
ORを使用して単一トリガに複数の動作を指定する機能は、標準SQLに対するPostgreSQLの拡張です。
TRUNCATEでのトリガ発行機能、および、ビューに対する文レベルトリガの定義機能は標準SQLに対するPostgreSQLの拡張です。
CREATE CONSTRAINT TRIGGERは標準SQLに対するPostgreSQLの拡張です。