MySQL count and comparison inside trigger

asked 2014-12-15 14:05:53 -0500

I have the following trigger:

delimiter //
create trigger sales
after insert on orders
for each row
SELECT count(quantity) as bought FROM orders
GROUP BY id, date
HAVING COUNT(bought);
IF (bought>50) THEN
SIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'exceeded' + id, MYSQL_ERRNO = 1000//
delimiter ;

When I use this query:

SELECT count(quantity) as bought 
FROM orders
GROUP BY id, date
HAVING COUNT(bought);

I'm able to get this table:

bought
43
2
56

I want to compare if any of these numbers are bigger than 50 and if any I want to show id associated with it. I'm getting error

 [Err] 1415 - Not allowed to return a result set from a trigger.
edit retag flag offensive close merge delete