使用自治事務在觸發器中執行DDL語句示例

關鍵字:

pragma AUTONOMOUS_TRANSACTION;

1.建立使用自治事務的過程

SQL>create or replace procedure sp_test

2  is

3     pragma AUTONOMOUS_TRANSACTION;

4  BEGIN

5     execute immediate 'drop table test_table';

6     execute immediate 'create table test_table as select * from dual';

7  END;

8  /

 

Procedure created.

 

2.在觸發器中呼叫該過程即可

SQL> create or replace trigger tr_test  after delete on emp

2  BEGIN

3    sp_test;

4  END;

5  /