create or replace trigger insertsjlw
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2;
icount integer;
begin
open c_sjlw;
fetch c_sjlw into dd;
select count(*) into icount from auto_sjlw_class where name=dd.bmlx and type='保密类型';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
select count(*) into icount from auto_sjlw_class where name=dd.gwbt and type='公文标题';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
select count(*) into icount from auto_sjlw_class where name=dd.gwlx and type='公文类型';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
select count(*) into icount from auto_sjlw_class where name=dd.fwbm and type='发文部门';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
close c_sjlw;
commit;
end insertsjlw;
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2;
icount integer;
begin
open c_sjlw;
fetch c_sjlw into dd;
select count(*) into icount from auto_sjlw_class where name=dd.bmlx and type='保密类型';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
select count(*) into icount from auto_sjlw_class where name=dd.gwbt and type='公文标题';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
select count(*) into icount from auto_sjlw_class where name=dd.gwlx and type='公文类型';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
select count(*) into icount from auto_sjlw_class where name=dd.fwbm and type='发文部门';
if icount=0 then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
close c_sjlw;
commit;
end insertsjlw;
他必须把select的结果放到一个变量里或者一个表里,所以不要用
select * from auto_sjlw_class where name=dd.bmlx and type='保密类型' 这样判断有没有,用:
if not exists(select count(*) from auto_sjlw_class where name=dd.bmlx and type='保密类型') then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2(128);
begin
open c_sjlw;
fetch c_sjlw into dd;
if not exists(select count(*) from auto_sjlw_class where name=dd.bmlx and type='保密类型';) then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
if not exists(select * from auto_sjlw_class where name=dd.gwbt and type='公文标题';) then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
if not exists(select * from auto_sjlw_class where name=dd.gwlx and type='公文类型';) then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
if not exists(select * from auto_sjlw_class where name=dd.fwbm and type='发文部门';) then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
close c_sjlw;
commit;
end insertsjlw;
你的游标里loop没有跳出循环的条件:exit when c_sjlw%notfound; 不然可要死循环了。
create or replace trigger insertsjlw
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2(128);
begin
open c_sjlw;
fetch c_sjlw into dd;
exit when c_sjlw%notfound;
if not exists(select count(*) from auto_sjlw_class where name=dd.bmlx and type='保密类型';) then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
if not exists(select * from auto_sjlw_class where name=dd.gwbt and type='公文标题';) then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
if not exists(select * from auto_sjlw_class where name=dd.gwlx and type='公文类型';) then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
if not exists(select * from auto_sjlw_class where name=dd.fwbm and type='发文部门';) then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
close c_sjlw;
commit;
end insertsjlw;
按你说的出现了以下错误:
SQL> show error
TRIGGER INSERTSJLW 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
9/92 PLS-00103: 出现符号 ";"在需要下列之一时:
.()*@%&-+/atmodremwith
<an exponent (**)>andorgrouphavingintersectminusstartunion
whereconnect||
符号 ";" 被忽略。12/85 PLS-00103: 出现符号 ";"在需要下列之一时:
.()*@%&-+/atmodremwith
<an exponent (**)>andorgrouphavingintersectminusstartunion
whereconnect||
符号 ";" 被忽略。LINE/COL ERROR
-------- -----------------------------------------------------------------15/85 PLS-00103: 出现符号 ";"在需要下列之一时:
.()*@%&-+/atmodremwith
<an exponent (**)>andorgrouphavingintersectminusstartunion
whereconnect||
符号 ";" 被忽略。18/85 PLS-00103: 出现符号 ";"在需要下列之一时:
.()*@%&-+/atmodremwith
<an exponent (**)>andorgrouphavingintersectminusstartunion
whereconnect||LINE/COL ERROR
-------- -----------------------------------------------------------------
符号 ";" 被忽略。
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2(128);
begin
open c_sjlw;
loop
fetch c_sjlw into dd;
exit when c_sjlw%notfound;
if not exists(select count(*) from auto_sjlw_class where name=dd.bmlx and type='保密类型') then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
if not exists(select * from auto_sjlw_class where name=dd.gwbt and type='公文标题') then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
if not exists(select * from auto_sjlw_class where name=dd.gwlx and type='公文类型') then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
if not exists(select * from auto_sjlw_class where name=dd.fwbm and type='发文部门') then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
end loop;
close c_sjlw;
commit;
end insertsjlw;
create or replace trigger insertsjlw
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2(128);
begin
open c_sjlw;
loop
fetch c_sjlw into dd;
exit when c_sjlw%notfound;
if not exists(select count(*) from auto_sjlw_class where name=dd.bmlx and type='保密类型') then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
if not exists(select count(*) from auto_sjlw_class where name=dd.gwbt and type='公文标题') then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
if not exists(select count(*) from auto_sjlw_class where name=dd.gwlx and type='公文类型') then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
if not exists(select count(*) from auto_sjlw_class where name=dd.fwbm and type='发文部门') then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
end loop;
close c_sjlw;
commit;
end insertsjlw;
after insert on auto_sjlw
declare
cursor c_sjlw is
select * from auto_sjlw order by id desc;
dd c_sjlw%rowtype;
ee varchar2;
v_count int;
begin
open c_sjlw;
loop
fetch c_sjlw into dd;
exit when c_sjlw%NOTFOUND;
v_count := 0;
select count(*) into v_count from auto_sjlw_class where name=dd.bmlx and type='保密类型';
if v_count <= 0 then
insert into auto_sjlw_class(name,type) values(dd.bmlx,'保密类型');
end if;
v_count := 0;
select count(*) into v_count from auto_sjlw_class where name=dd.gwbt and type='公文标题';
if v_count <= 0 then
insert into auto_sjlw_class(name,type) values(dd.gwbt,'公文标题');
end if;
v_count := 0;
select count(*) into v_count from auto_sjlw_class where name=dd.gwlx and type='公文类型';
if v_count <= 0 then
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if;
v_count := 0;
select count(*) into v_count from auto_sjlw_class where name=dd.fwbm and type='发文部门';
if v_count <= 0 then
insert into auto_sjlw_class(name,type) values(dd.fwbm,'发文部门');
end if;
end loop;
close c_sjlw;
commit;
end insertsjlw;
-------- ----------------------------------------------------
33/0 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
;
符号 ";" 被替换为 "end-of-file" 后继续。注:游标我不是要它的循环,我只要SELECT语句的第一条记录.
TRIGGER INSERTSJLW 出现错误:LINE/COL ERROR
-------- ---------------------------------------------------------
5/6 PLS-00215: 字符串长度限制在范围 (1...32767)
5/6 PL/SQL: Item ignored
刚才那些错误是指在那些EXISTS语句里多了“;”,其实你应该多试试,你都开始写触发器了,一些小的比较明显的错误应该可以自己识别了。
TRIGGER INSERTSJLW 出现错误:LINE/COL ERROR
-------- ---------------------------------------------------------
5/6 PLS-00215: 字符串长度限制在范围 (1...32767)
5/6 PL/SQL: Item ignored
insert into auto_sjlw_class(name,type) values(dd.gwlx,'公文类型');
end if; 我在EXISTS语句里找不到多的“;”,
.()*@%&-+/atmodremwith
<an exponent (**)>andorgrouphavingintersectminusstartunion
whereconnect¦¦
符号 ";" 被忽略。
这种错误通常就是多了个";"33/0 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
;
符号 ";" 被替换为 "end-of-file" 后继续。
这种错误通常是IF ENDIF,LOOP END LOOP,没有匹配好,或者缺少";"
5/6 PL/SQL: Item ignored 是指你的VARCHAR2定义的时候要有长度,比如VARCHAR2(10)
去掉。