oracle触发器更新当前表

装载注明：http://yuexiaodong.iteye.com/blog/1891589

 

       总是有这样的需求，当更新某个表的某条记录，然后需要对该表的某个字段进行更新，通过使用触发器即可完成这项工作。具体思路就是：首先建立一个临时表，然后在行触发器中记录当前操作的记录，然后在语句触发器中更新当前表。这样做的原因是，oracle对行触发器进行了限制，不能对当前表操作（具体如下：删除的行触发器不允许任何DDL操作，插入的行触发器允许通过游标读取数，这些都是我个人认为，欢迎指正！），但语句级触发器又不能获取当前增加或删除的记录，所以使用两类触发器。网上说可以使用自治事务，个人实验一把，没有达到我的目的，后来想想自治事务跟主事务独立，数据应该不是实时的，暂时放弃使用。

需求简介：对A表添加一个记录，根据表中某个字段的count（*）更新该新记录的某个字段属性值；删除某条记录，更新某个属性值；

源码如下：

 ---建立临时表 
CREATE GLOBAL  TEMPORARY TABLE  ttt_Insprofworkty_insert
(
   id NUMBER(10,0),
   workid NUMBER(10,0),
   worktypeid NUMBER(10,0),
   projectid NUMBER(10,0),
   ordervalue NUMBER(10,0)
);

CREATE GLOBAL  TEMPORARY TABLE  ttt_Delprofworktype_delete
(
   id NUMBER(10,0),
   workid NUMBER(10,0),
   worktypeid NUMBER(10,0),
   projectid NUMBER(10,0),
   ordervalue NUMBER(10,0)
);

 

create or replace trigger profworktype_delete
before  delete
on  profworktype
FOR EACH ROW
    -- 完成与删除的相同workid且大于当前删除ordervalue的操作
DECLARE    
	  begin  
	insert into ttt_Delprofworktype_delete values (:old.id,:old.workid,:old.worktypeid,:old.projectid,:old.ordervalue);     			 
          end;
------------------------------------
create or replace trigger profworktype_insert
before  insert
on  profworktype
FOR EACH ROW
   
DECLARE
BEGIN
	  select S_profworktype.NEXTVAL INTO :new.id from dual;
	 insert into ttt_insprofworkty_insert values(:new.id,:new.workid,:new.worktypeid,:new.projectid,:new.ordervalue);     
      
END;

 

---------------------------------
语句触发器完成业务
 create or replace trigger profworktype_statement
after   delete   or insert
on  profworktype
    -- 完成与删除的相同workid且大于当前删除ordervalue的操作
DECLARE
       v_count  NUMBER(10,0);
        cursor  cur_del  is select * from  ttt_Delprofworktype_delete deleted;
				cursor  cur_ins  is select * from ttt_Insprofworkty_insert    inserted;
    begin
			 if deleting then
				           begin
                    for i in cur_del loop
                     begin
                      -- dbms_output.put_line('-------'||i.ordervalue);
                       update  profworktype set ordervalue = ordervalue -1 where workid = i.workid and ordervalue > i.ordervalue ;
                       end ;
                       end loop;
                        delete from Ttt_Delprofworktype_Delete;
			 end ;    
			 end if;
			 -----
			 if inserting then
				  begin
				  for i in cur_ins loop
                            begin        
				v_count := 0;
         select   count(*) INTO v_count from  profworktype where ordervalue is not null and workid = i.workid;   		  
       update  profworktype set ordervalue = v_count+1 where id =i.id; 		 
            end ;
      end loop;
       delete from ttt_Insprofworkty_insert;
			end ;   
			end if;
			end;