装载注明: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;