Автоматическая смена статуса сотрудника по датам отпуска: триггер и функция

При разработке корпоративных систем часто возникает необходимость автоматически обновлять статус сотрудника в зависимости от внешних условий. В данной статье мы разберём, как реализовать механизм, при котором статус сотрудника меняется на «в отпуске», если текущая дата попадает в диапазон дат отпуска, и возвращается обратно на «действующий» после окончания отпуска. Рассмотрим пример на Java (JPA/Hibernate) и SQL (триггер и функция).

Постановка задачи

Имеются две сущности: Employee (сотрудник) и Vocation (отпуск). У сотрудника есть поле employeeStatusENUM, которое может принимать значения: 0 - «действующий», 1 - «в отпуске», 2 - «закрыт». Значения 0 и 2 устанавливаются вручную через HTML-форму, а значение 1 должно присваиваться автоматически, если текущая дата входит в интервал отпуска (дата начала и дата окончания включительно).

Сущность Vocation содержит дату начала отпуска (startOfVocation), количество дней (daysOfVocation) и дату окончания (endOfVocation). Отпуск может быть разбит на несколько частей (коллекция в Employee). Обновлять существующую запись отпуска нельзя - только создавать или удалять.

Реализация на стороне базы данных: триггер и функция

Оптимальное решение - использовать триггер на таблице vocation, который при вставке или удалении записи пересчитывает статус сотрудника. Для проверки условия напишем вспомогательную функцию.

Создание функции для проверки активного отпуска

CREATE OR REPLACE FUNCTION check_active_vacation(p_employee_id INT) RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM vocation v
        WHERE v.employee_id = p_employee_id
          AND CURRENT_DATE BETWEEN v.date_start AND v.date_end
    );
END;
$$ LANGUAGE plpgsql;

Функция возвращает TRUE, если у сотрудника есть хотя бы один отпуск, покрывающий текущую дату.

Триггер на вставку и удаление записей отпуска

Создадим триггерную функцию, которая обновляет статус сотрудника в зависимости от результата check_active_vacation:

CREATE OR REPLACE FUNCTION update_employee_status() RETURNS TRIGGER AS $$
DECLARE
    v_employee_id INT;
BEGIN
    -- Определяем ID сотрудника в зависимости от операции
    IF TG_OP = 'INSERT' THEN
        v_employee_id := NEW.employee_id;
    ELSIF TG_OP = 'DELETE' THEN
        v_employee_id := OLD.employee_id;
    END IF;

    -- Обновляем статус: если есть активный отпуск - 1, иначе - 0
    UPDATE employee
    SET employee_status = CASE
        WHEN check_active_vacation(v_employee_id) THEN 1
        ELSE 0
    END
    WHERE id = v_employee_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Затем привязываем триггер к таблице vocation:

CREATE TRIGGER trg_vocation_employee_status
AFTER INSERT OR DELETE ON vocation
FOR EACH ROW EXECUTE FUNCTION update_employee_status();

Особенности реализации на Java (JPA)

Если вы предпочитаете обрабатывать логику на уровне приложения, можно использовать EntityListeners или @PrePersist/@PreRemove в сущности Vocation. Однако такой подход менее надёжен, так как не сработает при прямых изменениях в БД. Рекомендуется комбинировать триггер с кэшированием в приложении.

Пример слушателя на Java:

@Entity
@EntityListeners(VocationListener.class)
public class Vocation { ... }

В слушателе после сохранения или удаления записи вызываете сервис, который пересчитывает статус сотрудника.

Заключение

Автоматическое обновление статуса сотрудника по датам отпуска - типичная задача, которую можно решить как на стороне базы данных (триггер + функция), так и на стороне приложения (JPA-слушатели). Триггерный подход обеспечивает целостность данных даже при обходе приложения. Главное - правильно определить момент срабатывания (после вставки/удаления) и учесть возможность нескольких отпусков у одного сотрудника.