一、问题实例
存在表poleinfotable包含如下字段:
TOTALPOLEID --------线路id,主键
CIRCUITRYNAME --------线路名称
POLEID --------杆塔号
CIRCUITRYTYPEPARTAL --------线路周期
UNITSTATUSPARTAL --------一般周期
TESHUQUPARTA --------特殊周期
MINCYCLE --------实际周期
要求:实际周期=线路周期、一般周期、特殊周期中的最小值,当线路周期、一般周期、特殊周期的值放生变化时,要重新计算实际周期的值;
解决思路:使用oracle触发器,当CIRCUITRYTYPEPARTAL、UNITSTATUSPARTAL、TESHUQUPARTA中任意值放生变化,则自动更新MINCYCLE;
触发器语句:
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE
BEFORE INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE
FOR EACH ROW
BEGIN
:new.mincycle :=least(:new.CIRCUITRYTYPEPARTAL,:new.TESHUQUPARTAL,:new.UNITSTATUSPARTAL);
END;
二、感悟心得
事实上我在写这个触发器的时候遇到了很多问题诸如:
ORA-04091: 表 SXB.POLEINFOTABLE 发生了变化, 触发器/函数不能读它;
ORA-00060: 等待资源时检测到死锁;
- 错误: ORA-04084: 无法更改此触发器类型的 NEW 值
下面我们把上面的情况逐个分析下:
在分析问题先我们先简单的回顾下触发器的语法:
create [or replace] trigger tri_name -----创建或覆盖触发器
[before|after|instead of] tir_event -----触发器触发的时机
on table_name|view_name|user_name|db_name -----触发器作用在什么地方(表、视图、用户模式、数据库)
[for each row] [when tri_condition] -----是否为行级触发器,触发的条件表达式是什么
begin -----开始
plsql_sentences; -----触发器的主体
end tri_name; -----结束
在拿到问题后,我们首先想到的是在CIRCUITRYTYPEPARTAL、UNITSTATUSPARTAL、TESHUQUPARTA三个字段完成插入或者更新操作之后才对MINCYCLE进行修改,于是我们很自然的在创建触发器的时候选择‘after’作为触发时机;
方案一:
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE
AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE
FOR EACH ROW
BEGIN
update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL) ;
END;
当我们对表poleinfotable进行修改后就会出现‘ORA-04091: 表 SXB.POLEINFOTABLE 发生了变化, 触发器/函数不能读它;’的错误;
原因是在触发事件发生后表poleinfotable产生了一个事务A(修改字段CIRCUITRYTYPEPARTAL、UNITSTATUSPARTAL、TESHUQUPARTA),如果此时我们想修改表poleinfotable的MINCYCLE字段,就又需要产生一个新的事务B(修改字段MINCYCLE);但是此时的事务A并没有被提交,我们又要产生事务B,这在oracle中是不合法的;
oracle的数据一致性要求,在一个表的事务没有提交之前,无法对这个表产生新的事务;
于是我们自然就想到了自定事务,既然触发事件的事务没有提交,我们就自己另外定义一个事务来执行update语句;
方案二:
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE
AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL) ;
COMMIT;
END;
此时当我们修改poleinfotable表的字段后就得到了‘ORA-00060: 等待资源时检测到死锁;’的错误了;
这里我个人分析的原因是虽然我们自定义了事务来执行update,但其本质仍然是对表poleinfotable进行操作,理论上我们没有违背数据一致的原则,但实际上我们貌似打了个‘擦边球’,oracle并没有通过;
那么我们换个思路,在触发器中我们不再进行修改操纵而是转而执行存储过程,由存储过程执行修改的部分;
方案三:
创建存储过程
CREATE OR REPLACE PROCEDURE POLEINFOTABLE_CYCLE
(
CIRCUITRYTYPEPARTAL IN NUMBER
, UNITSTATUSPARTAL IN NUMBER
, TESHUQUPARTAL IN NUMBER
)
AS
BEGIN
update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL);
END POLEINFOTABLE_CYCLE;
修改触发器
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE
AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE
FOR EACH ROW
DECLARE
CIRCUITRYTYPEPARTAL NUMBER;
TESHUQUPARTAL NUMBER;
UNITSTATUSPARTAL NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
CIRCUITRYTYPEPARTAL := :new.CIRCUITRYTYPEPARTAL;
TESHUQUPARTAL := :new.TESHUQUPARTAL;
UNITSTATUSPARTAL := :new.UNITSTATUSPARTAL;
COMMIT;
END;
这样运行的结果仍然是死锁;
此时我们分析下死锁的原因,之所以会出现这种情况是因为触发事件发生在数据修改之后,而触发结果和触发条件都作用在同一个表上,由于触发器的原因导致表的修改事务无法提交,而修改事务无法提交又导致触发器无法结束,这是一个死循环;
那么如果我们让触发事件发生在数据修改之前呢?此时先产生事务B,而此时没有事务A,所以事务B可以正常执行,而当触发器结束后,事务B也就结束了,此时再进行数据修改,产生事务A,事务A也就可以提交了;于是就有了上文的语句:
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE
BEFORE INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL ON POLEINFOTABLE
FOR EACH ROW
BEGIN
:new.mincycle :=least(:new.CIRCUITRYTYPEPARTAL,:new.TESHUQUPARTAL,:new.UNITSTATUSPARTAL);
END;
三、 总结
(1)当触发条件和触发结果作用在一个表的时候,触发时机因该选before;
(2)当触发条件在表A,触发结果在表B时,触发时机可以选after;
需要注意的是:触发器中有两个列标识符‘:new.列’,‘:old.列’,只有在触发条件为‘before’时才可以对‘:new.列’进行赋值;在触发时机为’after‘时,只能对‘:new.列’,‘:old.列’进行引用,不能赋值,也不能修改;