Clever Geek Handbook

Oracleのトリガーについて

伝統的に、記事は論文で書かれています。 より詳細なコンテンツは、記事の最後に添付されているビデオに記載されており、Oracleトリガーについての講義記録が付いています。





トリガーの概要



トリガーは、データベースに保存される名前付きのpl / sqlブロックです。





トリガー分類：





トリガーを使用する理由：









plsql_trigger_sourceはそのような構成体です。





構文simple_dml_trigger、instead_of_dml_trigger、compound_dml_trigger 、 system_triggerは、記事の対応するセクションに記載されています。



DMLトリガー







Simple_dml_triggerコンストラクト：



ここで、 dml_event_clause：



referencing_clause：



trigger_edition_clause：



trigger_body：





添付オブジェクトに応じて、次のように分類されます。





起動イベント別：





スコープ別：





応答時間別：





クロスエディショントリガー - エディション間の相互作用に使用されます。たとえば、新しいエディションにないフィールドから他のフィールドにデータを転送および変換します。



トリガーが機能した操作を決定する条件述語：

述語 説明
挿入 挿入操作でトリガーが起動した場合はTrue
更新中 更新操作でトリガーが起動した場合はTrue
更新（「列」） 特定のフィールドに影響する更新操作でトリガーが起動した場合はTrue
削除中 削除操作でトリガーが起動された場合はTrue




これらの述語は、ブール式を使用できる場所であればどこでも使用できます。



例 
CREATE OR REPLACE TRIGGER t BEFORE INSERT OR UPDATE OF salary, department_id OR DELETE ON employees BEGIN CASE WHEN INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting'); WHEN UPDATING('salary') THEN DBMS_OUTPUT.PUT_LINE('Updating salary'); WHEN UPDATING('department_id') THEN DBMS_OUTPUT.PUT_LINE('Updating department ID'); WHEN DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting'); END CASE; END;






擬似テープ



変更されるレコードのフィールドにアクセスし、変更前のフィールドの値と変更後のフィールドの値を取得できる疑似レコードがあります。 これらは、古いエントリと新しいエントリです。 参照構造を使用して、名前を変更できます。 これらのエントリの構造は、tablename％rowtypeです。 これらのレコードは、行レベルトリガーまたは複合トリガー（レコードレベルセクションを含む）のみにあります。

トリガー操作 OLD.column NEW。コラム
挿入 ヌル 新しい価値
更新する 古い意味 新しい価値
削除する 古い意味 ヌル




制限：





dmlトリガーの代わりに







代わりにdml_triggerコンストラクト：









例 
 CREATE OR REPLACE VIEW order_info AS SELECT c.customer_id, c.cust_last_name, c.cust_first_name, o.order_id, o.order_date, o.order_status FROM customers c, orders o WHERE c.customer_id = o.customer_id; CREATE OR REPLACE TRIGGER order_info_insert INSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Duplicate customer or order ID'); END order_info_insert;






ビューのネストされたテーブル列のトリガーの代わり



ネストされたテーブルビューのトリガーを作成できます。 このようなトリガーには、追加の疑似レコードもあります-親は、ビューのレコード全体を参照します（古い疑似レコードと新しい疑似レコードは、ネストされたテーブルのエントリのみを参照します）



そのようなトリガーの例 
 -- Create type of nested table element: CREATE OR REPLACE TYPE nte AUTHID DEFINER IS OBJECT ( emp_id NUMBER(6), lastname VARCHAR2(25), job VARCHAR2(10), sal NUMBER(8,2) ); / -- Created type of nested table: CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte; / -- Create view: CREATE OR REPLACE VIEW dept_view AS SELECT d.department_id, d.department_name, CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary FROM employees e WHERE e.department_id = d.department_id ) AS emp_list_ ) emplist FROM departments d; -- Create trigger: CREATE OR REPLACE TRIGGER dept_emplist_tr INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view REFERENCING NEW AS Employee PARENT AS Department FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table: INSERT INTO employees ( employee_id, last_name, email, hire_date, job_id, salary, department_id ) VALUES ( :Employee.emp_id, -- employee_id :Employee.lastname, -- last_name :Employee.lastname || '@company.com', -- email SYSDATE, -- hire_date :Employee.job, -- job_id :Employee.sal, -- salary :Department.department_id -- department_id ); END;






