Триггер всемогущий

Именно так, и не иначе, могу назвать объект БД Oracle, который может автоматизировать любой процесс с данными. Когда в далёких 1990-х мне приходилось налаживать заводской документооборот, то частенько сотрудницы ОТиЗ, ФинО, да и всей прочей бухгалтерии высказывали свои пожелания к ПО словами: "Вот бы одну кнопочку нажать, а данные сами заполнились, рассчитались и распечатались!". Но, к сожалению, тогда в нашем распоряжении были только Clipper и FoxPro, поэтому клиентскую часть приходилось расписывать досконально и многократно дублировать, а потом ещё и перепроверять интеграцию модулей между отделами. Когда автоматизация распространилась и на цеха, то табельщицам всё равно приходилось информировать по телефону общезаводские отделы о завершении ввода первичных данных, чтобы верхи у себя запускали общий расчёт. Как же нам, программистам, не хватало тогда такого умного объекта базы данных, как триггер. Он мог в разы сократить время на обработку данных, в том числе и на проверку корректности ввода. Разработчики на Oracle, гордитесь тем, что у вас есть такой страж порядка, исполняющий свою миссию столь безукоснительно и незаметно. Точно также и тестировщик в группе разработки выполняет роль смотрителя за качеством.

Внушительность триггеров заключена в их возможности управлять системой. Кроме DML триггеров для таблиц и вьюверов, Oracle предоставляет администраторам БД триггеры типа system - на схему и всю базу.

Рис. 1 - Мастер Триггера для таблицы в SQLDEVELOPER

Рис. 2 - Мастер Триггера для вьювера в SQLDEVELOPER

Рис. 3 - Мастер Триггера для схемы в SQLDEVELOPER

Рис. 4 - Мастер Триггера для базы в SQLDEVELOPER

Рис. 5 - Мастер Триггера в SQLDetective

Больше всего в триггерах мне нравится их автоматичность срабатывания при наступлении выбранных предопределённых системой условий. А невозможность их запуска извне, поскольку это по сути обычная хранимая подпрограмма, максимально защищает данные от несанкционированного распространения. Только стандартное событие базы выполнит PL/SQL блок или хранимую подпрограмму (процедура, функция, в том числе и пакетные) на языке PL/SQL, С или Java. Получается, что система контролирует саму себя. Но при написании тела триггера стоит внимательно относится к исполняемым командам, которые не должны воздействовать на исходную транзакцию.
Поскольку триггер - это специальная исполняемая программа, то и параметры у него свои, специальные (correlation names) - :NEW, :OLD, :PARENT. Они автоматически связаны с данными, которые изменяются в процессе срабатывания триггера, поэтому удобны в использовании в рамках исполняемого PL/SQL блока, условий WHEN самого триггера или в качестве передаваемых параметров в вызываемую процедуру.
Пример из документации Oracle про самостоятельную процедуру и её вызов из тела триггера с учётом специфических параметров триггера:
CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)
IS LANGUAGE Java
name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';
CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab
FOR EACH ROW
CALL Before_delete (:OLD.Id, :OLD.Ename)
/
Программисты - люди увлекающиеся, и, в надежде автоматизировать всё и вся, могут написать слишком большой PL/SQL блок в теле одного триггера. Поэтому документация их сразу предупреждает - не превышать 32Kb или 60 строк, либо обходить лимит за счёт использования самостоятельных хранимых подпрограмм, чтобы не получать ошибку "PLS-00123: program too large (Diana nodes)" при компиляции триггера в базу или постоянные задержки на срабатывании этого триггера.
Ещё одним ограничением - по-умолчанию 32 штуки - является набор единовременно исполняемых триггеров, например, все они во включенном состоянии для одной таблицы. Но если сервер базы достаточно мощный, то ограничение меняется системным параметром OPEN_CURSORS. Триггеры могут заменить внешнее приложение, поскольку они сами себя упорядочивают, выполняясь автоматически по строгой схеме: предшествие выражения, предшествие строки, последствие строки, последствие выражения. При этом временная точка ВМЕСТО никогда не пересекается с ДО, ПОСЛЕ точками, так как они предназначены для разных типов объектов (ВМЕСТО - вьювер, ДО или ПОСЛЕ - таблицы, схемы или база). Исключением являются материализованные представления, у которых не случается запуска триггера при обновлении (refresh), но лог материализованного представления есть не что иное как AFTER ROW триггер этого вьювера, собирающий данные о командах  INSERT, UPDATE или DELETE вьювера в отдельную техническую таблицу.
Отличным помощником для тестировщика приложения на основе множества авто-подпрограмм могла бы быть визуализация этих двигателей. К сожалению, на страницах Triggers модуля DB Examiner в SQLDetective, весьма подешевевшем этой весной IDE, есть только два списка триггеров - на схемы и базу.

