SQL Trigger for updating values from a query by id

I have three tables:

• employee with id(employee id), numOfTickets
• tickets with date, asset(id) and some more fields
• control with id(employee id), asset(id)

when a new ticket is created it automatically inserts a row into tickets and control. Now I want to create a Trigger or something similar to automatically update the number of open tickets for each employee.

A Query for selecting the employee id and the number of open tickets looks like:

SELECT employee, COUNT(*) FROM control GROUP BY employee

This works. But I tried to make a Trigger AFTER INSERT into control like this:

CREATE DEFINER=`root`@`localhost` 
TRIGGER `TEST_b` 
AFTER INSERT ON `control` 
FOR EACH ROW 
UPDATE `employee` SET `numOfTickets` = ( 
    SELECT COUNT(*) FROM control WHERE employee=( 
        SELECT employee FROM control GROUP BY employee));

But it does not work. I don’t know if this is even possible with a Trigger. So the goal is to set the number of open tickets for each employee by id returned from the first Query.

I would need some kind of Iteration.

Answer

You should only set numOfTickets for the same employee that’s being updated, and get the count for that employee.

CREATE DEFINER=`root`@`localhost` 
TRIGGER `TEST_b` 
AFTER INSERT ON `control` 
FOR EACH ROW 
    UPDATE `employee` SET `numOfTickets` = ( 
        SELECT COUNT(*) FROM control WHERE employee_id=NEW.employee_id
    )
    WHERE employee_id = NEW.employee_id;