トリガー挿入ステートメントをトリガーする 
 INSERT INTO TABLE ( SELECT d.emplist FROM dept_view d WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000);






複合DMLトリガー



バージョン11Gで導入されたこれらのトリガーには、すべてのタイプのDMLトリガーの処理が1つのブロックに含まれています。

Compound_dml_triggerコンストラクト：





ここで、 compound_trigger_block：





タイミングポイントセクション：





タイミングポイント：





tps_body：







主に使用されます：





複合トリガー構造



トリガーをトリガーしたステートメントの実行中に存在する変数を含む場合があります。

このようなトリガーには、次のセクションが含まれます。



これらのトリガーには初期化セクションはありませんが、これらの目的にはbeforeステートメントセクションを使用できます。

トリガーにbeforeステートメントセクションもafterステートメントセクションもなく、ステートメントがレコードに影響しない場合、そのようなトリガーは機能しません。



制限：





例 
 create or replace trigger tr_table_test_compound for update or delete or insert on table_test compound trigger v_count pls_integer := 0; before statement is begin dbms_output.put_line ( 'before statement' ); end before statement; before each row is begin dbms_output.put_line ( 'before insert' ); end before each row; after each row is begin dbms_output.put_line ( 'after insert' ); v_count := v_count + 1; end after each row; after statement is begin dbms_output.put_line ( 'after statement' ); end after statement; end tr_table_test_compound;






DMLトリガーを定義するための基本的な規則







DMLトリガーの制限







突然変異エラー表ORA-04091





行レベルのトリガーでターゲット表のデータを取得または変更しようとすると、Oracleはそれを許可せず、エラーORA-04091をスローします。

この問題を回避するには、次の手法を使用します。





システムトリガー



System_triggerコンストラクト：



このようなトリガーは、スキーマまたはデータベース全体のいずれかに適用されます。



システムトリガーが起動する時点にはいくつかのオプションがあります。





スキーマトリガー







トリガーの例 
 CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END;






データベーストリガー







トリガーの例 
 CREATE OR REPLACE TRIGGER check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack); END;






トリガーを作成する代わりに







トリガーの例 
 CREATE OR REPLACE TRIGGER t INSTEAD OF CREATE ON SCHEMA BEGIN EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)'; END;






システムトリガー属性



属性 戻り値とタイプ
ora_client_ip_address Varchar2

クライアントIPアドレス

例： 
 IF (ora_sysevent = 'LOGON') THEN v_addr := ora_client_ip_address; END IF;
ora_database_name Varchar2（50）

データベース名

例： 
 v_db_name := ora_database_name;
ora_des_encrypted_pa​​ssword Varchar2

作成または変更されたDES暗号化ユーザーパスワード

例： 
 IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table VALUES (ora_des_encrypted_password); END IF;
ora_dict_obj_name Varchar2（30）

DDL操作が実行されるオブジェクトの名前



例： 
 INSERT INTO event_table VALUES ('Changed object is ' || ora_dict_obj_name);
ora_dict_obj_name_list（

name_list OUT ora_name_list_t

）

Pls_integer

コマンドによって変更されたオブジェクトの数

Name_list-コマンドによって変更されたオブジェクトのリスト



例： 
 IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(name_list); END IF;
ora_dict_obj_owner Varchar2（30）

DDL操作の所有者



例： 
 INSERT INTO event_table VALUES ('object owner is' || ora_dict_obj_owner);
ora_dict_obj_owner_list（

owner_list OUT ora_name_list_t

）

Pls_integer

