Is it possible to update an "order" column from within a trigger in MySQL?

Question

We have a table in our system that would benefit from a numeric column so we can easily grab the 1st, 2nd, 3rd records for a job. We could, of course, update this column from the application itself, but I was hoping to do it in the database.

The final method must handle cases where users insert data that belongs in the "middle" of the results, as they may receive information out of order. They may also edit or delete records, so there will be corresponding update and delete triggers.

The table:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `seq` int(11) unsigned NOT NULL,
  `job_no` varchar(20) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

And some example data:

mysql> SELECT * FROM test ORDER BY job_no, seq;
+----+-----+--------+------------+
| id | seq | job_no | date       |
+----+-----+--------+------------+
|  5 |   1 | 123    | 2009-10-05 |
|  6 |   2 | 123    | 2009-10-01 |
|  4 |   1 | 123456 | 2009-11-02 |
|  3 |   2 | 123456 | 2009-11-10 |
|  2 |   3 | 123456 | 2009-11-19 |
+----+-----+--------+------------+

I was hoping to update the "seq" column from a t rigger, but this isn't allowed by MySQL, with an error "Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger".

My test trigger is as follows:

CREATE TRIGGER `test_after_ins_tr` AFTER INSERT ON `test`
  FOR EACH ROW
BEGIN
  SET @seq = 0;
  UPDATE
    `test` t
  SET
    t.`seq` = @seq := (SELECT @seq + 1)
  WHERE
    t.`job_no` = NEW.`job_no`
  ORDER BY
    t.`date`;
END;

Is there any way to achieve what I'm after other than remembering to call a function after each update to this table?

Answer 1

What about this?

CREATE TRIGGER `test_after_ins_tr` BEFORE INSERT ON `test`
  FOR EACH ROW
BEGIN
  SET @seq = (SELECT COALESCE(MAX(seq),0) + 1 FROM test t WHERE t.job_no = NEW.job_no);
  SET NEW.seq = @seq;
END;

Answer 2

From Sergi's comment above:

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html - "Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

I can't mark his answer as accepted, as the answer itself isn't correct, but I'd like to get my accept rate improved. I believe with more reputation I could edit his answer...