trigger中的自治事务

• 作者: 三十而立
• 时间：2009年9月14日 2:59:56
• 本文出自 “inthirties（三十而立）”博客，转载请务必注明作者和保留出处http://blog.csdn.net/inthirties/archive/2009/09/14/4550876.aspx

 

这是来自CSDN里，网友问过的一个帖子，

 

网友也在我的blog里提过这样的问题

 

问题如下

 

表 KSOURCE.USERINFO 发生了变化, 触发器/函数不能读它
UserInfo(UserID,UserName,DepartID,SerialNum,…)
对于一个部门（DepartID相同）下的用户，我要让其序号SerialNum保持连续，就建立了一个触发器：

 

引用:

CREATE OR REPLACE TRIGGER “KSOURCE”.TRUSERINFO_INSERT
AFTER
INSERT ON “KSOURCE”.”USERINFO”
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
Declare
v_SerialNum Number;
Begin
Select Count(*) Into v_SerialNum From UserInfo Where DepartID=:New.DepartID;
if (:New.SerialNum v_SerialNum) Then
Update UserInfo Set SerialNum=v_SerialNum Where UserID=:New.UserID;
end if;
End;

这样，如果用户输入的SerialNum不连续了，自动改成连续的，但Insert时却出现如下提示：
表 KSOURCE.USERINFO 发生了变化, 触发器/函数不能读它

这里为什么会出错了，这里的sql代码一点问题都没有，问题就出在是写在trigger里了。而且是for each row的trigger
行级的触发器代码中不能再操作该表，包括select,默认的在写TRIGGER的时候把本表锁死,不允许对其进行操作，

 

解决的方式

就是这里可以通过一些AUTONOMOUS_TRANSACTION(自治事务)来解决

 

或者是用表级别的trigger和行级trigger搭配，行级trigger做标记，表级别的trigger来做操作。配合着package里的临时变量存储标记。

 

一下是简单的处理方式，自治事务

引用:

CREATE OR REPLACE TRIGGER “KSOURCE”.TRUSERINFO_INSERT
AFTER
INSERT ON “KSOURCE”.”USERINFO”
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
Declare
v_SerialNum Number;
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
Select Count(*) Into v_SerialNum From UserInfo Where DepartID=:New.DepartID;
if (:New.SerialNum v_SerialNum) Then
Update UserInfo Set SerialNum=v_SerialNum Where UserID=:New.UserID;
end if;
commit;
End;

你试试这个解决方式叻，不过对于你这case，确实比较复杂，在你delete一条中间的数据时，确实还要控制其他的serialnum也随之发生变化。这样才能保持连续
由于是自治形式的，所以在你的程序块里，最后需要加上commit；

 

 

• 如果没有那句“三十而立”，三十岁的男人正可以轻轻松松
• 专业论坛 http://www.inthirties.com
• 技术博客 http://blog.csdn.net/inthirties
• 个人站点 http://blog.inthirties.com
• Oracle Mysql技术论坛| 打造实用的Oracle Mysql技术交流园地