刚学数据库,在学习触发器时出现如下问题:
SQL CODE:
create or replace trigger insert_ll
before insert on ll
--after inert on ll
referencing new as new old as old
for each row
declare
d_a char(10);
d_b number(10);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select ll.a,ll.b into d_a,d_b from ll where ll.a = :new.a;
if inserting then
insert into ll1(a,b)values(d_a,d_b);
end if;
commit;
end insert_ll;在执行数据插入前 ll表有1条数据
a b
1 100当我执行insert into ll values('2',200);语句时报如下错误:
ora-01403:未找到数据
ora-06512:在system.insert_ll,line 6
ora-04088:触发器system.insert_ll执行过程中出错
请问各位这个问题怎么解决啊。
SQL CODE:
create or replace trigger insert_ll
before insert on ll
--after inert on ll
referencing new as new old as old
for each row
declare
d_a char(10);
d_b number(10);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select ll.a,ll.b into d_a,d_b from ll where ll.a = :new.a;
if inserting then
insert into ll1(a,b)values(d_a,d_b);
end if;
commit;
end insert_ll;在执行数据插入前 ll表有1条数据
a b
1 100当我执行insert into ll values('2',200);语句时报如下错误:
ora-01403:未找到数据
ora-06512:在system.insert_ll,line 6
ora-04088:触发器system.insert_ll执行过程中出错
请问各位这个问题怎么解决啊。
因为在执行select ll.a,ll.b into d_a,d_b from ll where ll.a = :new.a;的时候出现错误。
insert into ll values('2',200); 此时 new.a=2
你ll表中没有 a=2的记录,所以报错。
你执行insert into ll values('1',200); 就没错了。
或者加个异常处理。如
create or replace trigger insert_ll
before insert on ll
referencing new as new old as old
for each row
declare
d_a char(10);
d_b number(10);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select ll.a, ll.b into d_a, d_b from ll where ll.a = :new.a;
if inserting then
insert into lll (a, b) values (d_a, d_b);
end if;
commit;
exception
when others then
return;
end insert_ll;
实际很简单
直接
insert into lll(a, b) values(:new.a, :new.b);
我把 before改为after,也不行。按字面理解 系统是先执行insert into ll values('2',200);
然后再把这条数据插入ll1表中,这样的话,此时ll表中有 a=2的记录,new.a=2 应该存在啊,怎么还报错?
请解答一下,谢谢!
你的需求真奇怪,如果像上面说的,那么
insert into lll(a, b) values(:new.a, :new.b);
就可以了,没必要select