Triggers in Oracle SQL Developer

Question

I need to set a trigger to change someone's grade in the "GRADE" table from null to a 'D' if they are deleted from the 'STUDENT' table. So far I have:

create or replace trigger delete_from_student
after delete on student 

for each row
begin

update grades
set grade = 'D'
where grade = ''

end delete_from_student;

I know it can't be right because I'm getting the red underline on "end." I also figure there should be someone to make sure that only the student who got deleted from the "STUDENT" table should have their grade changed to a D. So far we have only done where if you add someone to table A, then they are automatically put in table B, so I'm getting tripped up on how to use an UPDATE instead of an INSERT with a trigger and making sure to only affect one row in the "GRADES" table. Thank you for your help!

Edit: I've been reading more pages and I finally figured out the syntax to get the trigger to fire:

create or replace trigger delete_from_student
after delete on student 

for each row
begin

update grades set
grade = 'D'
where grade is null;

end delete_from_student;

Now my problem is that, like I was afraid, it affects every row on the "GRADES" table, instead of just the student that was deleted. So now my question is, how can I make sure I only affect the row for the student I deleted?

Second Edit: The point of the exercise is just to teach us how triggers work, not to create a functional DB. The professor prefaced it with saying you would not do this in the real world for reasons listed by other people, but he's just teaching us about triggers.

Answer 1

One question: WHY are you even doing this? It makes absolutely no since. As setup clearly GRADES is a child table of STUDENT and there should have a FK from Grades to Student. If that FK does not exist they your willing to accept orphaned Grades rows; that is what your working so hard to create. If that FK does exist then there are 3 possibilities for ON Delete clause.

1. Cascade. In which case the Grand row(s) you have just updated is about to be deleted itself. So what is the point of updating it?
2. Restrict (default if on delete not specified). In which case the exception "ORA-02292: integrity constraint ..." is about to be thrown. This negates both the Grades update and the Student delete.
3. Set Null. In which case you have just created the orphaned row as if the FK didn't exist.

So at the end of the process you have exception or an orphaned GRADES row that you cannot determine the student it belonged with, because the STUDENT row no longer exists. If so you obviously have a broken data model.

Answer 2

Use the student_id column if it exists in your table and use a before delete trigger. Here :old.student_id is the value of the column in the deleted row.

  create or replace trigger delete_from_student
  before delete on student 

   for each row
   begin

   update grades set
   grade = 'D'
   where grade is null
   and student_id=:old.student_id;

 end delete_from_student;