Oracle PL/SQLトリガーのチュートリアル: 複合の代わりに [例]

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

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

トリガーの利点

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

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

トリガーの種類 Oracle

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

に基づく分類 タイミング
BEFORE トリガー: 指定されたイベントが発生する前に起動されます。
AFTER トリガー: 指定されたイベントが発生した後に起動されます。
INSTEAD OF トリガー: 特殊なタイプ。さらに詳しいトピックについて学びます。 (DML のみ)
に基づく分類 レベル
STATEMENT レベルのトリガー: 指定されたイベントステートメントに対して XNUMX 回起動されます。
ROW レベルのトリガー: 指定されたイベントで影響を受けた各レコードに対して起動されます。 (DMLのみ)
に基づく分類 イベント
DML トリガー: DML イベントが指定されたときに起動します (INSERT/UPDATE/DELETE)。
DDL トリガー: DDL イベントが指定されたときに起動します (CREATE/ALTER)。
DATABASE トリガー: データベースイベント (LOGON/LOGOFF/STARTUP/SHUTDOWN) が指定されたときに起動します。

したがって、各トリガーは上記のパラメーターの組み合わせです。

トリガーの作成方法

以下は、トリガーを作成するための構文です。

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」は、ROW レベルのトリガーを指定します。
WHEN 句は、トリガーを起動する必要がある追加の条件を指定します。
宣言部、実行部、例外処理部は他のものと同様です。 PL/SQLブロック。宣言部分と例外処理部分はオプションです。

:NEW 句と :OLD 句

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

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

:NEW – トリガーの実行中にベーステーブル/ビューの列の新しい値を保持します。
:OLD – トリガーの実行中にベーステーブル/ビューの列の古い値を保持します。

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

	INSERT	UPDATE	DELETE
：新しい	VALID	VALID	無効。削除の場合には新しい値はありません。
：古い	無効。挿入ケースに古い値はありません	VALID	VALID

トリガーの代わりに

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

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

例: この例では、2 つの基本テーブルから複雑なビューを作成します。

Table_1 は emp テーブルであり、

Table_2 は部門テーブルです。

次に、INSTEAD OF トリガーを使用して、この複雑なビューのロケーション詳細ステートメントの UPDATE を発行する方法を確認します。また、トリガーで :NEW と :OLD がどのように役立つかについても確認します。

ステップ 1: 適切な列を含むテーブル「emp」および「dept」を作成する
ステップ 2: テーブルにサンプル値を入力する
ステップ 3: 上記で作成したテーブルのビューを作成する
ステップ 4: 代わりのトリガーの前にビューを更新する
ステップ 5: 代わりのトリガーの作成
ステップ 6: 代わりのトリガー後のビューの更新

ステップ1） 適切な列を含むテーブル「emp」および「dept」を作成する

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));
/

コードの説明

コード行 1 ～ 7: テーブル「emp」の作成。
コード行 8 ～ 12: テーブル「dept」の作成。

出力

テーブルが作成されました

ステップ2） テーブルを作成したので、このテーブルにサンプル値を入力し、上記のテーブルのビューを作成します。

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,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

コードの説明

コード行 13 ～ 19: 「dept」テーブルにデータを挿入しています。
コード行 20 ～ 26: 「emp」テーブルにデータを挿入します。

出力

PL/SQLプロシージャ完成した

ステップ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;

コードの説明

コード行 27 ～ 32: 「guru99_emp_view」ビューの作成。

コード行 33: guru99_emp_view をクエリしています。

出力

作成されたビュー

従業員名	DEPT_NAME	ロケーション
ZZZ	HR	アメリカ
YYY	セール	UK
XXX	財務	日本

ステップ4） トリガーの代わりにビューを更新します。

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

コードの説明

コード行 34 ～ 38: 「XXX」の場所を「フランス」に更新します。例外が発生しました。 DMLステートメント複合ビューでは許可されません。

出力

ORA-01779: キーが保存されていない表にマップされる列は変更できません

ORA-06512: 2行目

ステップ5）前のステップでビューを更新するときに発生するエラーを回避するために、このステップでは「トリガーの代わりに」を使用します。

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;
/

コードの説明

コード行 39: ROW レベルの「guru99_emp_view」ビューで「UPDATE」イベントの INSTEAD OF トリガーを作成します。これには、ベーステーブル「dept」内の場所を更新する更新ステートメントが含まれています。
コード行 44: Update ステートメントは、「:NEW」と「:OLD」を使用して、更新前後の列の値を検索します。

出力

トリガーが作成されました

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

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

SELECT * FROM guru99_emp_view;

コードの説明：

コード行 49 ～ 53: 「XXX」の所在地を「フランス」に更新。「INSTEAD OF」トリガーがビュー上の実際の更新ステートメントを停止し、ベーステーブルの更新を実行したため、これは成功します。
コード行 55: 更新されたレコードを確認しています。

出力：

PL/SQLプロシージャが正常に完了しました

従業員名	DEPT_NAME	ロケーション
ZZZ	HR	アメリカ
YYY	セール	UK
XXX	財務	フランス

複合トリガー

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

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

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

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
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;

構文の説明:

上記の構文は、「COMPOUND」トリガーの作成を示しています。
宣言セクションは、トリガー本体内のすべての実行ブロックに共通です。
これら 4 つのタイミングブロックは任意の順序で実行できます。これら 4 つのタイミングブロックをすべて使用することは必須ではありません。必要なタイミングだけCOMPOUNDトリガーを作成することができます。

例: この例では、給与列にデフォルト値 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;

コードの説明：

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

出力：

トリガーが作成されました

PL/SQL プロシージャが正常に完了しました。

EMP_NAME	EMP_NO	給料	MANAGER	DEPT_NO
CCC	1004	5000	単4	30

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

トリガーは有効または無効にすることができます。トリガーを有効または無効にするには、トリガーを無効または有効にする ALTER (DDL) ステートメントを指定する必要があります。

以下は、トリガーを有効/無効にするための構文です。

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

構文の説明:

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

まとめ

この章では、PL/SQL トリガーとその利点について学びました。また、さまざまな分類を学習し、INSTEAD OF トリガーと COMPOUND トリガーについて説明しました。

よろしければ：

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

トリガーの利点

トリガーの種類 Oracle

トリガーの作成方法

関連記事

:NEW 句と :OLD 句

トリガーの代わりに

複合トリガー

まとめ