Рис. 6 - SQLDetective - DB Examiner - Triggers - DB

Рис. 7 - SQLDetective - DB Examiner - Triggers - Schemas

А ведь как было бы удобно иметь дерево зависимостей dml-триггеров и их calling/called объектов, или хотя бы фильтры системных триггеров по типу применения. Кстати, в SmartDataset (модуль в SQLDetective) можно настроить фильтры и сортировки, а затем самостоятельно или в рамках встроенного Repository использовать вьювер sys.all_triggers по различным условиям, например: OWNER LIKE '%MY_USER%' or TABLE_NAME IS NULL or TRIGGERING_EVENT LIKE '%DROP%'. Отследить же системные события в любом IDE на сегодняшний день возможно только вручную из системных представлений. 
По-моему, разработчики избегают такой объект, как триггер, потому что его воздействие сложно отследить. Ведь обладая системной привилегией CREATE ANY TRIGGER можно настроить обработку чужих данных через свои объекты, которые владелец никогда не найдёт. Триггер на объект одной схемы может находиться как в этой самой схеме, так и в другой. Аналогично триггер на одну схему может создаваться и храниться в соседней схеме. Для создания и редактирования триггеров для чужих схем администраторские права не нужны юзеру-исполнителю. Например, USER1 создаёт ограничение (dml trigger) на добавление записей в таблицу схемы USER2 и запрещает удалять объекты (schema trigger) из схемы USER3. Оба триггера хранятся в схеме USER1, но срабатывают только на события в определённых схемах и объектах, отличных от держателя объектов. Эти срабатывания сложно отыскать в деревьях объектов, которые показывают всевозможные приложения для разработчиков (IDE) и даже Call Tree диаграммы или CRUD матрицы могут оказаться бесполезными. Частично помогают диаграммы References&Dependencies, но только для dml-триггеров, поскольку триггеры на схемы и базу в такой диаграмме не учитываются. Здесь нужны специальные визуализаторы вызовов, заточенные на структуру именно триггеров, где раздельно упоминаются владельцы объектов хранения и воздействия. Пользуясь этим фактом можно вести мониторинг работы незаметно для рядовых пользователей, выполняющих логгируемые шаги или иные значимые действия.
Пример.
-- создаём таблицу в одной схеме у простого юзера USER1
CREATE TABLE USER1.US1_TB1 (US1TB1COL1 VARCHAR2(10), US1TB1COL2 NUMBER(5,0), US1TB1COL3 DATE)
/
-- подключаемся к базе каким-нибудь системным юзером SYS с привилегией CREATE ANY TRIGGER  и создаём триггер USER2.NO_MY_TR на таблицу первого юзера USER1.US1_TB1, но в другой схеме  такого же простого юзера USER2
connect as db_admin
/
CREATE OR REPLACE TRIGGER USER2.NO_MY_TR
  BEFORE  INSERT OR UPDATE  ON USER1.US1_TB1  FOR EACH ROW
