Тело триггера

Операции, которые должны быть выполнены при срабатывании триггера, специфицируются в синтаксической конструкции triggered_SQL_statement(будем называть ееинициируемым SQL-оператором).Как видно из синтаксических правил, возможны два вида построения этой конструкции: в виде одиночного оператора SQL и в виде списка операторов со скобкамиBEGIN ATOMICиEND.

Недоумение читателей может вызвать неуточненная конструкцияSQL_procedure_statement. Постараемся объяснить ее происхождение и смысл. Дело в том, что в стандарте SQL:1999 определено процедурное расширение SQL, называемое SQL/PSM (отPersistent Stored Modules). Это достаточно большой язык, который мы не будем подробно рассматривать в этом курсе лекций¹⁶³⁾. Тем не менее для понимания синтаксиса определения триггеров необходимо отметить, что: (a) SQL/PSM включает основные операторы SQL, связанные с обновлением данных; (b) язык является вычислительно полным, т.е. включает развитые средства вычислений; (c) в языке содержатся средства определения и вызова функций ипроцедур,¹⁶⁴⁾и (d) SQL/PSM содержит стандартный комплект управляющих конструкций – циклы, ветвления разных типов и т. д. Тем самым,SQL_procedure_statement– это любая процедура, определенная на языке SQL/PSM.¹⁶⁵⁾В частности, эта процедура может представлять собой оператор SQL обновления базы данных.

Обсудим теперь, откуда возникает потребность в составном инициируемом SQL-операторе. Дело в том, что на практике при определении триггеров в качестве SQL_procedure_statementчаще всего используются операторы SQL обновления базы данных. Иногда (и мы покажем это на примере) для корректного определения функциональности триггера одного оператора не хватает, а в SQL отсутствует возможность определения составных операторов. Поэтому допускается использование средств определения составных операторов, присутствующих в SQL/PSM (BEGIN ATOMICиEND).

Для иллюстрации случая, когда при определении триггера достаточно специфицировать один оператор SQL, приведем пример определения триггера, условием срабатывания которого является выполнение операции вставки новой строки в таблицуEMP(прием на работу нового служащего). Если значение столбцаDEPT_NOв очередной вставляемой строке отлично отNULL, то триггер должным образом модифицирует значения столбцовDEPT_EMP_NOиDEPT_TOTAL_SALстроки таблицыDEPTсо значением столбцаDEPT_NO, которое соответствует номеру отдела нового служащего (пример 21.10):

163 Для читателей, которые имеют хотя бы минимальный опыт работы с продуктами компании Oracle, заметим, что во многих своих чертах SQL/PSM напоминает PL/SQL. Одной из причин, на основании которых мы отказались от описания SQL/PSM в этой книге, является то, что до сих пор (первый вариант стандарта SQL/PSM был опубликован в 1996 г.) нет ни одной реализации SQL, в которой этот стандарт был бы реализован полностью (точнее, ни одна такая реализация не известна автору).

164 Во многом на этих возможностях основываются механизмы SQL:1999, предназначенные для определения на уровне пользователя новых типов данных и их операций. Эта тематика также выходит за пределы данного курса (хотя мы немного затронем соответствующие вопросы в последней лекции этого курса).

165 На самом деле, для написания процедур, функций и методов допускается использование не только языка SQL/PSM, но и традиционных языков программирования, для которых в стандарте определены правила связывания с SQL. В последней лекции курса мы немного затронем и эту тему.

CREATE TRIGGER DEPT_CORRECTION AFTER INSERT ON EMP

FOR EACH ROW

WHEN (EMP.DEPT_NO IS NOT NULL)

UPDATE DEPT SET

DEPT_EMP_NO = DEPT_EMP_NO + 1,

DEPT_TOTAL_SAL = DEPT_TOTAL_SAL + EMP_SAL

WHERE DEPT.DEPT_NO = EMP.DEPT_NO;

Теперь предположим, что при увольнении служащего (удалении строки из таблицыEMP) мы хотим не только должным образом модифицировать таблицуDEPT, но и сохранять (с целью аудита) данные об уволенном служащем в таблицеEMP_DISMISSED¹⁶⁶⁾:

EMP_DISMISSED

EMP_NO : EMP_NO

EMP_NAME : VARCHAR

DEPT_NO : DEPT_NO

Определение соответствующего триггера могло бы выглядеть следующим образом (пример 21.11):

CREATE TRIGGER EMP_DISMISSION AFTER DELETE ON EMP

FOR EACH ROW

BEGIN ATOMIC

INSERT INTO EMP_DISMISSED

ROW (EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO);

UPDATE DEPT SET

DEPT_EMP_NO = DEPT_EMP_NO – 1,

DEPT_TOTAL_SAL = DEPT_TOTAL_SAL – EMP_SAL

WHERE DEPT.DEPT_NO = EMP.DEPT_NO

END;