Using a single SQL Trigger to copy multiple rows from a single deletion?
<p>I'm trying to remove a single row from a table, which can trigger and copy all relative information (multiple rows) to a Third Table. I've constructed a simple example below:</p>

<hr>

<p><strong>Table: Students</strong></p>

<pre><code>NAME     AGE
-----    ------
Mark     19
Jim      21
Scott    22
</code></pre>

<p><strong>Table: Grades</strong></p>

<pre><code>NAME     GRADE
-----    ------
Mark     95
Mark     98
Jim      60
Scott    100
Scott    79
Scott    77
</code></pre>

<p><strong>Table: Backup (what I what the Trigger to accomplish)</strong></p>

<pre><code>SQL&gt; DELETE FROM STUDENTS WHERE NAME='MARK';
SQL&gt; SELECT * FROM BACKUP;

NAME     AGE      GRADE
-----    ------   ------
Mark     19       95
Mark     19       98
</code></pre>

<p>In the above example, removing the row "Mark...30" would also remove the two rows in the second table with "Mark" as the name. I'm not sure how to do this. I am able to compile the trigger code, but calling "DELETE FROM STUDENTS WHERE NAME='MARK'" returns a too-many-rows error. Below is my code</p>

<p><strong>My Code</strong></p>

<pre><code>CREATE OR REPLACE TRIGGER save_the_grades
BEFORE DELETE
ON grades
FOR EACH ROW

DECLARE
     v_grade number(3);
BEGIN

SELECT grade INTO v_grade FROM Grades WHERE Names=:old.name;

INSERT INTO backup
(name, age, grade)
VALUES
(:old.name, :old.age, v_grade);
END;
</code></pre>