BEGIN
:new.US1TB1COL1 := 'my value ' || to_char(:old.US1TB1COL2);
END;
/
-- теперь обычный юзер при вставке или изменении значений второй колонки не сможет понять почему в первой колонке сами появляются значения, несмотря на то, что таблица не имеет значений по-умолчанию и своих триггеров
connect as user1
/
INSERT INTO USER1.US1_TB1(US1TB1COL2)  VALUES(1)
/
INSERT INTO USER1.US1_TB1(US1TB1COL2)  VALUES(2)
/
INSERT INTO USER1.US1_TB1(US1TB1COL2)  VALUES(3)
/
UPDATE USER1.US1_TB1 SET US1TB1COL2 = :US1TB1COL2
/
Триггеры, созданные в любых схемах юзером с системными привилегиями CREATE ANY TRIGGER и  ADMINISTER DATABASE TRIGGER, срабатывают для обозначенных схем, но остаются невидимыми им. Однако, перед созданием триггеров в разных editions нужны права на сами editions (наследование, текущее и активное пространство), а для корректного срабатывания вызываемых процедур необходимо позаботиться о доступе юзера к самим исполняемым элементам (достаточно привилегии EXECUTE на спецификацию пакета). Отслеживая права, не забывайте о посредниках - системных ролях.
Универсальность триггера заключена ещё в частичной замене свойств (переключение активности, компиляция, переименование) через команду ALTER и глобальную замену через выражение OR REPLACE в команде создания, позволяющее кардинальные перемены без удаления объекта. Также, триггер универсален тем, что создаётся в базе даже с ошибками компиляции, не заставляя создателя отлаживать его на первом же этапе. Но сильно беспокоиться не стоит, поскольку срабатывать инвалидный объект не будет, не смотря на его включенность. И это на мой взгляд - хороший плюс.
Ещё полезная фича есть в базах с EDITION (Oracle после 10 версии), где триггеры по-умолчанию создаются в режиме EDITIONABLE и это позволяет создавать различное содержимое для одного и того же объекта. Версионность кода, то есть смена EDITIONs, переключается в одну команду (ALTER DATABASE DEFAULT EDITION = ORA$BASE) администратором базы, либо обычный юзер выбирает доступный текущий EDITION. К сожалению, не всякое IDE позволяет параллельную работу с кодом в разных EDITION: мастер триггера не имеет опции EDITIONABLE | NONEDITIONABLE, диалог коннекта к базе не позволяет перевыбрать current EDITION, например SQLDetective и ClearSQL. Порядок исполнения триггеров одного и того же объекта в одном и том же edition контролируется параметром FOLLOWS | PRECEDES. Полезным параметром для дерева объектов в IDE могла бы быть сортировка триггеров по параметру следования. Да, версионность кода усложняет работу с триггерами, но версионное наследование CROSSEDITION можно определять параметром FORWARD | REVERSE. В БД Oracle продумана даже такая запутанность, но "увидеть" эту логику можно только в системных вьюверах, IDE не поспевают за внутренними удобствами, хоть и пытаются рапортовать о "полной поддержке новой версии базы". Так что, пока единственный выход - это собственноручно настроенные запросы из вьюверов SYS.ALL_TRIGGERS, SYS.ALL_TRIGGER_COLS SYS.ALL_TRIGGER_ORDERING и им подобных.
БД Oracle, введя dml-команду MERGE, не забыла про триггеры и разрешила создавать составные триггеры, назвав их COMPOUND, позволяющие обработать до четырёх событий ДО-ПОСЛЕ одновременно и избежать переизбытка однотипных объектов, их насильного упорядочивания. Такие триггеры результативно предотвращают мутацию данных и способствуют эффективной передаче данных в другие таблицы. И те, и другие одновременно разрешают обработку DELETE, INSERT и UPDATE команд, что значительно упрощает разработку. А конкретизация и лимитирование списка полей для срабатывания UPDATE триггера уберегает базу от излишней загруженности. CRUD матрицы объединяют обращения к объектам в единый список и своеобразно визуализируют код, но только тот, что заключён в теле триггера. Поэтому структуры и зависимости самих триггеров и их связных объектов нуждаются в самостоятельной диаграмме, нечто среднее между Ref&Dep и Call Tree диаграммами на уровне всей базы (см. ClearDB Sample Docu).
Благодаря DML триггеру можно автоматически заполнять и отслеживать внешние ключи, последовательность уникальных значений, каскадность зависимых величин и связанных объектов, соблюдать сложные и комплексные ограничения, генерить новые значения и логгировать изменения данных. Поскольку триггер исполняется на серверной стороне, то большую половину приложения, которое обычно является полностью клиентской частью, можно вынести в тело триггеров. Например, вся наша Bug Tracking System состояла из десятка таблиц и по полдюжины триггеров к ним. Никакого дополнительного интерфейса уже не требовалось, автоматизация закрытия и передачи задач была максимальная.
Триггером схемы или базы можно отслеживать создание, удаление и изменение объектов: кто, что и когда сделал или с какой ошибкой базы столкнулся, когда и кто подключился к базе или база стартанула без предшествующей остановки. Это вам не голый список запросов из Top SQL (SQLDetective - DB Examiner), а полноценная отчётность о важных событиях БД. Контроль и мониторинг базы или отдельной схемы возможен при исполнении команд: AFTER CLONE (только для PLUGGABLE DATABASE, срабатывает и самостоятельно удаляется после копирования/клонирования базы), AFTER DB_ROLE_CHANGE (актуально только для триггера базы), AFTER LOGON, AFTER SERVERERROR (кроме ошибок ORA-01403: no data found, ORA-01422: exact fetch returns more than requested number of rows, ORA-01423: error encountered while checking for extra rows in exact fetch, ORA-01034: ORACLE not available, ORA-04030: out of process memory when trying to allocate string bytes), [AFTER | BEFORE] SET CONTAINER (при исполнении ALTER SESSION SET CONTAINER команды), AFTER STARTUP (актуально только для триггера базы), ALTER (кроме ALTER DATABASE), ANALYZE, ASSOCIATE STATISTICS, AUDIT, BEFORE LOGOFF, BEFORE SHUTDOWN (актуально только для триггера базы), BEFORE UNPLUG (только для PLUGGABLE DATABASE, срабатывает и самостоятельно удаляется после успешного исполнения), COMMENT, CREATE (кроме CREATE DATABASE или CREATE CONTROLFILE), DDL, DISASSOCIATE STATISTICS, DROP (кроме удаления самого триггера), GRANT, NOAUDIT, RENAME, REVOKE, SUSPEND, TRUNCATE. Исполненные события администратор базы может логгировать в особый список (таблица в этой же или соседней базе, сообщения по электронной почте или СМС), "опасные" события запрещать (не выполнить, показав пользовательское сообщение об ошибке), не заменяя их на иные. Некоторые IDE, например DB Monitor в SQLDetective, отслеживают некоторые события базы и оповещают о критичных изменениях, но для этого необходимо постоянно держать приложение и модуль в работающем состоянии, что само по себе нагружает сервер и клиентскую машину. А если написать системные триггеры и включать их только в периоды предположительных срабатываний, то такая настройка базы не только минимизирует нагрузку на сервер, но и конкретизирует столь легко варьируемое логгирование. Это вполне логично, так как для выполнения триггера база открывает автономную транзакцию, исполняет тело триггера и закрывает (сохраняет изменения) транзакцию, не пересекаясь с пользовательскими транзакциями. Поэтому триггер лучше джоба: вполне можно триггер всегда держать включенным (он не будет отнимать ресурсы без толку, только при определённом событии), а регулярно срабатывающий джоб только излишне нагрузит систему и мало-что важного успеет отследить.
IDE предназначены для упрощения работы программистам или даже в качестве обучающего инструмента элементам языка базы. Но мастер триггера в SQLDEVELOPER предлагает список событий вне зависимости от выбранного порядка исполнения, а редактировать скомпиллированный объект предлагает только через его DDL, без визуализации интерфейсом, как это доступно в SQLDetective через мастер объекта. Но и в SQLDetective мастер триггера становится опасным, если он в чужой схеме - подмена на текущего владельца проходит незаметно, особенно в схемных.

