CREATE or replace TRIGGER Maxscore
BEFORE INSERT ON grade
REFERENCING NEW AS new
FOR EACH ROW
DECLARE
mins INT;
BEGIN
SELECT min_score
INTO mins
FROM courses
WHERE id = new.course_id
;
IF new.grade < mins
THEN
new.grade := 100 - mins;
END IF;
END;
I do not no why I get this error:
Error(3,1): PL/SQL: SQL Statement ignored
I run the following query, but it does not return any rows:
select *
from user_errors
where name = 'maxscore' and type = 'TRIGGER';
The name in where name= must be uppercase, Oracle by default has all object names in uppercase unless explicitly quoted (which is a bad idea to do in general).
Better is something like:
select err.name
, err.type
, err.line
, err.position
, err.text
from user_errors err
join user_objects ojt
on ojt.object_name = err.name
and ojt.object_type = err.type
where attribute != 'WARNING'
and message_number not in (905,304,364)
and lower(err.text) not like '%ignored%'
and lower(err.text) not like '%terminated%'
and lower(err.text) not like '%triggering view is invalid%'
and lower(err.text) not like '%has errors%'
order
by ojt.last_ddl_time desc
, err.name
, err.line
/
The line numbers with triggers start AFTER the trigger definition, so at declare.
Actual problem
Add colon before EACH reference to new in trigger definition as in:
drop table grade
create table grade(a number, grade number, course_id number)
CREATE or replace TRIGGER Maxscore
BEFORE INSERT ON grade
REFERENCING NEW AS new
FOR EACH ROW
DECLARE
mins INT;
BEGIN
SELECT min_score
INTO mins
FROM courses
where id = :new.course_id
;
IF :new.grade < mins
THEN
:new.grade := 100 - mins;
END IF;
END;
