Oracle PL/SQLトリガー：&複合型の代わりに

PL/SQLのトリガーとは何ですか?

トリガーは保存されます PL / SQLの によって発火されるプログラム Oracle エンジンが自動的に DMLステートメント テーブルに対して挿入、更新、削除などの操作が実行されるとき、または何らかのイベントが発生したときにトリガーが実行されます。トリガーの場合に実行されるコードは、要件に応じて定義できます。トリガーを起動するイベントと実行タイミングを選択できます。トリガーの目的は、データベース上の情報の整合性を維持することです。

トリガーの利点

トリガーの利点は次のとおりです。

• 一部の派生列値を自動的に生成する
• 参照整合性の強制
• イベントのログ記録とテーブルアクセスに関する情報の保存
• 会計監査
• Syncテーブルの大量のレプリケーション
• セキュリティ権限の強制
• 無効なトランザクションの防止

トリガーの種類 Oracle

トリガーは次のパラメータに基づいて分類できます。

タイミングに基づく分類

• トリガー前: 指定されたイベントが発生する前に発火します。
• トリガー後: 指定されたイベントが発生した後に発火します。
• トリガーの代わりに: 特殊なタイプです。詳細は後述のトピックで説明します。（DML専用）

レベルに基づく分類

• ステートメントレベルのトリガー: 指定されたイベントステートメントに対して、1回だけ実行されます。
• 行レベルトリガー: 指定されたイベントで影響を受けたレコードごとに発生します。（DMLの場合のみ）

イベントに基づく分類

• DMLトリガー: DMLイベント（INSERT/UPDATE/DELETE）が指定されたときに発生します。
• DDLトリガー: DDLイベント（CREATE/ALTER）が指定されたときに発生します。
• データベーストリガー: データベースイベント（ログオン/ログオフ/起動/シャットダウン）が指定されたときに発生します。

つまり、各トリガーは上記のパラメータの組み合わせによって構成されます。

トリガーの作成方法

以下にトリガーを作成するための構文を示します。以下のスクリーンショットは、このトリガー作成構文を示しています。 Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

構文の説明:

• 上記の構文は、トリガーの作成時に存在するさまざまなオプションのステートメントを示しています。
• BEFORE/AFTERはイベントのタイミングを指定します。
• 挿入/更新/ログオン/作成など。 トリガーを起動する必要があるイベントを指定します。
• ON句は、前述のイベントが有効なオブジェクトを指定します。例えば、DMLトリガーの場合、これはDMLイベントが発生する可能性のあるテーブル名になります。
• 「FOR EACH ROW」コマンドは、行レベルのトリガーを指定します。
• WHEN句は、トリガーが作動する必要がある追加条件を指定します。
• 宣言部、実行部、例外処理部は他のものと同じです PL/SQLブロック宣言部分と 例外処理 一部はオプションです。

:NEW 句と :OLD 句

行レベルのトリガーでは、トリガーは関連する行ごとに起動されます。 また、DML ステートメントの前後の値を知る必要がある場合もあります。

Oracle 行レベルトリガーには、これらの値を保持するための2つの句が用意されています。これらの句を使用することで、トリガー本体内で古い値と新しい値を参照できます。

• ：新しい トリガーの実行中に、ベーステーブル／ビューの列に新しい値を保持します。
• ：古い トリガー実行中に、ベーステーブル／ビューの列の古い値を保持します。

この句は、DMLイベントに基づいて使用する必要があります。以下の表は、どの句がどのDMLステートメント（INSERT/UPDATE/DELETE）に対して有効であるかを示しています。

トリガーの代わりに

「INSTEAD OFトリガー」は特殊なタイプのトリガーです。DMLトリガーでのみ使用され、複雑なビューでDMLイベントが発生する場合に使用されます。

3つの基本テーブルからビューを作成する例を考えてみましょう。このビューに対してDMLイベントが発行されると、データが3つの異なるテーブルから取得されるため、ビューは無効になります。このような場合、INSTEAD OFトリガーが使用されます。INSTEAD OFトリガーは、指定されたイベントに対してビューを変更するのではなく、基本テーブルを直接変更するために使用されます。

例1： この例では、2つの基本テーブルから複雑なビューを作成します。ここで、Table_1は従業員テーブル、Table_2は部署テーブルです。

次に、INSTEAD OFトリガーを使用して、この複雑なビューのロケーション詳細を更新する方法を見ていきます。また、トリガーで:NEWと:OLDがどのように役立つかも見ていきます。この例は、以下の手順で行います。

• ステップ1：適切な列を持つテーブル「emp」と「dept」を作成する
• ステップ2：サンプル値でテーブルを埋める
• ステップ3：上記で作成したテーブルのビューを作成する
• ステップ4：INSTEAD OFトリガーの前にビューを更新する
• ステップ5：INSTEAD OFトリガーの作成
• ステップ6：INSTEAD OFトリガー後のビューの更新

