sql - Oracle Trigger copy data from one table to another

Question

Student table : student ID, student name, student contact
Evaluation: EvalID, Eval number

student gives an evaluation of each lesson, from 1-7. I need to creat a trigger which copies student information, and the evaluation they gave into another table if they give a evaluation of 7

What i have done is

Create or replace trigger test
After Insert on evaluation
when (eval_number = 0)
Begin

I dont know what to do from here.... any ideas? and is my first bit of trigger correct?

Answer 1

Following tables:

CREATE TABLE student(
studentId INTEGER,
student_name varchar2(100),
student_contact varchar2(100));

CREATE TABLE evaluation(
evalId  INTEGER,
eval_num INTEGER,
student_id INTEGER)

create table eval_audit(
studentId INTEGER,
student_name VARCHAR2(100),
student_contact VARCHAR2(100),
eval_num INTEGER)

Trigger to insert data from student when there is a evaluation with 7:

CREATE OR REPLACE TRIGGER AIEvaludation
AFTER INSERT ON evaluation
FOR EACH ROW
WHEN (new.eval_num = 7)
DECLARE
    CURSOR curStudent IS
        SELECT studentid, student_name, student_contact
          FROM student
         WHERE studentid = :new.student_id;
    --
    vRowStudent curStudent%ROWTYPE;
BEGIN
    OPEN curStudent;
    FETCH curStudent INTO vRowStudent;
    CLOSE curStudent;
    INSERT INTO eval_audit
    (studentid, student_name, student_contact, eval_num)
    VALUES (vRowStudent.studentid, vRowStudent.student_name, vRowStudent.student_contact, :new.eval_num);
END AIEvaludation;