チームによって変更されたオブジェクトの所有者の数

Owner_list-コマンドによって変更されたオブジェクトの所有者のリスト



例： 
 IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(owner_list); END IF;
ora_dict_obj_type Varchar2（20）

ddl操作が実行されるオブジェクトのタイプ



例： 
 INSERT INTO event_table VALUES ('This object is a ' || ora_dict_obj_type);
ora_grantee（

user_list OUT ora_name_list_t

）

Pls_integer

付与操作に参加しているユーザーの数

User_list-これらのユーザーのリスト



例： 
 IF (ora_sysevent = 'GRANT') THEN number_of_grantees := ora_grantee(user_list); END IF;
ora_instance_num 数

インスタンス番号



例： 
 IF (ora_instance_num = 1) THEN INSERT INTO event_table VALUES ('1'); END IF;
ora_is_alter_column（

column_name IN VARCHAR2

）

ブール値

指定されたフィールドが変更操作によって変更された場合はtrue。 そうでなければ偽



例： 
 IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN alter_column := ora_is_alter_column('C'); END IF;
ora_is_creating_nested_table ブール値

現在のイベントがネストされたテーブルの作成である場合はtrue。 そうでなければ偽



例： 
 IF (ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND ora_is_creating_nested_table) THEN INSERT INTO event_table VALUES ('A nested table is created'); END IF;
ora_is_drop_column（

column_name IN VARCHAR2

）

ブール値

指定されたフィールドが削除された場合はtrue。 そうでなければ偽



例： 
 IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN drop_column := ora_is_drop_column('C'); END IF;
ora_is_servererror（

error_number IN VARCHAR2

）

ブール値

error_number番号で例外がスローされた場合はtrue。 そうでなければ偽



例： 
 IF ora_is_servererror(error_number) THEN INSERT INTO event_table VALUES ('Server error!!'); END IF;
ora_login_user Varchar2（30）

現在のユーザー名



例： 
 SELECT ora_login_user FROM DUAL;
ora_partition_pos Pls_integer

テーブルを作成するためのトリガーの代わりに、パーティション構造を挿入できるコマンドのSQLテキスト内の位置



例： 
 -- Retrieve ora_sql_txt into sql_text variable v_n := ora_partition_pos; v_new_stmt := SUBSTR(sql_text,1,v_n - 1) || ' ' || my_partition_clause || ' ' || SUBSTR(sql_text, v_n));
ora_privilege_list（

privilege_list OUT ora_name_list_t

）

Pls_integer

付与または取り消し操作に関係する特権の数

Privilege_list-これらの特権のリスト



例： 
 IF (ora_sysevent = 'GRANT' OR ora_sysevent = 'REVOKE') THEN number_of_privileges := ora_privilege_list(privilege_list); END IF;
ora_revokee（

user_list OUT ora_name_list_t

）

Pls_integer

取り消し操作に参加しているユーザーの数

User_list-これらのユーザーのリスト



例： 
 IF (ora_sysevent = 'REVOKE') THEN number_of_users := ora_revokee(user_list); END IF;
ora_server_error（

PLS_INTEGERの位置

）

数

指定された位置のエラースタックのエラーコード。1はスタックの先頭



例： 
 INSERT INTO event_table VALUES ('top stack error ' || ora_server_error(1));
ora_server_error_depth Pls_integer

エラースタック内のエラーメッセージの数



例： 
 n := ora_server_error_depth; -- Use n with functions such as ora_server_error
ora_server_error_msg（

PLS_INTEGERの位置

）

Varchar2

指定された場所のエラースタックのエラーメッセージ



例： 
 INSERT INTO event_table VALUES ('top stack error message' || ora_server_error_msg(1));
ora_server_error_num_params（

PLS_INTEGERの位置

）

Pls_integer

指定されたエラースタック位置で置き換えられた行の数（％s形式を使用）



例： 
 n := ora_server_error_num_params(1);
ora_server_error_param（

