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

Answer 1

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.