CODEWITHSUNDEEP
javamysqltriggers
Perdomoff
Perdomoff

Reputation: 957

How to make a Mysql insert statement conditionally occur depending on the result of a 1 Select Statement

Consider what my Ajax call looks like (below the code I explain the issue):

 $.ajax({
                type : "GET",
                url : "servlet",
                data: {course_name_param:selectedOption},
                dataType:"json",
                success : function(data) {
                    $('#startDate').empty();
              $('#startDate').append('<option value="">Select Start Date</option>');
     $.each(data, function(index, jsonData) {

  /*Here below is where I check availability*/
 if(jsonData.students_registered < jsonData.max_size){

      /*Display available courses*/
  }
}

I have a MySQL table that holds information on courses available for educational sessions. 1 of these fields is available slots(maximum capacity).

Registration is an insert statement. I need to control this by checking the slots actually available when inserting.

Currently, I perform the check with Ajax calls to this table. The check is useless when multiple users load the page relatively close in timing(less than a minute).

Question:

Upvotes: 1

Views: 134

Answers (2)

Perdomoff
Perdomoff

Reputation: 957

A "before insert" trigger on the table where the user inserts. I went with this approach. Then handling the SQLException in the application:

    CREATE DEFINER=`root`@`localhost` TRIGGER before_insert_registration
BEFORE INSERT ON registration 
FOR EACH ROW
BEGIN

DECLARE num_rows INTEGER;

Declare x INTEGER;
SET x = NEW.course_id;

SELECT 
    COUNT(*)
INTO num_rows FROM  courses
WHERE
    course_id = NEW.course_id
        AND maxsize > students_registered;

    IF num_rows < 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Testing Custom Fail-Over';
    END IF;
END

Upvotes: 0

Drazen Bjelovuk
Drazen Bjelovuk

Reputation: 5472

You could simply structure your query in such a way that you only pull back the courses with open slots. Something like this might give you an idea:

SELECT   courses.title
FROM     courses 
WHERE    students_registered < max_size

If you want to ensure validation on the database, you can use a conditional insert:

INSERT INTO enrollments(course_id, student_id)  
SELECT <your course id>, <your student id> FROM dual
WHERE EXISTS (
  SELECT * FROM courses
  WHERE  id = <your course id>
  AND    students_registered < max_size
)

Upvotes: 1

Related Questions