oracle表触发器更新数据库,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;