Pre-define the number of rows in mysql-CodePudding

I am making a cinema booking system for my dbms project. for the bookings table , I only want a certain number of inserts in a table to be possible. How can i achieve this. So basically I want to predefine the number of rows in a table while creating it.

CodePudding user response：

You COULD do exactly what you asked for...

by creating a trigger which counts the entries of your table, compares the amount to the number you want and aborts the insert with an error message in case the allowed row count is reached. Something like this (in this case, only two rows are allowed):

CREATE TRIGGER chkRowCount
BEFORE INSERT
ON bookings
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO @cnt FROM bookings;
  IF @cnt >= 2 THEN
    SIGNAL SQLSTATE '02000' 
    SET MESSAGE_TEXT = 'Insert aborted, maximum row count reached!';
  END IF;
END;

I created an example here: db<>fiddle

Don't forget to add delimiters if required.

...BUT...

you should verify if this is really what you want to do. If yes, ok, stop reading here. But in usual cinemas, this will likely not be a sufficient solution. So if you're still reading this, let's assume there are several rooms in your cinema and there will be shown several movies per room and per day. So checking the total number of bookings means nothing. Maybe even bookings for the whole family are possible, so four or five seats will be affected by one single booking. I recommend to do not check and react on all these things in the database at all. Such things should be done in your application. Thus, you don't need to do a check every time you do a booking or to create a trigger with five or ten different cases. You could just count and update the amount of already booked seats per movie/time and per room. And only if this amount exceeds a given amount to compare, you will react on this in your application. In best case, you will disable the booking button as long as no previous booking will be canceled because of whatever reason. The maximum amount of possible bookings - or better possible booked seats - per room and per movie can be saved as example in a config file. And you can make sure to count the number of free/booked seats, not the number of bookings. Let's stop here, I think you will find your way, but I hope this answer will help.

CodePudding user response：

You can use a trigger to do this.

CREATE TRIGGER booking_trigger BEFORE INSERT ON bookings
FOR EACH ROW
BEGIN
  IF NEW.booking_date > NOW() THEN
    SET NEW.booking_date = NULL;
  END IF;
END;