Problem

Given a table of employee data, write a trigger that will update the total salary for each department when an employee's salary is updated.
Example input:
| Department | Employee | Salary |
|------------|----------|--------|
| IT         | John     | $50,000 |
| IT         | Jane     | $60,000 |
| Sales      | Bob      | $40,000 |
Example output:
| Department | Total Salary |
|------------|--------------|
| IT         | $110,000     |
| Sales      | $40,000      |

Solution

CREATE TRIGGER update_salary
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
  UPDATE department
  SET total_salary = total_salary + NEW.salary - OLD.salary
  WHERE department.id = NEW.department_id;
END;

/*

The trigger above is optimal because it only updates the department table when the employee table is updated.

*/