还是上次那个触发器的问题,不过修改后单独再发一次,把表结构也弄出来了
-------------
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;
解决方案 »
- 使用oracle sql developer定义包,报错误: PLS-00103: 出现符号 "CREATE"
- 如何查看oracle某用户近期执行的SQL
- dblink乱码问题 [急]
- 一个困惑的问题
- request: set autotrace traceonly;response:Cannot SET AUTOTRACE ??? what is the matter?
- 有人可以跟我讲讲,隐式游标吗
- 请教:用SQLPlus导入*.sql文件后,所建立的表没能OMS中显示(在线等)
- 求助:01460错误。
- oracle的安裝
- 有谁知道dmp文件的格式,给个提示?
- 刚接触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;