update a table by using a trigger after inserting data

Question

I have the following tables:

Professor
id_professor
name
number_courses_assigned

Course
id_course
name_course
id_professor

I want to have a trigger that after I insert or delete a course into the table of the same name, the field number_courses_assigned from the table Professor gets updated immediately. So far I have done the following:

create or replace trigger update_number_courses
after insert on course
declare 
  codP course.id_professor%type;
  countC professor.number_courses_assigned%type;
begin
  select course.id_professor into codP from course;
  select professor.number_courses_assigned into countC from professor where id_professor=codP;
  update professor set number_courses_assigned=countC+1 where id_professor=codP;
end;

The problem that I have is that when I want to insert a row into the Course table the Oracle SQL Developer returns me an error and does not allow the insertion of the new record, the message I got is:

Row 5: ORA-01422: exact fetch returns more than requested number of rows

Any help?

and what's the error ?

are
– are

2015-11-21 21:45:38 +00:00
Commented Nov 21, 2015 at 21:45 — are
– are, Commented Nov 21, 2015 at 21:45

are · Accepted Answer · 2015-11-21 22:11:33Z

1

there's error in your code this select returns more than one record

select course.id_professor into codP from course;

I'd recommend you change your trigger to row-level-trigger and get new values via :new

more about the triggers with examples read here http://www.techonthenet.com/oracle/triggers/after_insert.php http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm

changes in your code will be:

create or replace trigger update_number_courses
after insert on course
FOR EACH ROW
declare 
begin
  update professor 
    set number_courses_assigned=number_courses_assigned+1 
    where id_professor=:new.id_professor;
end;

edited Nov 21, 2015 at 22:11

answered Nov 21, 2015 at 22:04

are

2,6153 gold badges24 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dave Costa Over a year ago

ah, I missed that it was a statement-level trigger. good catch.

Dave Costa · Accepted Answer · 2015-11-24 01:07:32Z

This line in the trigger is probably causing your error:

select course.id_professor into codP from course;

Since this has no where clause, it will select all rows from the table. If there is more than one row (including the new one you're inserting), the error will be thrown. A SELECT .. INTO must return exactly one row.

A better way to do this would be to make it a row-level trigger (AFTER INSERT FOR EACH ROW) In that case, the values in the row that you've just inserted can be accessed in the trigger via an implicit row variable :NEW. So you could simply write:

codP := :new.id_professor;

(There's really no need to assign it to a local variable at all, but you can do that for clarity if you like.)

Another note regarding the rest of the trigger is that it's not necessary to select the current number of courses assigned then use the select value in the update. You can simply write the update like this:

update professor set
number_courses_assigned=number_courses_assigned+1
where id_professor=codP;

Collectives™ on Stack Overflow

update a table by using a trigger after inserting data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related