create or replace trigger insertPid
after insert
on table1
declare
begin
sp_insert_table1;
end;
====================
sp_insert_table1;是一个存储过程 用来对table1 insert一条记录 的
我这么编写后 报错:超过递归sql级别的最大值50
各位该怎么解决???
after insert
on table1
declare
begin
sp_insert_table1;
end;
====================
sp_insert_table1;是一个存储过程 用来对table1 insert一条记录 的
我这么编写后 报错:超过递归sql级别的最大值50
各位该怎么解决???
CREATE OR REPLACE PROCEDURE sp_insert_table1 AS
T_COUNT NUMBER;
AAA NUMBER;
BBB NUMBER;
CCC NUMBER;
T_FUNCTIONID varchar2(100);
SB9_FID varchar2(100);
SB6_FID varchar2(100);
SB3_FID varchar2(100);
i NUMBER;
BEGIN
select count(*)
into T_COUNT
from table1 t where t.functionid not like '%00%';
i:= 1;
loop
exit when i>T_COUNT;
select functionid into T_FUNCTIONID from (select rownum rno,t.functionid from table1 t where t.functionid not like '%00%') where rno=i;
SB9_FID:= substrb(T_FUNCTIONID,1,9)||'.00';
select count(*)into AAA from table1 t where t.functionid=SB9_FID;
if AAA <=0
then
insert into table1 t(t.functionid,t.functionname,t.functiontype,t.openflag,t.pid) select s.functionid,s.functionname,s.functiontype,s.openflag,s.pid from table2 s where s.functionid=SB9_FID;
end if;
SB6_FID:=substrb(T_FUNCTIONID,1,6)||'.00.00';
select count(*)into BBB from table1 t where t.functionid=SB6_FID;
if BBB <=0
then
insert into table1 t(t.functionid,t.functionname,t.functiontype,t.openflag,t.pid) select s.functionid,s.functionname,s.functiontype,s.openflag,s.pid from table2 s where s.functionid=SB6_FID;
end if;
SB3_FID:= substrb(T_FUNCTIONID,1,3)||'.00.00.00';
select count(*)into CCC from table1 t where t.functionid=SB3_FID;
if CCC <=0
then
insert into table1 t(t.functionid,t.functionname,t.functiontype,t.openflag,t.pid) select s.functionid,s.functionname,s.functiontype,s.openflag,s.pid from table2 s where s.functionid=SB3_FID;
end if;
i:= i + 1;
T_FUNCTIONID:='';
end loop;
END sp_insert_table1;
insert into table1 t(t.functionid,t.functionname,t.functiontype,t.openflag,t.pid) select s.functionid,s.functionname,s.functiontype,s.openflag,s.pid from table2 s where s.functionid=SB3_FID;
其中某一列的值再触发其实,不应该在同一表中执行,最好用主从表