Pис. 8 - в схеме USER2 триггер запрещает удалять объекты из схемы USER1, но при малейшей корректировке через интерфейс триггер меняет владельца незаметно для редактирующего

Так что воздержитесь от правки объектов в интерфейсе, на сегодняшний день более надёжно править код в простом SQL редакторе, как это предлагает SQLDEVELOPER. К сожалению, полноценной поддержки триггеров нет ни в одном доступном IDE. К примеру, для отладки триггера Stored Program Editor в SQLDetective автоматически генерит DML выражение в анонимном PL/SQL блоке, но не учитывает ограничения списка полей UPDATE OF, а для схемных или триггеров на базу генерит лишь пустой Begin-End блок, хотя вполне мог показать примеры из Code Assistant. Подсказки подобного рода уничижают ученическую лицензию SQLDetective. Вероятно, это и есть причина того, что в разрабатываемых приложениях так мало полезных объектов. Хотя допускаю, что кому-то не нравятся некоторые ограничения: триггеры в схеме SYS посторонним создавать запрещено; имена триггеров не могут повторяться в одной схеме, но можно одноимённо называть таблицу и один из её триггеров; по-умолчанию триггер создаётся включенным, то есть может невовремя сработать; анонимный блок тела триггера не позволяет объявлять переменные типа LONG или LONG RAW; триггер не срабатывает для LOB колонки, изменяемой через OCI функции или DBMS_LOB пакет, но, надеюсь, Oracle со временем эту проблему разрешит. А пока, у продавцов IDE есть шанс предложить уникальную утилиту, где бы можно было отследить триггеры на схемы и базу. Например, распутать клубок: USER1 не может создавать и удалять собственные объекты при достаточном наличии системных привилегий:
-- подключаемся к базе системным юзером и создаём трёх пользователей с минимальным, но достаточным набором прав
connect db_admin
/
CREATE USER USER1
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER1
/
GRANT RESOURCE TO USER1
/
GRANT CREATE PROCEDURE TO USER1
/
GRANT UNLIMITED TABLESPACE TO USER1
/
CREATE USER USER2
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER2/
GRANT RESOURCE TO USER2
/
GRANT CREATE PROCEDURE TO USER2
/
GRANT UNLIMITED TABLESPACE TO USER2
/
CREATE USER USER3
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER3
/
GRANT RESOURCE TO USER3
/
GRANT CREATE PROCEDURE TO USER3
/
GRANT UNLIMITED TABLESPACE TO USER3
/
-- подключаемся к базе первым пользователем и проверяем возможность создания и удаления объектов
connect user1
/CREATE OR REPLACE PROCEDURE USER1.NEW_PROCEDURE  AS
BEGIN    NULL;    END;
/
drop procedure NEW_PROCEDURE 
/
-- возвращаемся к коннекту админом и создаём схемные триггеры во второй и третьей схемах
connect db_admin
/
CREATE OR REPLACE TRIGGER USER2.US2DRUS1
   BEFORE   DROP   ON USER1.SCHEMA
   BEGIN
     RAISE_APPLICATION_ERROR (num => -20000,  msg => 'User2: Do not drop an object!');
   END;
