下面那个触发器的代码出了一点问题,lastnames和studentids无法在触发器报错的状态下显示出来,请问大家要怎么解决?等待好心人!!
insert into Registration values ('265479','FA122','06','2',0,0)
*第 1 行出现错误:
ORA-20000: Student ID has register 4 modules already, the number of module for a particular semester should not exceed 4
ORA-06512: 在 "SYSTEM.CHECKMODULECOUNT", line 22
ORA-04088: 触发器 'SYSTEM.CHECKMODULECOUNT' 执行过程中出错
==============================================================
create or replace trigger checkmodulecount
before insert on registration
for each row
declare
lastnames varchar2(20);
studentids varchar2(20);
moduleCount number;
ex exception;
begin
select count(moduleid) into moduleCount
from registration r,student s
where s.lastname= lastnames
and s.studentid= studentids
and moduleid = :new.moduleid
and r.studentid=s.studentid
and r.semester='2' or r.semester='1';
if moduleCount < 4 then
dbms_output.put_line('Module registered successful');
else
raise ex;
end if;
exception
when ex then
raise_application_error(-20000, 'Student '|| lastnames || ' ID '||studentids || ' has register 4 modules already, the number of module for a particular semester should not exceed 4');
end;
insert into Registration values ('265479','FA122','06','2',0,0)
*第 1 行出现错误:
ORA-20000: Student ID has register 4 modules already, the number of module for a particular semester should not exceed 4
ORA-06512: 在 "SYSTEM.CHECKMODULECOUNT", line 22
ORA-04088: 触发器 'SYSTEM.CHECKMODULECOUNT' 执行过程中出错
==============================================================
create or replace trigger checkmodulecount
before insert on registration
for each row
declare
lastnames varchar2(20);
studentids varchar2(20);
moduleCount number;
ex exception;
begin
select count(moduleid) into moduleCount
from registration r,student s
where s.lastname= lastnames
and s.studentid= studentids
and moduleid = :new.moduleid
and r.studentid=s.studentid
and r.semester='2' or r.semester='1';
if moduleCount < 4 then
dbms_output.put_line('Module registered successful');
else
raise ex;
end if;
exception
when ex then
raise_application_error(-20000, 'Student '|| lastnames || ' ID '||studentids || ' has register 4 modules already, the number of module for a particular semester should not exceed 4');
end;
解决方案 »
- 请教,如何通过pl/sql将CSV中得数据导入带有clob的表中
- oracle导入dmp数据不成功
- dbms_scheduler的repeat_interval设置问题
- v$resource_limit视图中几个字段是什么意思?
- oracle 表空间问题
- 如何才能提交统计效率!
- 帮我改一下下面的SQL,谢谢
- ???Long Raw 域内字符串的搜索问题???
- toad创建的数据库默认用户名,密码是什么?
- oracle数据库中对varchar类型有什么要求嘛,遇到莫名其妙的问题
- 请问:PGA区域中,如何进行数据的读取和写入?PGA读取和写入的,都是哪方面的数据呢?这些数据,需要与硬盘、或者SGA交互吗?
- 做一个备份程序 如果备份库空间不足报什么错?
2、从逻辑上分析,select count(moduleid) into moduleCount
from registration r,student s
where s.lastname= lastnames
and s.studentid= studentids
and moduleid = :new.moduleid
and r.studentid=s.studentid
and r.semester='2' or r.semester='1';
后面的or条件有错误,否则,前面的值条件就没意义了。应该是and r.studentid=s.studentid and (r.semester='2' or r.semester='1');
3、过程段中别定义和字段名称一样的变量
4、raise_application_error(-20000, 'Student '|| :new.lastnames || ' ID '||:new.studentid || ' error );
before insert on registration
for each row
declare
lastnames varchar2(20);
studentids varchar2(20);
moduleCount number;
ex exception; begin
select count(moduleid) into moduleCount
from registration r,student s
where s.lastname= lastnames
and s.studentid= studentids
and moduleid = :new.moduleid
and r.studentid=s.studentid
and r.semester='2' or r.semester='1';
if moduleCount < 4 then
dbms_output.put_line('Module registered successful');
else
raise ex;
end if;
exception
when ex then
raise_application_error(-20000, 'Student '|| lastnames || ' ID '||studentids || ' has register 4 modules already, the number of module for a particular semester should not exceed 4');
end;
这有必要写触发器么??select count(moduleid) into moduleCount
from registration r,student s
行级触发用这语句会报错还有条件r.semester='2' or r.semester='1'
加括号
TRIGGER CHECKMODULECOUNT 出现错误:LINE/COL ERROR
8/16 PLS-00049: 错误的赋值变量 'NEW.LASTNAME' create or replace trigger checkmodulecount
before insert on registration
for each row
declare
moduleCount number;
ex exception;begin
select count(moduleid) into moduleCount
from registration r,student s
where lastname=:new.lastname
and studentid=:new.studentid
and moduleid = :new.moduleid
and r.studentid=s.studentid
and (r.semester='2' or r.semester='1');
if moduleCount < 4 then
dbms_output.put_line('Module registered successful');
else
raise ex;
end if;
exception
when ex then
raise_application_error(-20000, 'Student '|| lastname || ' ID '||studentid || ' has register 4 modules already, the number of module for a particular semester should not exceed 4');
end;