Oracle触发器和事务

布满荆棘的人生 2022-04-15 02:25 331阅读 0赞

# oracle触发器和事务 #

2015年11月24日 14:16:43 [it\_taojingzhan][it_taojingzhan] 阅读数：320

## 编写触发器时，需要注意以下几点： ##

l        触发器不接受参数。

l        一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l        在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l        触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l        **在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）**。

l        触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l        在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l        在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l        不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

关于“在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。” 这个可以区分一下，如果调用的过程中声明的是“自治事物”是可以调用的。

创建如下三张表，一个触发器和两个过程

**\[html\]** [view plain][][copy][view plain]

1.  create table t\_test(id int,tname varchar2(20));  
2.  create table t\_test1(id int,tname varchar2(20));  
3.  create table t\_test2(id int,tname varchar2(20));  
4.    
5.  create or replace trigger tr\_t\_test  
6.   after  insert  
7.    on t\_test  
8.    FOR EACH ROW  
9.    DECLARE  
10.   i int;  
11. begin  
12.   i :=1;  
13.   pro\_t\_test1(:NEW.id,:NEW.tname);  
14.   
15.   -- pro\_t\_test2(:old.id,:old.tname);  
16.   --rollback;  
17. end;  
18. /  
19.   
20. create or replace procedure pro\_t\_test1(vid int, vname varchar2)  
21. is  
22. Pragma Autonomous\_transaction;  
23. begin  
24.   insert into t\_test1 values(vid,vname);  
25.   commit;  
26. end;  
27. /  
28. create or replace procedure pro\_t\_test2(vid int, vname varchar2)  
29. is  
30. begin  
31.    insert into t\_test2 values(vid,vname);  
32.    commit;  
33. end;  
34. /

第一步触发器中的“

**\[html\]** [view plain][][copy][view plain]

1.  \-- pro\_t\_test2(:old.id,:old.tname);  
2.  \--rollback;

”这两行是没有注释的掉。

虽然这个触发器可以创建成功，但是在insert操作的时候会报错ora-04092。

**\[html\]** [view plain][][copy][view plain]

1.  SQL> insert into t\_test values(1,'a');  
2.     
3.  insert into t\_test values(1,'a')  
4.     
5.  ORA-04092: cannot COMMIT in a trigger  
6.  ORA-06512: at "YJQF.PRO\_T\_TEST2", line 5  
7.  ORA-06512: at "YJQF.TR\_T\_TEST", line 7  
8.  ORA-04088: error during execution of trigger 'YJQF.TR\_T\_TEST'  
9.     
10. SQL> commit;  
11.    
12. Commit complete  
13.    
14. SQL> select count(\*) from t\_test;  
15.    
16.   COUNT(\*)  
17. \----------  
18.          0  
19.    
20. SQL> select count(\*) from t\_test1;  
21.    
22.   COUNT(\*)  
23. \----------  
24.          1  
25.    
26. SQL> select count(\*) from t\_test2;  
27.    
28.   COUNT(\*)  
29. \----------  
30.          0  
31.    
32. SQL>   
33. SQL> insert into t\_test values(1,'a');  
34.    
35. insert into t\_test values(1,'a')  
36.    
37. ORA-04092: cannot ROLLBACK in a trigger  
38. ORA-06512: at "YJQF.TR\_T\_TEST", line 8  
39. ORA-04088: error during execution of trigger 'YJQF.TR\_T\_TEST'  
40.    
41. SQL> commit;  
42.    
43. Commit complete  
44.    
45. SQL> select count(\*) from t\_test;  
46.    
47.   COUNT(\*)  
48. \----------  
49.          0  
50.    
51. SQL> select count(\*) from t\_test1;  
52.    
53.   COUNT(\*)  
54. \----------  
55.          2  
56.    
57. SQL> select count(\*) from t\_test2;  
58.    
59.   COUNT(\*)  
60. \----------  
61.          0  
62.    
63. SQL>

这两条insert虽然失败了，但是还是写到了t\_test1表中了，在pro\_t\_test1上是有commit的，从这个可以看出“自主事物”是完全独立的。

在你的主事务中，你可以选择能够从其他事务中进行调用的独立事物。自治事务可以提交或回滚其修改而不影响调用它的主事务。

将这两行后注释掉后，插入就成功了。

SQL> insert into t\_test values(1,'a');  
   
1 row inserted  
   
SQL> commit;  
   
Commit complete  
   
SQL> select count(\*) from t\_test;  
   
  COUNT(\*)  
\----------  
         1  
   
SQL> select count(\*) from t\_test1;  
   
  COUNT(\*)  
\----------  
         3  
   
SQL> select count(\*) from t\_test2;  
   
  COUNT(\*)  
\----------  
         0

[https://blog.csdn.net/it\_taojingzhan/article/details/50012161][https_blog.csdn.net_it_taojingzhan_article_details_50012161]

[it_taojingzhan]: https://me.csdn.net/it_taojingzhan
[view plain]: http://blog.csdn.net/rookie_ceo/article/details/8206687#
[https_blog.csdn.net_it_taojingzhan_article_details_50012161]: https://blog.csdn.net/it_taojingzhan/article/details/50012161