我指的是: 当A 字段更新后,再去更新B字段,且这2个字段是同一条记录的。 做了个测试如下: drop table test001; create table test001( --建立测试表 id number(4), text01 varchar2(30), text02 varchar2(30) ); drop trigger test_trg_001; create or replace trigger test_trg_001 after update on test001 begin update test001 set text02=to_char(sysdate,'HH24:MI') --just get some character where id=1; end; /insert into test001 values(1,'aaa','bbb'); update test001 set text01='ccc' where id=1;结果: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-00036: maximum number of recursive SQL levels (50) exceeded
SQL> create table t (a number,b number);表已创建。 1 create or replace trigger t_t before update on t 2 for each row 3 declare 4 n number; 5 begin 6 n:=:new.a+1; 7 :new.b:=n; 8* end; SQL> /触发器已创建SQL> select * from t;未选定行SQL> insert into t values(1,1);已创建 1 行。SQL> update t set t.a=2;已更新 1 行。SQL> select * from t; A B ---------- ---------- 2 3
当A 字段更新后,再去更新B字段,且这2个字段是同一条记录的。
做了个测试如下:
drop table test001;
create table test001( --建立测试表
id number(4),
text01 varchar2(30),
text02 varchar2(30)
);
drop trigger test_trg_001;
create or replace trigger test_trg_001
after update on test001
begin
update test001 set text02=to_char(sysdate,'HH24:MI') --just get some character
where id=1;
end;
/insert into test001 values(1,'aaa','bbb');
update test001 set text01='ccc' where id=1;结果:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded
表的update trigger中又 update表自己 那不是无限循环了
用 :new 吧
1 create or replace trigger t_t before update on t
2 for each row
3 declare
4 n number;
5 begin
6 n:=:new.a+1;
7 :new.b:=n;
8* end;
SQL> /触发器已创建SQL> select * from t;未选定行SQL> insert into t values(1,1);已创建 1 行。SQL> update t set t.a=2;已更新 1 行。SQL> select * from t; A B
---------- ----------
2 3