Update same row in a trigger in mysql

Question

I have a table SCHEDULES_CLASS, 2 of it's fields are CLASS_ID(foreign key from the CLASS table), STATUS and NUMBER_OF_ENROLLED_STUDENTS. I have other table CLASS having 1 field as MAX_SEATS.

When the NUMBER_OF_ENROLLED_STUDENTS in a scheduled class equals the MAX_SEATS available for the respective CLASS, I need to change the status of that SCHEDULES_CLASS to "FULL" from the initial status which is "OPEN".

I created a trigger for this purpose as follows:

USE mydb;
DELIMITER ##
dROP TRIGGER IF EXISTS updateClassStatusTrigger ##
CREATE TRIGGER updateClassStatusTrigger
BEFORE UPDATE ON SCHEDULED_CLASS
  FOR EACH ROW
  BEGIN
        UPDATE SCHEDULED_CLASS SET STATUS="FULL" WHERE CLASS_ID=NEW.CLASS_ID
        AND EXISTS (SELECT 1 FROM SCHEDULED_CLASS sc, CLASS cl WHERE cl.CLASS_ID=NEW.CLASS_ID AND NEW.NUMBER_OF_ENROLLED_STUDENTS=cl.MAX_SEATS);
END##

I am using phpmyadmin, and I get the following error, when any update takes place on the SCHEDULED_CLASS table:

#1442 - Can't update table 'SCHEDULED_CLASS' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any ideas, how to accomplish this task? i.e. update the status of the scheduled class to 'FULL' whenever the enrolled students number reaches the max available seats.

Thanks

why you don't do this in a business layer instead of using triggers? — CodeZombie, Jul 14 '12 at 12:39
I am doing this in php+mysql, calling the mysql queries. I thought as this is one time updation on a fixed condition triggers would be a good chioce. Thanks. — mtk, Jul 14 '12 at 13:19

score 9 · Accepted Answer · answered Jul 14 '12 at 12:13

The way to update same row is to simply SET NEW.column_name = some_Value. To simplify your trigger, consider:

CREATE TRIGGER updateClassStatusTrigger
BEFORE UPDATE ON SCHEDULED_CLASS
FOR EACH ROW
  SET NEW.STATUS='FULL'

Now, in addition, you need only to update value given some condition. This can't be done within the same query here since we are no longer using a standard UPDATE. So, split:

USE mydb;
DELIMITER ##
dROP TRIGGER IF EXISTS updateClassStatusTrigger ##
CREATE TRIGGER updateClassStatusTrigger
BEFORE UPDATE ON SCHEDULED_CLASS
  FOR EACH ROW
  BEGIN
        SELECT EXISTS (SELECT 1 FROM SCHEDULED_CLASS sc, CLASS cl WHERE cl.CLASS_ID=NEW.CLASS_ID AND NEW.NUMBER_OF_ENROLLED_STUDENTS=cl.MAX_SEATS) INTO @row_exists;
        IF @row_exists THEN
          SET NEW.STATUS='FULL';
        END IF;
  END##

I hope I don't have some syntax error in the above. I've tested it on my own tables, but have then edited to fit your schema.

Your own attempt failed because from within a trigger, MySQL does not allow you to modify the table from which the trigger executed. It could not analyze your query to determine what you essentially tried can ba accomplished in a valid way - it doesn't even try. It simply forbids modifying same table.

That perfectly helped. Thanks a lot ! – mtk Jul 14 '12 at 13:20 — mtk, Jul 14 '12 at 13:20

Update same row in a trigger in mysql

1 Answers1