I am trying to make an after insert trigger that will add the bnumber
to the table available with the rack-id
that has the most open slots. To get this I need to take the total number of slots – the number of instances and if there are no free slots then the insert will fail. Below is what I have came up with so far but I don not know if it is at all correct and right now it throws 1111: Invalid use of Group function
.
BEGIN insert into Available SELECT new.bnumber,max('y.rack-id') From (select count(*) as 'Available.rack-id' from Available inner join Rack group by 'Available.rack-id' ) y where max(y.rack-id) > 0; END
updated non error code but is not inserting anything into the table Available
insert into Available SELECT new.bnumber,max('y.rack-id') From (select count(*) as 'Available.rack-id' from Available inner join Rack on 'Rack.rack-id' = 'Available.rack-id' group by 'Available.rack-id' ) y having max('y.rack-id') > 0;
Tables:
CREATE TABLE `Bike` ( `bnumber` int NOT NULL, `make` varchar(64) DEFAULT NULL, `color` varchar(8) DEFAULT NULL, `year` int DEFAULT NULL, PRIMARY KEY (`bnumber`) ) CREATE TABLE `Rack` ( `id` int NOT NULL, `location` varchar(256) DEFAULT NULL, `num-holds` int DEFAULT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `Available` ( `bnumber` int NOT NULL, `rack-id` int DEFAULT NULL, PRIMARY KEY (`bnumber`), KEY `bnumber_idx` (`rack-id`), KEY `bnumber_idx1` (`bnumber`), CONSTRAINT `bnumber` FOREIGN KEY (`bnumber`) REFERENCES `Bike` (`bnumber`), CONSTRAINT `rack-id` FOREIGN KEY (`rack-id`) REFERENCES `Rack` (`id`) ) Bike bnumber,make,color,year '1', muller, yellow, 2017 '2', muller, green, 2018 '3', muller, red, 2020 Rack id,location,num-holds (num-holds is the number of empty slots) '0', 'Louisville', '3' '1', 'Clarksville', '4' Available bnumber,rack-id '1', '0' '2', '0' '3', '1'
so in this examples when you add a new bike it will be something like bnumber
4. It will then get the count of instances of rack-id
so 2 instances of rack-id
0 and 1 instance of rack-id
1. Then it will subtract the the instances from the corresponding num-holds
. so this step would be rack-id 0 has 3 num-holds - 2 instances = 1 empty slot available
. rack-id 1 has 4 num-holds - 1 instance = 3 empty slots
, so bnumber
4 would be inserted into rack with the rack-id
1.
Advertisement
Answer
First, you are missing a ON
clause:
from Available inner join Rack
it will look something like this:
ON Available.col = Racl.col
Then, in your outer query:
insert into Available SELECT new.bnumber ,max('y.rack-id') From ( ) y where max(y.rack-id) > 0;
You are missing a GROUP BY new.bnumber
and also HAVING clause is use instead WHERE
when you need to filter by aggregation.
DECLARE rowcount INT; select count(*) INTO rowcount from Available inner join Rack on 'Rack.rack-id' = 'Available.rack-id' group by 'Available.rack-id' ORDER BY 'rack-id' LIMIT 1; insert into Available ('bnumber','rack-id') SELECT new.bnumber, rowcount