I have a table that looks like this:
create table test (student_id, course, failures);
I have a program (which I can't change) that every time a student fails in a course inserts a record like (34333, "math", 1).
As you can see the number of failures is 1.
So today if a student fails 300 times I will have 300 records with the same student_id, course.
I want to create some trigger that instead of inserting the new row, it will check if there is already a line with the same student_id and course and if there is, it will increment its failures column instead of inserting a new row.
This way I will have instead of 300 lines only one line like:
(34333, "math", 300)
Since this table can be updated with thousands of records per minute, and the table is very big, I wanted the trigger to convert the insert operation into an update operation without translating every insert to:
1. The original insert.
2. An update of the existing row.
3. Delete the new row.
Since this will hurt performance.
Is there a way to convert an insert operation directly into a an update ?
I was thinking of defining a unique index on student_id, course and change the insert that it will contain the previous failures.
Any help will be appreciate. Sorry for the long story....
Avner
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
