このトピックでは、PL/SQLのトリガー関数について説明します。
データ変更のトリガー
PL/SQLでは、データ変更トリガーは、パラメーターを持たず、戻り値の型がtriggerの関数として宣言されます。 データ変更トリガーがCREATE triggerステートメントで指定されたいくつかのパラメーターを受け取る場合でも、データ変更トリガーはパラメーターなしで宣言する必要があります。 これらのパラメーターは、次のセクションで説明するように、TG_ARGVを使用して渡されます。
PL/SQL関数がトリガーとして呼び出されると、次の特殊変数がトップレベルブロックに自動的に作成されます。
NEW: RECORDデータ型の変数。 この変数は、行レベルのトリガーでINSERTおよびUPDATE操作の新しいデータ行を保持します。 この変数は、ステートメントレベルのトリガーおよびDELETE操作ではNULLです。
OLD: RECORDデータ型の変数。 この変数は、行レベルのトリガーでUPDATEおよびDELETE操作の古いデータ行を保持します。 この変数は、ステートメントレベルのトリガーおよびINSERT操作ではNULLです。
TG_NAME: NAMEデータ型の変数。 この変数は、実行されるトリガーの名前を保持します。
TG_WHEN: TEXTデータ型の変数。 この変数は、値がBEFORE、AFTER、またはINSTEAD OFの文字列を保持します。 値は、実行されるトリガーの定義によって異なります。
TG_LEVEL: TEXTデータ型の変数。 この変数は、値がROWまたはSTATEMENTの文字列を保持します。 値は、実行されるトリガーの定義によって異なります。
TG_OP: TEXTデータ型の変数。 この変数は、値がINSERT、UPDATE、DELETE、またはTRUNCATEの文字列を保持します。 値は、トリガーが発生した操作を示します。
TG_RELID: OIDデータ型の変数。 この変数には、トリガーが発生するテーブルのオブジェクトIDが保持されます。
TG_RELNAME: NAMEデータ型の変数。 この変数は、トリガーが実行されるテーブルの名前を保持します。 この変数は廃止され、将来のバージョンで削除される可能性があります。 この変数はTG_TABLE_NAME変数に置き換えられます。
TG_TABLE_NAME: NAMEデータ型の変数。 この変数は、トリガーが実行されるテーブルの名前を保持します。
TG_TABLE_SCHEMA: NAMEデータ型の変数。 この変数は、トリガーが実行されるテーブルのスキーマ名を保持します。
TG_NARGS: INTEGERデータ型の変数。 この変数は、CREATE triggerステートメントでトリガー関数に提供されるパラメーターの数を保持します。
TG_ARGV[]: TEXTデータ型の変数。 この変数は、CREATE TRIGGERステートメントのパラメーターを保持します。 インデックスは0からカウントされます。 値が0未満またはTG_NARGS変数の値以上のインデックスは無効です。 この場合、NULLが返されます。
トリガー関数は、トリガーが実行されるテーブルのスキーマと完全に一致するNULLまたはレコードまたは行の値を返す必要があります。
行レベルのBEFOREトリガーは、NULLを返して、行に対する後続の操作をスキップするようにトリガーマネージャーに指示できます。 たとえば、後続のトリガーは起動されず、この行に対してINSERT、UPDATE、DELETE操作は実行されません。 NULL以外の値が返された場合、この行の操作が続行されます。 返された行の値がNEW変数の元の値と異なる場合、挿入または更新される行が変更されます。 したがって、行の値を変更せずにトリガー関数を使用してアクションをトリガーする場合は、NEW変数の元の値または別の同等の値を返す必要があります。 保存する行を変更する場合は、NEW変数の単一の値を直接置き換えて変更したNEW変数を返すか、新しいレコードまたは行を作成して返すことができます。 DELETE操作のBEFOREトリガーの戻り値は直接影響しません。 ただし、トリガーアクションを続行するには、戻り値がNULL以外の値である必要があります。 DELETE操作のトリガーでは、NEW変数の値はNULLです。 ほとんどの場合、DELETE操作のトリガーはOLD変数の値を返します。
INSTEAD OFトリガーは常に行レベルのトリガーであり、ビューにのみ使用できます。 戻り値がNULLの場合は、更新が実行されず、行に対する後続の操作をスキップできることを示します。 この場合、後続のトリガーは起動されず、周囲のINSERT、UPDATE、またはDELETE操作によって行が影響を受ける行としてカウントされません。 NULL以外の戻り値は、要求された操作がトリガーによって実行されたことを示します。 NEW変数の値は、INSERTおよびUPDATE操作に対して返されます。 トリガ関数は、INSERT RETURNINGおよびUPDATE RETURNING操作をサポートするように変数の値を変更することができる。 この変更は、後続のトリガーに渡される行値、またはON CONFLICT DO UPDATE句を持つINSERTステートメント内の特別なEXCLUDEDエイリアス参照にも影響します。 OLD変数の値は、DELETE操作のために返されます。
AFTER行レベルトリガー、BEFOREステートメントレベルトリガー、またはAFTERステートメントレベルトリガーの戻り値は、常に無視されるか、NULLになります。 ただし、これらのトリガーはすべて、エラーを発生させることで操作全体を中止できます。
サンプルPL/SQLトリガー関数
次のサンプルトリガーは、行が挿入または更新されたときに、テーブル内の行にユーザー名と時間をスタンプする方法の例を示しています。 このトリガーは、従業員の名前が提供されているかどうか、および従業員の給与が正の値であるかどうかもチェックします。
テーブルempを作成 (
empnameテキスト、
給与整数、
last_dateタイムスタンプ、
last_userテキスト
);
empを挿入または更新する前にTRIGGER emp_stampを作成する
FOR EACH ROW
BEGIN
-- empnameとsalaryの値がNULLでないことを確認します。
NEW.empnameがNULLの場合
RAISE EXCEPTION 'empnameをnullにすることはできません';
END IF;
NEW.salaryがNULL THENの場合
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-彼らがそれを支払わなければならないとき、誰が私たちのために働きますか?
もし新しい。サラリー <0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-給与が変更された時間と給与を変更したユーザーを記録します。
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
戻る新しい;
エンド; 新しいテーブルを作成して、テーブルに対する各INSERT、UPDATE、またはDELETE操作の行を保持することもできます。 このメソッドを使用して、テーブルの変更を監査できます。
監査用のサンプルPL/SQLトリガー関数
次のサンプルトリガーでは、emp_auditテーブルを使用して、INSERT、UPDATE、およびDELETE操作をempテーブルに記録する方法の例を示します。 このようにして、empテーブルへの変更が監査されます。 現在の時刻、ユーザー名、および操作タイプは、emp_auditテーブルの各行にスタンプされます。
テーブルempを作成 (
empnameテキストNOT NULL,
給与整数
);
CREATE TABLE emp_audit (
演算char (1) NOT NULL,
スタンプタイムスタンプNOT NULL,
useridテキストNOT NULL,
empnameテキストNOT NULL,
給与整数
);
トリガーを作成するemp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
BEGIN
--
-- emp_auditテーブルに行を作成して、empテーブルで実行された操作を記録します。
-- 特殊な変数TG_OPを使用して操作を取得できます。
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
ELSIF (TG_OP = 'UPDATE')
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
ELSIF (TG_OP = 'INSERT')
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
END IF;
RETURN NULL; − AFTERトリガの結果を無視する。
エンド; ビューを使用してメインテーブルを監査テーブルに結合し、各エントリが変更された時刻を表示することもできます。 このようにして、テーブルへの変更に対して完全な監査証跡が実行され、監査証跡の簡略化されたビューが提供されます。 このビューには、各エントリに最後の変更が加えられたときのタイムスタンプのみが表示されます。
監査用のサンプルPL/SQLビュートリガー関数
この例では、トリガーを使用してビューを更新し、ビュー内の行に対するINSERT、UPDATE、またはDELETE操作がemp_auditテーブルに記録されるようにします。 現在の時刻、ユーザー名、および操作タイプが記録され、行が最後に変更されたときのタイムスタンプがビューに表示されます。
テーブルempを作成 (
empnameテキストPRIMARY KEY,
給与整数
);
CREATE TABLE emp_audit (
演算char (1) NOT NULL,
useridテキストNOT NULL,
empnameテキストNOT NULL,
給与整数、
スタンプタイムスタンプNOT NULL
);
ビューを作成するemp_view AS
SELECT e.empname,
e. サラリー、
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
1、2によるグループ;
トリガーを作成するemp_audit
emp_viewの挿入または更新または削除の代わりに
FOR EACH ROW
BEGIN
--
-- empテーブルで要求された操作を実行し、emp_auditテーブルに行を作成して操作を記録します。
--
IF (TG_OP = 'DELETE') THEN
empから削除する場所empname = OLD.empname;
見つからなかった場合はリターンNULL; 終了IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', user, OLD.*);
古いリターン;
ELSIF (TG_OP = 'UPDATE')
UPDATE empSETサラリー=NEW。サラリーWHERE empname = OLD.empname;
見つからなかった場合はリターンNULL; 終了IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', user, NEW.*);
戻る新しい;
ELSIF (TG_OP = 'INSERT')
emp値に挿入する (NEW.empname、NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', user, NEW.*);
戻る新しい;
END IF;
エンド; トリガーを使用して、別のテーブルのサマリーテーブルを維持することもできます。 集計テーブルは、クエリを高速化するために、一部のクエリの元のテーブルの代わりに使用できます。 これは、測定または観測されたデータを含むファクトテーブルが非常に大きい場合があるデータウェアハウスで一般的に使用されます。
サマリーテーブルを維持するためのサンプルPL/SQLトリガー関数
次のスキーマは、Ralph KimballのThe Data Warehouse ToolkitのGrocery Storeの例に一部基づいています。
-- メインテーブルtime_dimensionとsales_fact。
--
TABLE time_dimensionの作成 (
time_key整数NOT NULL,
day_of_week整数NOT NULL,
day_of_month整数NOT NULL,
月整数NOT NULL,
4分の1整数NOT NULL,
年整数NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
テーブルsales_factの作成 (
time_key整数NOT NULL,
product_key整数NOT NULL,
store_key整数NOT NULL,
amount_sold numeric(12,2) NOT NULL、
units_sold整数NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- サマリーテーブルsales_summary_bytime。
--
CREATE TABLE sales_summary_bytime (
time_key整数NOT NULL,
amount_sold numeric(15,2) NOT NULL、
units_sold numeric (12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime ON sales_summary_bytime(time_key);
--
-- INSERT、UPDATE、およびDELETE操作が実行されたときに、サマリーテーブルの列を変更するために使用される関数とトリガー。
--
トリガーの作成maint_sales_summary_bytime
sales_factの挿入または更新または削除後
FOR EACH ROW
DECLARE
delta_time_key整数;
delta_amount_sold数値 (15,2);
delta_units_sold数値 (12);
delta_amount_cost数値 (15,2);
BEGIN
-増分または減分の量を計算します。
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE')
-- time_keyフィールドを変更する更新を禁止します。
-ほとんどの変更はDELETEおよびINSERT操作を使用して実行されるため、これは面倒ではありません。
IF ( OLD.time_key != NEW.time_key)
RAISE EXCEPTION 'time_keyの更新: % -> % not allowed' 、
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT')
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-サマリーテーブルの新しい値を含む行を挿入または更新します。
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_updateが見つかったとき。
BEGIN
sales_summary_bytimeに挿入 (
time_key,
amount_sold,
units_sold,
amount_cost)
値 (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
UNIQUE_VIOLATIONが残ったとき
-何もしないでください。
END;
エンドループinsert_update;
RETURN NULL;
END;
sales_fact値に挿入 (1,1、1,10、3,15);
sales_fact値に挿入 (1,2、1,20、5,35);
sales_fact値に挿入 (2,2、1,40、15,135);
sales_fact値に挿入 (2,3、1,10、1,13);
SELECT * からsales_summary_bytime;
sales_fact WHERE product_key = 1から削除します。
SELECT * からsales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * からsales_summary_bytime; AFTERトリガーは、遷移テーブルを使用して、トリガーステートメントによって変更された行のセット全体を検査することもできます。 CREATE TRIGGERステートメントは、1つまたは2つの遷移テーブルに名前を割り当てます。 次に、関数はそれらの名前を、読み取り専用の一時テーブルであるかのように参照できます。
遷移テーブルは、監査に使用することができる。
次の例では、行ごとに起動するトリガーを使用せずに監査するためのサンプルPL/SQLトリガー関数と同じ結果を生成します。 次の例では、遷移テーブルの関連情報が収集され、ステートメントごとにトリガーが1回実行されます。 これは、ステートメントを呼び出して複数の行を変更する必要がある場合、行トリガーアプローチよりも高速です。 REFERENCING句は、ケースごとに異なる必要があることに注意してください。 したがって、イベントの種類ごとにトリガーを宣言する必要があります。 単一のトリガー関数を使用できます。 ただし、TG_OP変数のテストを防ぐために、3つの別々のトリガー関数を使用することをお勧めします。
テーブルempを作成 (
empnameテキストNOT NULL,
給与整数
);
CREATE TABLE emp_audit (
演算char (1) NOT NULL,
スタンプタイムスタンプNOT NULL,
useridテキストNOT NULL,
empnameテキストNOT NULL,
給与整数
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURN TRIGGER IS
BEGIN
--
-- emp_auditテーブルに行を作成して、empテーブルで実行された操作を記録します。
-- 特殊な変数TG_OPを使用して操作を取得できます。
--
IF (TG_OP = 'DELETE') THEN
emp_auditに挿入する
SELECT 'D', now(), user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE')
emp_auditに挿入する
SELECT 'U', now(), user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT')
emp_auditに挿入する
SELECT 'I', now(), user, n.* FROM new_table n;
END IF;
RETURN NULL; − AFTERトリガの結果を無視する。
END;
トリガーの作成emp_audit_ins
empに挿入した後
new_tableとしての新しいテーブルの参照
各ステートメント実行機能process_emp_audit();
トリガーの作成emp_audit_upd
empで更新した後
古いテーブルをold_tableとして参照する新しいテーブルとしてnew_table
各ステートメント実行機能process_emp_audit();
トリガーを作成するemp_audit_del
empを削除した後
古いテーブルをold_tableとして参照
各ステートメント実行機能process_emp_audit(); イベントのトリガー
PL/SQLでは、イベントトリガーを定義できます。 PolarDB for PostgreSQL (Oracleと互換) では、イベントトリガーとして呼び出される関数は、パラメーターがなく、戻り値の型がevent_triggerの関数として宣言する必要があります。
PL/SQL関数がイベントトリガーとして呼び出されると、次の特殊変数が最上位ブロックに自動的に作成されます。
TG_EVENT: TEXTデータ型の変数。 この変数には、トリガーが発生するイベントを示す文字列が保持されます。
TG_TAG: TEXTデータ型の変数。 この変数は、トリガーが起動されるコマンドタグを保持します。
次のサンプルトリガーは、PL/SQLでイベントトリガー関数を使用する方法の例を示しています。
この例では、トリガーを使用して、サポートされているコマンドを実行するたびにNOTICEメッセージを生成します。
CREATE OR REPLACE FUNCTIONスニッチ () RETURN event_trigger IS
BEGIN
RAISE NOTICE 'snitch: % % '、tg_event、tg_tag;
END;
イベントトリガースニッチを作成ddl_command_start実行機能スニッチ ();