CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Afa
Afa

Reputation: 81

Error(3,1): PL/SQL: SQL Statement ignored

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';

Upvotes: 1

Views: 3221

Answers (1)

Guido Leenders
Guido Leenders

Reputation: 4262

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;

Upvotes: 4

Related Questions