• Oracleトリガーの概要
• DMLトリガー
  
  • 擬似テープ
  • dmlトリガーの代わりに
  • ビューのネストされたテーブル列のトリガーの代わり。
  • 複合DMLトリガー
    
    • 複合トリガー構造
  • DMLトリガーを定義するための基本的な規則
  • DMLトリガーの制限
  • 突然変異エラー表ORA-04091
• システムトリガー
  
  • スキーマトリガー
  • データベーストリガー
  • トリガーを作成する代わりに
  • システムトリガー属性
  • システムトリガーイベント
• トリガーのコンパイル
• トリガー例外
• トリガー順序
• トリガーの有効化/無効化
• トリガー操作の権利
• 情報辞書のトリガー

トリガーの概要

• 自分でトリガーを呼び出すことはできません。トリガーは常に特定のイベントでのみ自動的に起動します（有効になっている場合）
• 再帰トリガーを作成しないでください。 つまり、たとえば、更新後のトリガーで、同じテーブルの更新が実行されます。 この場合、トリガーはメモリがなくなるまで再帰的に起動します。

• DMLトリガー（テーブルまたはビューごと）
• システムトリガー（回線またはデータベースごと）
• 条件付きトリガー（when条件を持つトリガー）
• トリガーの代わりに（作成コマンドのdmlビュートリガーまたはシステムトリガー）

• 仮想フィールド値を自動的に生成するには
• ロギング用
• 統計を収集するには
• ビューがdml操作に関係している場合、テーブルのデータを変更するには
• 特定の時間にdml操作を防ぐには
• テーブルの作成時に確立された記述的制約では実装できない複雑なデータ整合性制約を実装するには
• あらゆる種類の監査を整理するには
• データベース内の情報を変更した場合の対処方法について他のモジュールに通知するには
• ビジネスロジックを実装するには
• データベーステーブルでカスケードアクションを整理するため
• データベースまたはスキーマのシステムイベントに応答するには

DMLトリガー

• DMLトリガーは、テーブルまたはビューに対して作成され、レコードの挿入、更新、または削除時にトリガーされます。
• トリガーは、テーブルが定義されているスキーマとは異なるスキーマで作成できます。 この場合、トリガーを実行するときの現在の回路は、トリガー自体の回路と見なされます。
• MERGE操作は、行の操作に応じて、レコードを変更、挿入、または削除するトリガーをトリガーします。
• トリガーはトランザクションの一部であり、トリガーのエラーは操作をロールバックし、トリガーのテーブル変更はトランザクションの一部になります。
• トランザクションがロールバックされると、トリガーの変更もロールバックされます。
• トリガーでは、DDLおよびトランザクション管理ステートメントは禁止されています（例外はスタンドアロントランザクションです）。

• テーブルの上
• ビュー内（トリガーの代わり）

• レコードの挿入（挿入）
• レコードの更新（更新）
• レコードを削除（削除）

• チームレベル（ステートメントレベルのトリガー）
• 行レベルのトリガー
• 複合トリガー

• 操作前（前）
• 操作後（後）

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; 

擬似テープ

• 疑似レコードでは、レコード全体のレベルの操作は禁止されています（：new = null;）
• 古いレコードのフィールドの値を変更することはできません
• 削除時にトリガーが起動した場合、新しいレコードのフィールドの値を変更することはできません
• 後トリガーでは、新しいレコードのフィールドの値を変更できません

dmlトリガーの代わりに

• これらはビュー用に作成され、DML操作を機能に置き換えます。
• 更新可能でないビューの挿入/更新または削除操作を許可します。

• これは常に行レベルのトリガーです。
• 古い擬似エントリと新しい擬似エントリにアクセスできますが、変更することはできません
• dml操作をビューに置き換えます

 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トリガー

• このようなトリガーは、さまざまなイベントおよびさまざまな時点（オペレーターまたは行レベル、イベントの挿入/更新/削除、イベントの前後）で機能します。
• オフライントランザクションにすることはできません。

• コレクション内の行を収集して別のテーブルに挿入し、定期的にバンドルに挿入します
• 変更テーブルエラーを回避する

複合トリガー構造

• 文の前
• ステートメントの後
• 各行の前
• 各行の後

• 式レベルのセクションの古い、新しい、または親の擬似エントリにはアクセスできません（ステートメントの前とステートメントの後）
• 変更できるのは、各行セクションの前にある新しい擬似レコードフィールドの値のみです
• あるセクションで生成された例外は、別のセクションでは処理できません。
• gotoステートメントを使用する場合、同じセクション内のコードを指す必要があります。

 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トリガーを定義するための基本的な規則