/
CREATE OR REPLACE TRIGGER USER3.US3CRUS1
   BEFORE   CREATE   ON USER1.SCHEMA
   BEGIN
      RAISE_APPLICATION_ERROR (num => -20000,  msg => 'User3 does not allow to create an object');
   END;
/-- возвращаемся к обычному юзеру и вновь выполняем создание и удаление объекта
connect user1
/
CREATE OR REPLACE PROCEDURE USER1.NEW_PROCEDURE  AS
BEGIN    NULL;    END;
/
drop procedure NEW_PROCEDURE 
/
Результат отрицательный:

Рис. 9 - Исполнение схемных триггеров чужого владельца

Рис. 10 - Исполнение схемных триггеров чужого владельца

Внимание: на коннект админа триггеры не распространяются - он создаёт и удаляет объекты в ограниченной схеме без проблем. Системных привилегий у юзера USER1 достаточно. Сообщения об ошибках никак не раскрывают тайны - где зарыто ограничение (тип ограничения - включенный триггер, место хранения - чужие схемы, наименований триггеров в сообщениях не может быть - текст в примере дан только для образца).
А ведь такая чехарда легко могла быть распутана диаграммой схемных триггеров:

Рис. 11 - Диаграмма связей схемных триггеров

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

Рис. 12 - Значение колонки по-умолчанию не может быть составным

Рис. 13 - Автоматическое заполнение колонок посредством триггеров