PLS_INTEGERの位置、

param IN PLS_INTEGER

）

Varchar2

指定された位置のエラースタックにあるエラーメッセージの置換テキスト（アカウント置換テキストで返されるパラメーター）



例： 
 -- Second %s in "Expected %s, found %s": param := ora_server_error_param(1,2);
ora_sql_txt（

sql_text OUT ora_name_list_t

）

Pls_integer

pl / sql sql_textコレクション内の要素の数。

sql_textパラメーター自体は、トリガーが機能したコマンドのテキストを返します



例： 
 CREATE TABLE event_table (col VARCHAR2(2030)); DECLARE sql_text ora_name_list_t; n PLS_INTEGER; v_stmt VARCHAR2(2000); BEGIN n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP v_stmt := v_stmt || sql_text(i); END LOOP; INSERT INTO event_table VALUES ('text of triggering statement: ' || v_stmt); END;
ora_sysevent Varchar2（20）

トリガーが発動するチームの名前



例： 
 INSERT INTO event_table VALUES (ora_sysevent);
ora_with_grant_option ブール値

付与オプションで特権が付与されている場合はtrue。 そうでない場合はfalse。



例： 
 IF (ora_sysevent = 'GRANT' AND ora_with_grant_option = TRUE) THEN INSERT INTO event_table VALUES ('with grant option'); END IF;
ora_space_error_info（

error_number OUT NUMBER、

error_type OUT VARCHAR2、

object_owner OUT VARCHAR2

table_space_name OUT VARCHAR2

object_name OUT VARCHAR2、

sub_object_name OUT VARCHAR2

）

ブール値

スペース不足が原因でエラーが発生した場合はtrue。 出力パラメーターでは、オブジェクトに関する情報。



例： 
 IF (ora_space_error_info ( eno,typ,owner,ts,obj,subobj) = TRUE) THEN DBMS_OUTPUT.PUT_LINE('The object '|| obj || ' owned by ' || owner || ' has run out of space.'); END IF;




システムトリガーイベント



イベント 説明 利用可能な属性
起動後 データベースを起動するとき。 DBレベルでのみ発生します。 エラーが発生すると、システムログに書き込みます。 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

シャットダウンする前に サーバーがシャットダウンプロセスを開始する前。 DBレベルでのみ発生します。 エラー時には、システムログに書き込みます。 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

DB_ROLE_CHANGEの後 ロールをスタンバイからプライマリに、またはプライマリからスタンバイに変更した後に初めてデータベースを起動するとき。

Data Guard構成でのみ使用され、DBレベルでのみ発生します

ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

サーバーエラー後 エラーが発生した場合（条件がある場合、条件で指定されたエラーでのみ機能します）。 トリガーの本体でエラーが発生すると、それ自体を再帰的に呼び出しません。 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_server_error

ora_is_servererror

ora_space_error_info

変更前



変更後

alterコマンドでオブジェクトが変更された場合 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_type

ora_dict_obj_name

ora_dict_obj_owner

ora_des_encrypted_pa​​ssword

（ALTER USERイベントの場合）

ora_is_alter_column

（ALTER TABLEイベントの場合）

ora_is_drop_column

（ALTER TABLEイベントの場合）

ドロップする前に



ドロップ後

オブジェクトを削除するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_type

ora_dict_obj_name

ora_dict_obj_owner

分析する前に



分析後

分析コマンドがトリガーされたとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

関連統計の前に



アソシエイト統計後

関連統計コマンドを実行するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

ora_dict_obj_name_list

ora_dict_obj_owner_list

審査前



監査後



NOAUDITの前に



ノーディット後

auditまたはnoauditを実行する場合 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

コメントする前に



コメント後

オブジェクトにコメントを追加するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

作成前



作成後

オブジェクトを作成するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_type

ora_dict_obj_name

ora_dict_obj_owner

ora_is_creating_nested_table

（CREATE TABLEイベントの場合）

