Oracle. Row-level trigger. Пример

Рассмотрим следующую ситуацию в данном примере. 

Допустим, в нашей программе есть определенные таски, которые выполняются и имеют набор статусов. Например, STARTING, STARTED, WAITING, RUNNING, FINISHED. 

Есть таблица RUN_JOBS, в которой одна запись соответствует одному таску. Когда же таск меняет статус, эта запись обновляет свой статус, а также проставляет таймстемпы в момент перехода в определенный статус и в момент его окончания. 

Мы же хотим логгировать все переходы из одного статуса в другой и время, которое понадобилось на смену статусов в другую таблицу. 

И так, таблица RUN_JOBS:

create table RUN_JOBS ( ID NUMBER, STATUS VARCHAR2(32), START_DATE TIMESTAMP, LAST_UPDATE_DATE TIMESTAMP, )

Для логгирования создадим таблицу:

CREATE TABLE RUN_JOBS_LOG ( ID NUMBER(6) NOT NULL PRIMARY KEY, JOB_ID NUMBER NOT NULL, FROM_STATUS VARCHAR2(32), TO_STATUS VARCHAR2(32), TIME_ELAPSED INTEGER )

FROM_STATUS будет содержать значение статуса, из которого таск вышел, TO_STATUS - новый статус. TIME_ELAPSED - сюда будем записывать время,  которое было затрачено таском в определенном статусе. 

Сделаем foreign key в таблице RUN_JOBS_LOG для связи с таблице тасков  - RUN_JOBS. 

ALTER TABLE RUN_JOBS_LOG ADD (CONSTRAINT RUN_JOBS_LOG_FK1 FOREIGN KEY (JOB_ID) REFERENCES RUN_JOBS (ID) ON DELETE CASCADE);

Для вычисления времени в секундах между двумя timestamp будем использовать функцию из этого поста - Oracle PL/SQL. Как вычислить разницу в секундах между двумя timestamp?

А для записи изменения всех статусов будем использовать row-level триггер на таблице RUN_JOBS. Он будет отслеживать изменения значений в колонке STATUS, вычислять затраченное время и записывать в таблицу RUN_JOBS_LOG.

create or replace TRIGGER JOBSLOGGER BEFORE UPDATE OF STATUS ON RUN_JOBS FOR EACH ROW BEGIN insert into RUN_JOBS_LOG(id, job_id, from_status, to_status, time_elapsed) values(RUN_JOBS_LOG_SEQ.nextval, :new.id, :old.status, :new.status, CALC_TIMESTAMP_DIFF_IN_SECONDS(:new.last_update_date, :old.last_update_date) ); NULL; END;

В триггере используется sequence для получения инкремента для id. 

Создаем его:

CREATE SEQUENCE RUN_JOBS_LOG_SEQ INCREMENT BY 1 START WITH 100;

И так, триггер возбуждается при изменении значений в колонке STATUS. 

:new дает нам возможность доступаться к новым измененным значениям. 

:old  позволяет получить старые значения. 

То есть,

если статус изменился со STARTING в STARTED,

то :old.status будет содержать значение STARTING, а 

:new.status - STARTED.

Спасибо за внимание.