Да, часть операций могут произвести констрейнты и специализированные колонки (unique ID, default column value), но им не под силу отследить сложные или композитные условия (specific logon requirements, firewall, work hours, custom rules). В нашей самописной BTS именно триггер автоматически отправлял письма-нотификации высшему руководству об успешной публикации очередного билда: публикатор проставлял в одной из таблиц BTS признак завершения работ, когда чек-лист публикации весь был с датами  окончания и номер опубликованного билда с опцией "Выложен", и UPDATE OF триггер рассылал e-mail сообщения на предопределённые адреса с полной и точной информацией: наименование продукта, номер версии, дата-время публикации, имя ответственного.
-- триггер OUR_DB.VERSION_BU_TRG контролировал публикацию билда по чек-листу
CREATE OR REPLACE TRIGGER OUR_DB.VERSION_BU_TRG
  BEFORE
  UPDATE
  ON OUR_DB.VERSION
  FOR EACH ROW
  WHEN ( NEW.PUBLISHED = 'Y' )
DECLARE
  I INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO I
    FROM RELEASE_CHECK_LIST
    WHERE VER_ID = :NEW.ID AND
          CHECK_DATE IS NULL;
  IF ( I > 0 ) THEN
    RAISE_APPLICATION_ERROR('-20001', 'There are still '||TO_CHAR(I)||' not checked items for this version in the Release Check List. Publishing is forbidden!');
  END IF;
END;
/-- триггер OUR_DB.VER_PUBLISHED_TRG рассылал нотификации стейкхолдерам об окончании публикации билда
CREATE OR REPLACE TRIGGER OUR_DB.VER_PUBLISHED_TRG
  AFTER
  INSERT OR UPDATE OF PUBLISHED
  ON OUR_DB.VERSION
  FOR EACH ROW
DECLARE
 prod_name VARCHAR2(30);
 mail_body VARCHAR2(300);
BEGIN
  If :new.published = 'Y' then
     Select pr.Name into prod_name from our_db.products pr Where pr.id = :new.prd_id;
     mail_body := prod_name||' '||:new.version||'.'||TO_CHAR(:new.release)||'.'||TO_CHAR(:new.Build)||' '
               ||' has been published on '||TO_CHAR(:new.BUILD_DATE, 'yyyy.mm.dd hh24:mi:ss');
-- пример пакетной процедуры mailer.SEND_SMTP_MAIL можно найти на форумах Oracle
     our_db.mailer.SEND_SMTP_MAIL(
          'ProductPublisher@OurCompany.org'
        , 'Tester-Publisher'
        , 'ProductOwner@OurCompany.org'
        , 'Product Owner'
        , 'Published Build - '||prod_name
        , mail_body);
  End If;
END;
/Такая автоматизация значительно сокращала моё рабочее время и отлично исключала "человеческий фактор": усталость, опечатки, забывчивость. Иной пример - от администратора базы (вернее множества баз на множестве серверов в различных местах): случайные и непредвиденные падения базы или сервера отслеживаются маленьким триггером на каждую инсталляцию, сигнализирующим админу о включении (startup) той или иной базы без предварительного стандартного гашения (stop), поскольку ей необходим дополнительный аудит.
Триггер - это вам не прыщик-выскочка, он удалец!
Сам по строкам бродит, рыщет, проверяет, генерит.
Он в плаще, он невидимка, словно сыщик за углом.
У него заданий тыщщи, а успевает всё кругом.
Он пинка даст всей системе и напомнит каждой соне,
Что и где случилось - вскоре разведёт проблему в корне.
Вы его лишь снарядите целью и шагов порядком,
А в бою он вас заменит целиком и без оглядки.
Вот сколько плюсов у этого маленького объекта базы данных Oracle, которые полноценно могут заменить целое внешнее приложение. А вашим программистам нравится писать триггеры и использовать их минимализм и приближённость к данным вместо "лапши" многократных проверок в рамках клиентского приложения? Вы всё-ещё вручную проверяете ввод первичных данных или уже доверились автомату, который всё делает сам и за вас - программисты, и за вас - тестировщики, и за вас - операторы данных?