DDLの前に



DDL後

データベースの変更、制御ファイルの作成、データベースの作成を除く、ほとんどのDDLコマンドで機能します。 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

統計を分離する前に



統計分離後

disassociate statisticsコマンドを実行するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

ora_dict_obj_name_list

ora_dict_obj_owner_list

付与前



付与後

grantコマンドを発行するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

ora_grantee

ora_with_grant_option

ora_privilege_list

ログオフ前 ユーザーが切断する前に機能し、回線レベルまたはデータベースで発生します ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ログオン後 ユーザーがデータベースへの接続を正常に確立した後に機能します。 エラーが発生すると、ユーザーはエントリを拒否されます。 SYSには無効です。 ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_client_ip_address

名前変更前



名前変更後

renameコマンドを実行するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_owner

ora_dict_obj_type

取り消す前に



取り消し後

取り消しコマンドを実行するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner

ora_revokee

ora_privilege_list

中断後 サーバーエラー（メモリ不足）が原因でsqlコマンドが中断された場合に機能します。

この場合、トリガーは条件を変更してコマンドの実行を再開する必要があります）

ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_server_error

ora_is_servererror

ora_space_error_info

切り捨てる前に



切り捨て後

truncateコマンドを実行するとき ora_sysevent

ora_login_user

ora_instance_num

ora_database_name

ora_dict_obj_name

ora_dict_obj_type

ora_dict_obj_owner





トリガーのコンパイル



トリガーの作成コマンド中にエラーが発生した場合、トリガーは作成されますが、無効な状態になります。 この場合、そのようなトリガーがハングしているオブジェクトに対して操作（トリガーをトリガーする必要がある操作）を実行しようとすると、すべて失敗します。 これは次の場合には適用されません。



トリガーを再コンパイルするには、alter triggerコマンドを使用します。



トリガー例外



トリガーで例外が発生した場合、操作全体がロールバックされます（トリガー内で行われた変更を含む）。 これの例外：





トリガー順序



Trigger_ordering_clauseコンストラクト：





  1. まず、すべての前のステートメントトリガー
  2. 次に、トリガーの各行の前のすべて
  3. 結局、各行トリガーの後
  4. そして最後に、トリガーステートメントの後のすべて


同時に起動するトリガーの実行順序を明示的に指定するには（この順序はデフォルトでは定義されていないため）、次を使用し、構造を先行させます。



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



これは、たとえば、大量の情報をテーブルに読み込むために必要になる場合があります。

次のコマンドを使用して、トリガーを有効/無効にできます。 

 ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };


テーブルのすべてのトリガーを一度に有効/無効にするには： 

 ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;




トリガーを変更するには、トリガーの作成または置換コマンドを使用するか、最初にドロップトリガーを削除してからトリガーを再作成します。

トリガーの変更操作では、トリガーの有効化/無効化、コンパイル、または名前の変更のみが可能です。

トリガーのコンパイル： 

 alter trigger TRIGGER_NAME compile;




トリガー操作の権利





トリガーを使用するには、スキーム内であっても、トリガーの作成権限が必要です。これにより、作成、変更、削除の権限が付与されます。 

 grant create trigger to USER;


他のすべてのスキームでトリガーを操作するには、* anyトリガー権限が必要です。 作成、変更、削除の権利は個別に付与されることに注意してください。 

 grant create any trigger to USER; grant alter any trigger to USER; grant drop any trigger to USER;


DATABASEレベルでシステムトリガーを操作するには、ADMINISTER DATABASE TRIGGER権限が必要です。 

 grant ADMINISTER DATABASE TRIGGER to USER;




トリガー情報を含むデータの辞書：







この記事の執筆に基づいた講義のビデオ録画：







オラクルのトピックに関する他の多くのビデオは、このチャンネルで見つけることができます： www.youtube.com/c/MoscowDevelopmentTeam



その他のOracleの記事



Oracleのコレクションについて


More articles:


All Articles