enhydraboy(乱舞的浮尘) ,怎样指定? AFTER INSERT之中,是不是当前行已经确实存入了数据库中?只要不对于A表进行数据变动操作,完全可以进行自由访问?
索性,我把程序简要的帖上来: create or replace trigger TRIG_INS_UPDATE_A after insert or update on A for each row declare S Varchar2(5000); begin S := 'DELETE FROM B WHERE NO=:P'; EXECUTE IMMEDIATE S USING :New.No;
S := 'INSERT INTO B( NO,COUNT)(Select NO,count(*) FROM A WHERE No=:p group by no)'; Execute Immediate S using :New.No; End ........
FOR EACH ROW example: CREATE TRIGGER scott.salary_check BEFORE INSERT OR UPDATE OF sal, job ON scott.emp FOR EACH ROW WHEN (new.job <> 'PRESIDENT') pl/sql_block
谢谢 enhydraboy(乱舞的浮尘) 的关注! 那如此说来,我的代码岂非已经是“行触发”了?
目前我的触发器这样编写的话,当执行到Execute Immediate S using :New.No时,会出错说,A表的数据已经更改,不能继续,,,,
create or replace trigger TRIG_INS_UPDATE_A before insert or update on A for each row declare num number; begin select count(1) into num from b where no=:new.no; if inserting then if num=0 then INSERT INTO B( NO,COUNT) values(:new.no,1); else update b set count=count+1 where no=:new.no; end if; end if; End; /
触发器体中的 SQL 语句不能进行下列操作: 读或修改触发语句的任何变异表,其中包括触发表本身。 读或修改触发表的约束表中的主关键字,唯一关键字和外部关键字列。除此之外的其他列可以修改。 由于行触发器对变异表操作存在这两个限制,上述功能想实现的话可以先建一个包(临时表也可以),再建前语句级触发器将上面查询出的数据存放到包或临时表中,最后再建一个行级触发器,从包中读取已存放的数据来进行操作.下面是实现的例子: //////////////////////// 包create or replace package pck_scoscottzcntestsen as type tab_num is table of number index by binary_integer; type tab_class is table of varchar2(10) index by binary_integer; v_num tab_num; v_class tab_class; v_count number :=0; end;////////// 行级create or replace trigger trg_scoscottzcntestsen1 before update on scott.zcn for each row begin pck_scoscottzcntestsen.v_count := pck_scoscottzcntestsen.v_count + 1; pck_scoscottzcntestsen.v_num(pck_scoscottzcntestsen.v_count) := :new.num; pck_scoscottzcntestsen.v_class(pck_scoscottzcntestsen.v_count) := :new.class; end ; /////////////////////////////////////////////////////////////////// ////语句级 create or replace trigger trg_scoscottzcntestsen2 after update of name on scott.zcn declare v_maxnum constant number :=5; v_curnum number :=0; v_num number; v_class varchar2(10); begin for icount in 1.. pck_scoscottzcntestsen.v_count loop v_class := pck_scoscottzcntestsen.v_class(icount); select count(1) into v_curnum from scott.zcn a where a.class = v_class; if v_curnum > v_maxnum then raise_application_error(-20000,v_class || 'scott:sen当前人数已满!asdfasdf'); end if; end loop; pck_scoscottzcntestsen.v_count := 0; end ;
ORACLE的触发器有行触发的概念,你只需要指定行触发方式就可以了。
AFTER INSERT之中,是不是当前行已经确实存入了数据库中?只要不对于A表进行数据变动操作,完全可以进行自由访问?
create or replace trigger TRIG_INS_UPDATE_A
after insert or update on A
for each row
declare
S Varchar2(5000);
begin
S := 'DELETE FROM B WHERE NO=:P';
EXECUTE IMMEDIATE S USING :New.No;
S := 'INSERT INTO B( NO,COUNT)(Select NO,count(*) FROM A WHERE No=:p group by no)';
Execute Immediate S using :New.No;
End ........
CREATE TRIGGER scott.salary_check
BEFORE
INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job <> 'PRESIDENT')
pl/sql_block
那如此说来,我的代码岂非已经是“行触发”了?
before insert or update on A
for each row
declare
num number;
begin
select count(1) into num from b where no=:new.no;
if inserting then
if num=0 then
INSERT INTO B( NO,COUNT) values(:new.no,1);
else
update b set count=count+1 where no=:new.no;
end if;
end if;
End;
/
读或修改触发语句的任何变异表,其中包括触发表本身。
读或修改触发表的约束表中的主关键字,唯一关键字和外部关键字列。除此之外的其他列可以修改。
由于行触发器对变异表操作存在这两个限制,上述功能想实现的话可以先建一个包(临时表也可以),再建前语句级触发器将上面查询出的数据存放到包或临时表中,最后再建一个行级触发器,从包中读取已存放的数据来进行操作.下面是实现的例子:
////////////////////////
包create or replace package pck_scoscottzcntestsen as
type tab_num is table of number
index by binary_integer;
type tab_class is table of varchar2(10)
index by binary_integer;
v_num tab_num;
v_class tab_class;
v_count number :=0;
end;//////////
行级create or replace trigger trg_scoscottzcntestsen1
before update on scott.zcn
for each row
begin
pck_scoscottzcntestsen.v_count := pck_scoscottzcntestsen.v_count + 1;
pck_scoscottzcntestsen.v_num(pck_scoscottzcntestsen.v_count) := :new.num;
pck_scoscottzcntestsen.v_class(pck_scoscottzcntestsen.v_count) := :new.class;
end ;
///////////////////////////////////////////////////////////////////
////语句级
create or replace trigger trg_scoscottzcntestsen2
after update of name on scott.zcn
declare
v_maxnum constant number :=5;
v_curnum number :=0;
v_num number;
v_class varchar2(10);
begin
for icount in 1.. pck_scoscottzcntestsen.v_count loop
v_class := pck_scoscottzcntestsen.v_class(icount);
select count(1) into v_curnum
from scott.zcn a
where a.class = v_class;
if v_curnum > v_maxnum then
raise_application_error(-20000,v_class || 'scott:sen当前人数已满!asdfasdf');
end if;
end loop;
pck_scoscottzcntestsen.v_count := 0;
end ;