触发器事务

总结:不能在DML触发器、Instead of触发器、DDL触发器内直接使用提交事务或回滚事务的语句，但Database触发器可以
1、DML触发器(Instead of)
SQL> Create Table tmp_dml(str Varchar2(20));
Table created
SQL> Create Table tmp_dml_log(str_log Varchar2(20));
Table created


SQL> 
SQL> Create Or Replace Trigger trg_dml
  2  After Insert
  3  On tmp_dml
  4  For Each Row
  5  Declare
  6  Begin
  7    Insert Into tmp_dml_log Values(:new.str);
  8    Commit;
  9  End;
 10  /
Trigger created


SQL> Insert Into tmp_dml Values('a');
Insert Into tmp_dml Values('a')
ORA-04092: COMMIT 不能在触发器中
ORA-06512: 在 "ZLHIS.TRG_DML", line 4
ORA-04088: 触发器 'ZLHIS.TRG_DML' 执行过程中出错


SQL> select * from tmp_dml;
STR
--------------------


SQL> select * from tmp_dml_log;
STR_LOG
--------------------


SQL> 
SQL> Create Or Replace Trigger trg_dml
  2  After Insert
  3  On tmp_dml
  4  For Each Row
  5  Declare
  6  Begin
  7    Insert Into tmp_dml_log Values(:new.str);
  8    Rollback;
  9  End;
 10  /
Trigger created


SQL> Insert Into tmp_dml Values('a');
Insert Into tmp_dml Values('a')
ORA-04092: ROLLBACK 不能在触发器中
ORA-06512: 在 "ZLHIS.TRG_DML", line 4
ORA-04088: 触发器 'ZLHIS.TRG_DML' 执行过程中出错


SQL> select * from tmp_dml;
STR
--------------------


SQL> select * from tmp_dml_log;
STR_LOG
--------------------
SQL> Create Or Replace Trigger trg_dml
  2  After Insert
  3  On tmp_dml
  4  For Each Row
  5  Declare
  6  Begin
  7    Insert Into tmp_dml_log Values(:new.str);
  8    Execute Immediate 'Create Table tmp(Id Number)';
  9  End;
 10  /
Trigger created


SQL> Insert Into tmp_dml Values('a');
Insert Into tmp_dml Values('a')
ORA-04092: COMMIT 不能在触发器中
ORA-06512: 在 "ZLHIS.TRG_DML", line 4
ORA-04088: 触发器 'ZLHIS.TRG_DML' 执行过程中出错


SQL> select * from tmp_dml;
STR
--------------------


SQL> select * from tmp_dml_log;
STR_LOG
--------------------


SQL> 


SQL> Create Or Replace Trigger trg_dml
  2  After Insert
  3  On tmp_dml
  4  For Each Row
  5  Declare
  6    Pragma Autonomous_Transaction;
  7  Begin
  8    Insert Into tmp_dml_log Values(:new.str);
  9    Commit;
 10  End;
 11  /
Trigger created


SQL> Insert Into tmp_dml Values('a');
1 row inserted


SQL> select * from tmp_dml;
STR
--------------------
a


SQL> select * from tmp_dml_log;
STR_LOG
--------------------
a


SQL> 


2、DDL触发器
SQL> Create Table tmp_ddl_log(str_log Varchar2(20));
Table created


SQL> Create Or Replace Trigger trg_ddl
  2  After Create
  3  On Schema
  4  Declare
  5  Begin
  6    Insert Into tmp_ddl_log Values(ora_dict_obj_name);
  7    Commit;
  8  End;
  9  /
Trigger created


SQL> Create Table tmp1(Id Number);
Create Table tmp1(Id Number)
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-04092: COMMIT 不能在触发器中
ORA-06512: 在 line 4


SQL> select * from tmp_ddl_log;
STR_LOG
--------------------


SQL> 
SQL> Create Or Replace Trigger trg_ddl
  2  After Create
  3  On Schema
  4  Declare
  5  Begin
  6    Insert Into tmp_ddl_log Values(ora_dict_obj_name);
  7    Rollback;
  8  End;
  9  /
Trigger created


SQL> Create Table tmp1(Id Number);
Create Table tmp1(Id Number)
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-04092: ROLLBACK 不能在触发器中
ORA-06512: 在 line 4


SQL> select * from tmp_ddl_log;
STR_LOG
--------------------


SQL> 
SQL> Create Or Replace Trigger trg_ddl
  2  After Create
  3  On Schema
  4  Declare
  5  Begin
  6    Insert Into tmp_ddl_log Values(ora_dict_obj_name);
  7    Execute Immediate 'create table tmp(id number)';
  8  End;
  9  /
Trigger created


SQL> Create Table tmp1(Id Number);
Create Table tmp1(Id Number)
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00955: 名称已由现有对象使用
ORA-06512: 在 line 4
ORA-06512: 在 line 4


SQL> select * from tmp_ddl_log;
STR_LOG
--------------------


SQL> 
SQL> Create Or Replace Trigger trg_ddl
  2  After Create
  3  On Schema
  4  Declare
  5    Pragma Autonomous_Transaction;
  6  Begin
  7    Insert Into tmp_ddl_log Values(ora_dict_obj_name);
  8    Commit;
  9  End;
 10  /
Trigger created


SQL> Create Table tmp1(Id Number);
Table created


SQL> select * from tmp_ddl_log;
STR_LOG
--------------------
TMP1


SQL> 


3、DATABASE级触发器
SQL> Create Table tmp_database_log(username varchar2(20),ip Varchar2(20));
Table created


SQL> 
SQL> Create Or Replace Trigger trg_database
  2  After logon
  3  On Database
  4  Declare
  5  
  6  Begin
  7    Insert Into tmp_database_log Values(ora_login_user,ora_client_ip_address);
  8    Commit;
  9  End;
 10  /
Trigger created


SQL> conn scott/tiger@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott@orcl


SQL> conn system/manager@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as system@orcl


SQL> conn zlhis/his@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as ZLHIS@ORCL


SQL> select * from tmp_database_log;
USERNAME             IP
-------------------- --------------------
SCOTT                127.0.0.1
SYSTEM               127.0.0.1
SYSTEM               127.0.0.1
ZLHIS                127.0.0.1
SCOTT                127.0.0.1
SYSTEM               127.0.0.1
ZLHIS                127.0.0.1
7 rows selected


SQL>