Как создать триггер для изменения поля одной таблицы по условию из другой?

Question

[alex_lemurski]

Никак не могу разобраться в механике написания функции и триггера к нему в базе данных
детали такие:
сущность, в которой должно происходить автомт смета значения поля, в зависимости от условия в другой сущности

ПОЛУЧАТЕЛЬ УСЛОВИЯ
@Entity
class Employee {
.....
@Column(name = "employee_status")
private EmployeeStatusENUM employeeStatusENUM;
(0="действующий", 1="в отпуске", 2=закрыт" --- 0 и 2 задаются через поле в html,
а 1 только автоматом по условию из сущности Vocation
.....
@OneToMany(mappedBy = "employee")
private List vocationList = new ArrayList<>();
(график отпусков, коллекция т.к. годовой отпуск делить можно на несколько частей)

getters, setters...
}

ПОСТАВЩИК УСЛОВИЯ
@Entity
class Vocation {
.....
@Column(name = "date_start")
private LocalDate startOfVocation;
(дата начала отпуска, задается через поле в html)

@Column(name = "days")
private Integer daysOfVocation;
(кол-во дней отпуска, задается через поле в html)

@Column(name = "date_end")
private LocalDate endOfVocation;
(дата окончания отпуска, задается через setter в контроллере, перед сохранением (созданием) новой сущности,
*vocation.setEndOfVocation(vocation.getStartOfVocation().plusDays(vocation.getDaysOfVocation()));*
хотя и это поле можно наверно через триггер заполнять, но не через html, таково указание.
данную сущность Vocation созданную для Employee обновлять нельзя, только создать и удалить)

@ManyToOne
@JoinColumn(name = "employee_id")
private Employee employee;

getters, setters...
}

условие задания employeeStatusENUM = 1 следующее:
если текущая дата находится в диапазоне дат отпуска (вкл день начала и конца)
статус с 0 на 1 и 1 на 0 должно происходить автоматически




примеры кусочков таблиц

Comments

[alexalexes]

А зачем в таблицу записывать статус, зависящий от текущей даты?
Достаточно создать хранимую функцию, которая получает некий статус от совокупности данных текущей таблицы или нескольких таблиц, и ей пользоваться в запросах.
Если не нужно светить название данной функции, то используйте вью с этой функцией (или, вообще, с примитивным case-ом) - в ней будет на выходе короткое название статуса, как будто он хранится в базе, и триггеров никаких не нужно будет.

[alex_lemurski]

alexalexes,
"А зачем в таблицу записывать статус, зависящий от текущей даты?"
так надо, и никак иначе, и появился он далеко не только потому что появилась связь employee - vocation, появился задолго до этого, и участвует он много еще где.
если я явно ошибся с выбором технологии как динамически и автоматически менять значение в колонке БД так и скажите, если не умеете создавать триггеры по таким условиям или вобще, не пишите пож что мне надо а что нет, совсем не профессиоанльно.

[alexalexes]

Вам через шедулер в postgres придется создавать задачу, которая раз в сутки будет через процедуру менять статус, если хотите такое свойство в живую иметь. Триггер тоже будет дергать аналогичную процедуру, но с частными параметрами.
Просто я пишу, потому что концепция проектирования базы не верная, из-за этого много придется обвязок делать, чтобы поддерживать поля в актуальном состоянии.
Текущие статусы должны вычисляться налету в запросах или функции, а не храниться в базе.

[alex_lemurski]

alexalexes,
"Триггер тоже будет дергать аналогичную процедуру, но с частными параметрами"
не вопрос, я готов на это, пуст дергает. есть возможность показать как будет выглядеть триггер+функция для конкретно моего случая?

"Текущие статусы должны вычисляться налету в запросах или функции, а не храниться в базе."
может это если делать селекты непосредственно из бд.
в моем случае на основе этих полей выводится информация на экран, для обычных людей, про бд они даже не слышали, так же доступность к объекту сущности в зависимости от его статуса

[alexalexes]

в моем случае на основе этих полей выводится информация на экран, для обычных людей, про бд они даже не слышали,

У вас API доступа к данным базы как то жестко завязано на отображение.
Так быть не должно.
Если у вас есть сложно вычисляемые свойства, то упаковывайте их в функции и предоставляйте доступ к этим функциям для клиентов базы данных. Если нужно инкапсулировать их в поля, то не нужно давать доступ к исходным таблицам, нужно использовать вью.

Answer 1

[alexalexes]

Создаете функцию для триггера:

CREATE OR REPLACE FUNCTION set_empoyee_status()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF NEW.date_start <= CURRENT_DATE and NEW.date_end >= CURRENT_DATE  THEN
	   update employee -- тут изменяется таблица работников!
           set employee.empoyee_status = 1
          where employee.emp_id = NEW.employee_id;
          ELSE
          update employee -- тут изменяется таблица работников!
           set employee.empoyee_status = 0
          where employee.emp_id = NEW.employee_id;
	END IF;
	RETURN NEW;
END;
$$

Потом нужно присоединить функцию к триггеру:

CREATE TRIGGER tr_set_employee_status
  BEFORE UPDATE
  ON vocation -- тут на таблицу отпусков вешается триггер!
  FOR EACH ROW
  EXECUTE PROCEDURE set_empoyee_status();

PS: Если функция будет вешаться на разные события (в особенности, на delete), то нужно выбирать NEW/OLD источник записи для определения статуса.