• 更新-トリガーする変更可能なフィールドのリストを指定できます
• SQL実行段階でトリガーを開始せずに、ヘッダーとWhen ...のすべての条件がチェックされます。
• オペレーターが組み込み関数のみを使用できる場合
• 同じ種類の複数のトリガーを作成できます。実行順序はデフォルトでは定義されていませんが、FOLLOWS TRIGGER_FIRST構文を使用して設定できます
• 一意性制約は、レコードが変更されたとき、つまり実行前トリガーの実行後にチェックされます
• 変数宣言セクションは、単語DECLAREによって定義されます
• トリガーのメインブロックは、通常のPL / SQLブロックと同じルールに従います

DMLトリガーの制限

• DDLステートメントを実行できません（スタンドアロントランザクションのみ）
• トランザクション制御ステートメントでルーチンを実行できません
• SERIALLY_REUSABLEパッケージへのアクセス権がありません
• サイズは32Kを超えることはできません
• タイプLONGおよびLONG RAWの変数は宣言できません

突然変異エラー表ORA-04091

• 操作レベルトリガーを使用する
• トリガーのオフライントランザクション
• サードパーティの構造（パッケージレベルのコレクション）を使用する
• 化合物トリガーを使用する
• トリガーから機能を削除してアルゴリズム自体を変更する

システムトリガー

• 操作が実行される前（トリガーが起動する場所）
• 操作が完了した後（トリガーが起動する）
• Createステートメントを実行する代わりに

スキーマトリガー

• 回路のユーザー所有者がイベントを起動する（操作を実行する）ときに常に動作し、そのイベントでトリガーが起動されます。
• 他のユーザーが作成者の権限で呼び出されるプロシージャ/関数を起動し、このプロシージャ/関数でシステムトリガーが作成された操作が実行される場合、このトリガーは機能します。

 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; 

システムトリガー属性

 IF (ora_sysevent = 'LOGON') THEN v_addr := ora_client_ip_address; END IF; 

 v_db_name := ora_database_name; 

 IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table VALUES (ora_des_encrypted_password); END IF; 

 INSERT INTO event_table VALUES ('Changed object is ' || ora_dict_obj_name); 

 IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(name_list); END IF; 

 INSERT INTO event_table VALUES ('object owner is' || ora_dict_obj_owner); 

 IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(owner_list); END IF; 

 INSERT INTO event_table VALUES ('This object is a ' || ora_dict_obj_type); 

 IF (ora_sysevent = 'GRANT') THEN number_of_grantees := ora_grantee(user_list); END IF; 

 IF (ora_instance_num = 1) THEN INSERT INTO event_table VALUES ('1'); END IF; 

 IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN alter_column := ora_is_alter_column('C'); END IF; 

 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; 

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

 IF ora_is_servererror(error_number) THEN INSERT INTO event_table VALUES ('Server error!!'); END IF; 

 SELECT ora_login_user FROM DUAL; 

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

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

 IF (ora_sysevent = 'REVOKE') THEN number_of_users := ora_revokee(user_list); END IF; 

 INSERT INTO event_table VALUES ('top stack error ' || ora_server_error(1)); 

 n := ora_server_error_depth; -- Use n with functions such as ora_server_error 

 INSERT INTO event_table VALUES ('top stack error message' || ora_server_error_msg(1)); 

 n := ora_server_error_num_params(1); 

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

 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; 

 INSERT INTO event_table VALUES (ora_sysevent); 

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

 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; 

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

トリガーのコンパイル

• トリガーは無効な状態で作成されます（またはそのような状態になります）
• データベーストリガーイベントでの起動後
• トリガーイベントは、データベースへのログオン後またはスキーマへのログオン後であり、システムユーザーとしてログインが試行されます。

トリガー例外

• トリガーイベントがデータベースの起動後またはデータベースのシャットダウン前の場合
• トリガーイベントがデータベースへのログオン後であり、ユーザーがデータベーストリガー管理権限を持っている場合
• スキーマへのログオン後のトリガーイベントで、ユーザーがスキーマを所有しているか、または任意のトリガーを変更する特権を持っている場合

トリガー順序

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; 

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

 grant ADMINISTER DATABASE TRIGGER to USER; 

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

• dba_triggers-トリガー情報
• dba_source-トリガー本体コード
• dba_objects-トリガーの有効性

その他のOracleの記事