Good day :)

There is some database in which there are two tables: employee and department . The employee table stores information about the employee, which is logical, and the department table, in addition to the heap of fields, contains a field that indicates the number of employees of a department. The task is to write a trigger that makes a change in the "Number of employees" field after adding a new employee to the "Employees" table. In short, then: added employee, using a trigger, increase by 1 the number of employees in another table . And there was such a nuance, if initially the tables are not consistent ( in the “Employees” table 20 people from department X, and in the “Department” table in the field, the number of employees is all, for example, 6 ), then how to reconcile the tables using the stored procedure ? Otherwise, the triggers do not make sense, your cap.

Thanks for attention :)

  • And why with the help of the procedure, if this is done in one request? - Mike
  • Togo requires a task :) - Mark

1 answer 1

In this task, writing a trigger or procedure brings consistency problems and unnecessary entities to the database without the rationale for this solution.

Store the aggregated quantity is required only where the sample request takes a long time. For example, financial history and total wallet balance based on this story. A couple of million transactions and the calculation of the balance can turn into a long wait, and the balance the user had to show yesterday.

If you want to get the number of employees in the department, make it a query, something like the following:

SELECT departament.name, COUNT(*) FROM departament JOIN staff ON staff.departament_id = departament.id GROUP BY departament.id

Avoid aggregating values ​​in the fields of tables, and use them when it really increases query performance by several times , by reducing the number and time of operations performed, in other cases, use queries using indexes.

UPDATE to comment

Then the query is even simpler:

  UPDATE departament SET count = (SELECT COUNT(*) FROM staff WHERE departament_id=departament.id)

Here is the procedure:

  DELIMITER // CREATE PROCEDURE `correct_count`() BEGIN UPDATE departament SET count = (SELECT COUNT(*) FROM staff WHERE departament_id=departament.id) END//

sqlFiddle

  • Smart and informative answer, thank you :) But the fact is that I am just a student and I have to carry out the tasks given to me. It follows from the assignment that the aggregate value should be stored, and the entire assignment is in writing this stored function :) - Mark
  • @Mark, then your entire stored procedure is this request with the UPDATE operation - updated answer - Firepro