The department is the parent table while the employee is the child table. Employees have a salary column, and the sum of salaries in a given department should not exceed the budget column value of the associated department table record.
However, CHECK constraints are limited to the columns of the table for which we defined the custom constraint. If we want to implement a more complex data integrity rule, then a database trigger is a much more suitable alternative.
Therefore, we are going to create the following check_department_budget trigger function, which verifies that the sum of salaries in a given department does not exceed the allocated budget.
CREATE OR REPLACE FUNCTION check_department_budget()
RETURNS TRIGGER AS $$
SELECT INTO allowed_budget budget
WHERE id = NEW.department_id;
SELECT INTO new_budget SUM(salary)
WHERE department_id = NEW.department_id;
IF new_budget > allowed_budget
RAISE EXCEPTION 'Overbudget department [id:%] by [%]',
(new_budget - allowed_budget);
$$ LANGUAGE plpgsql;
Notice that the check_department_budget PostgreSQL function returns a TRIGGER object since we want this trigger function to be executed in the context of table row INSERT or UPDATE events.
Now, we also need to define a PostgreSQL trigger that is executed after each INSERT or UPDATE on the employee table:
CREATE TRIGGER check_department_budget_trigger
AFTER INSERT OR UPDATE ON employee
FOR EACH ROW EXECUTE PROCEDURE check_department_budget();
And, that’s it. We now have a trigger in place that, on every employee table INSERT or UPDATE, checks whether the sum of salaries does not exceed the department budget.
Assuming we have the following IT department with a budget of 100000:
| id | budget | name |
| 1 | 100000 | IT |
And, we have three employees currently working in the IT department:
| id | name | salary | department_id |
| 1 | Alice | 40000 | 1 |
| 2 | Bob | 30000 | 1 |
| 3 | Carol | 20000 | 1 |
Notice that the current sum of salaries is 90000, so, currently, we are 10000 under budget.
Now, let’s consider that Alice and Bob want to run the following operations:
Alice wants to give a year-end raise of 10% to all employees in the IT department, which should raise the budget from 90000 to 99000
Bob wants to hire Dave for 9000, therefore raising the budget from 90000 to 99000
If both Alice and Bob commit their transactions, we risk going over the budget. However, thanks to the check_department_budget trigger function, one of the transaction will be rolled back, as illustrated by the following diagram:
When Bob hires Dave, the budget was 90000, so his INSERT statement is validated by the check_department_budget trigger function.
However, when Alice wants to execute the UPDATE, the budget is now 99000, so, if the UPDATE succeeds, the new budget value will be 108900. Luckily, the check_department_budget trigger function will not validate the UPDATE statement, and an exception will be thrown, and Alice’s transaction will be rolled back.
I'm running an online workshopk on the 14th of May about The Best Way to Fetch Data with Java Persistence and Hibernate.
Database trigger functions are very useful when it comes to applying consistency rules that involve multiple tables.
Many times, application developers try to enforce these rules in the application layer by using a read-modify-write data access pattern. However, on the default Read Committed isolation level, reading the sum of salaries in the application does not guarantee that the sum will be the same at the end of the transaction. So, without adding an extra pessimistic or optimistic locking mechanism, the read-modify-write will just lower the probability of a data integrity issue, without really eliminating it.
So, adding the data integrity rules at the database level is the best approach, as, in case of a constraint violation, the current running transaction will be rolled back, and the databases will never be left in an inconsistent state.