SQL Query Assistance

[karentutor1]

Hi,

I am trying to insert a row one table (B) based on a number value in table (A). i.e. a max_registration value.

If the Event table (A) has a maximum number of registrants of say 10.

And someone tries to register for the event in table (B). The system will count the number of registrants already completed prior to doing the insertion.

If the number of registrants is less than the maximum as described in the event description (event), the registration in table B goes ahead. If not, it is rolled back.

I am presuming some form of transaction and am throwing in some valid foreign key numbers.

The tables are:

Table A event (Event - description of event)
Event_id int primary not null auto increment
user_id mediumint not null foreign key (matching Table C)
title varchar(56) not null,
people_max tinyint not null

Table B event_registration (Registration for event)
event_registration_id primary not null auto increment
event_id (foreign key matching table A)
user_id (foreign key, matching table C)

Table C User (Create Event or Register for event)
user_id primary not null auto_increment

Transaction
Start transaction

I need help with the following SQL syntax: (this is just a ‘guess’)
INSERT INTO event_registration (event_registration_id, event_id, user_id) VALUES (NULL, 1,1) IF (count(event_registration.event_id) <= SELECT (event.people_max)) WHERE event_registration_id.event_id = event.event_id;

(if not rollback)

Hope that makes sense!
Any assistance would be appreciated!

thx
Karen

[swampBoogie]

I would put this type of functionality in an insert trigger on B that would raise an exception if the limit is reached. The syntax for defining such a trigger depends on which DBMS that you are using.

[karentutor1]

Hi,

Ok, would you elaborate a little?

I am using MariaDB 10.1.10
I using Workbench (but just to help generate SQL queries).

thx
Karen

[swampBoogie]

Somewhat like

create trigger checkLimit before insert on event_registration
for each row
begin
    declare c1,c2 int;
    set c1 = (select count(*)
                from event_registration
               where event_registration.event_id = new.event_id);
    set c2 = (select people_max
                from event
               where event.event_id = new.event_id);
    if  c1 >= c2 then
       signal sqlstate 'UE123'
          set message_text = concat('Event fully booked. Only ',cast(c2 as varchar(10)),' people can attend.');
    end if;
end

New denotes a record variable that is populated by the DBMS with values from the statement that causes the trigger to be executed. This variable has fields that corresponds to each column in the table on which the triiger is defined.

[karentutor1]

Thank you ever so much for your help!

I will experiment with it and alter it to make it work.

Thx again

ps - my background is business MBA (Oxford) let me know if I may help too!

Karen