ステップ1）適切な列を持つテーブル「emp」と「dept」を作成します。

以下のスクリーンショットは、「emp」と「dept」の基本テーブルの作成を示しています。 Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code 説明

• Code 1行目～7行目： テーブル「emp」の作成。
• Code 8行目～12行目： テーブル「dept」の作成。

出力：

Table Created

ステップ2） さて、テーブルを作成したので、次にサンプル値を入力してみます。

以下のスクリーンショットは、「dept」テーブルと「emp」テーブルに挿入されるサンプル行を示しています。

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code 説明

• Code 13行目～19行目： 「dept」テーブルにデータを挿入しています。
• Code 20行目～26行目： 「emp」テーブルにデータを挿入しています。

出力：

PL/SQL procedure completed

ステップ3） 上記で作成したテーブルのビューを作成します。

以下のスクリーンショットは、複雑なビューが作成され、その後クエリが実行されている様子を示しています。

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code 説明

• Code 27行目～32行目： 「guru99_emp_view」ビューを作成します。
• Code 33行目： guru99_emp_view をクエリしています。

出力：

View created

ステップ4） INSTEAD OFトリガーの前にビューを更新します。

以下のスクリーンショットは、複合ビューの更新試行と、その結果発生したエラーを示しています。

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code 説明

• Code 34行目～38行目： 「XXX」の場所を「FRANCE」に更新してください。複合ビューに対してDMLステートメントを直接実行することは許可されていないため、例外が発生しました。

出力：

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

ステップ5） 前の手順でビューを更新する際に発生したエラーを回避するため、この手順では「INSTEAD OF トリガー」を使用します。

以下のスクリーンショットは、INSTEAD OFトリガーの作成を示しています。

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code 説明

• Code 39行目： 行レベルで「guru99_emp_view」ビューの「UPDATE」イベントに対するINSTEAD OFトリガーを作成します。このトリガーには、ベーステーブル「dept」内の場所を更新するための更新ステートメントが含まれています。
• Code 44行目： 更新ステートメントでは、「:NEW」と「:OLD」を使用して、更新前と更新後の列の値を取得します。

出力：

Trigger Created

ステップ6） INSTEAD OFトリガー後のビューの更新。これでエラーは発生しなくなります。「INSTEAD OFトリガー」がこの複雑なビューの更新処理を処理するためです。コードが実行されると、従業員XXXの所在地が「日本」から「フランス」に更新されます。

以下のスクリーンショットは、INSTEAD OFトリガーによる更新が成功し、更新されたビューが表示されたことを示しています。

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code 説明：

• Code 49行目～53行目： 「XXX」の場所を「FRANCE」に更新します。「INSTEAD OF」トリガーがビューに対する実際の更新ステートメントを停止し、ベーステーブルの更新を実行したため、更新は成功しました。
• Code 55行目： 更新されたレコードを確認しています。

出力：

PL/SQL procedure successfully completed

複合トリガー

複合トリガーとは、単一のトリガー本体内で4つのタイミングポイントそれぞれに対してアクションを指定できるトリガーです。サポートされる4つのタイミングポイントは以下のとおりです。

• BEFORE STATEMENT – レベル
• BEFORE ROW – レベル
• AFTER ROW – レベル
• ステートメント後の – レベル

異なるタイミングのアクションを同じトリガーに組み合わせる機能を提供します。

以下のスクリーンショットは、4つのタイミングセクションを含む複合トリガー構文を示しています。

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

構文の説明:

• 上記の構文は、「複合」トリガーの作成方法を示しています。
• 宣言部分は、トリガー本体内のすべての実行ブロックに共通です。
• これら4つのタイミングブロックは、どのような順序でも構いません。4つすべてを用意する必要はありません。必要なタイミングのみに対応する複合トリガーを作成することも可能です。

例1： この例では、給与列にデフォルト値の5000を自動入力するトリガーを作成します。

以下のスクリーンショットは、複合トリガーの例とその出力を示しています。

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code 説明：

• Code 2行目～10行目： 複合トリガーを作成します。これは、BEFORE ROW レベルのタイミングに合わせて作成され、給与にデフォルト値 5000 を設定します。これにより、レコードをテーブルに挿入する前に、給与がデフォルト値「5000」に変更されます。
• Code 11行目～14行目： レコードを「emp」テーブルに挿入します。
• Code 16行目： 挿入されたレコードを検証しています。

出力：

Trigger created

PL/SQL procedure successfully completed.

トリガーの有効化と無効化

トリガーは有効化または無効化できます。トリガーを有効化または無効化するには、トリガーに対して、無効化または有効化を行うALTER（DDL）ステートメントを指定する必要があります。

以下に、トリガーを有効化／無効化するための構文を示します。

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

構文の説明:

• 最初の構文は、単一のトリガーを有効／無効にする方法を示しています。
• XNUMX 番目のステートメントは、特定のテーブルのすべてのトリガーを有効または無効にする方法を示しています。

よくあるご質問