还是上次那个触发器的问题,不过修改后单独再发一次,把表结构也弄出来了
-------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
cwMark |
ExamMark |
---------------- 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;
-------------
student |
-------------|
studentID{PK)|
Firstname |
Lastname |
dateEnrolled |
dateWithdrawn|
dateGraduated|
-----------------------------
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
cwMark |
ExamMark |
---------------- 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;
解决方案 »
- 日期类型转换
- archive模式下的生产库日常操作经验分享
- 出现ORA-03114错误后为什么用GetState检测仍然是连接状态
- 在PL/SQL 下自定义函数的疑问
- 急啊,PL/SQL里的EBS是什么
- 关于pl/sql几个问题
- 如何让Oracle启动时自动加载自定义的初始化参数配置文件?
- oracle 10g有没有9i那样的客户端?该如何配置使能用pl/sql连接??
- 急求解决办法,谢谢,就10分了
- ORACLE有没有数组型字段,比如WORD[1000],若有怎么使用,若无怎么实现,请详细指教,分不够可另开帖再加,谢谢!
- 刚接触ORACLE,请教数据连接问题,弄了一天了,没弄好
- 如何查询刚刚插入记录的主键?
registration |
----------------|
studentID(PK,FK)|
moduleID(PK,FK) |
year(PK) |
semester(PK) |
cwMark |
ExamMark |
----------------
so,:new.lastname根本不存在,肯定报错。 registration 和student 关联,仅需要studentID即可。
select count(moduleid) into moduleCount
from registration r,student s
where r.studentid=:new.studentid
and r.studentid=s.studentid
and (r.semester='2' or r.semester='1'); 如果想得到lastname,再去student表里读。另外,这个需求没必要使用trigger,外部判定就可以。
加上year(PK)semester(PK) ,他们是共同的约束构成这个表的主键,没问题的
LINE/COL ERROR
18/1 PL/SQL: Statement ignored
18/47 PLS-00201: 必须声明标识符 'S.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 r.studentid=:new.studentid
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 '|| s.lastname|| ' ID '||studentid || ' has register 4 modules already, the number of module for a particular semester should not exceed 4');
end;