7.2.2 Применение триггеров
Триггеры являются мощным средством для обеспечения целостности данных и расширения функциональности. Наиболее часто триггеры применяются для:
- реализации сложных бизнес-правил и ограничений;
- автоматической генерации вычисляемых значений в столбцах;
- предупреждения ошибок в транзакциях;
- аудита и сбора статистики доступа к таблицам;
- поддержки синхронной репликации таблиц.
Ниже мы приводим несколько примеров триггеров для "Корпорации Кинга". Приводимые примеры были реализованы в средах интерактивного SQL - Oracle SQL*Plus и DB2 Command Center (см. Приложения 4 и 5). Поскольку символ ';' в этих средах по умолчанию используется как признак конца оператора, для того, чтобы употреблять этот символ внутри оператора CREATE TRIGGER, используются специальные средства. В SQL*Plus признаком конца оператора CREATE TRIGGER является символ '/'. В Command Center мы установили в настройках признак конца оператора - символ '@'.
7.2.2.1 Коррекция общей суммы в строке заказа
Очевидное бизнес-правило: в каждой строке заказа (в таблице item) сумма в строке должна быть равна произведению количества единиц товара на цену единицы.
В предлагаемом решении при добавлении или изменении строки в таблице item значение, задаваемое в столбце total, просто игнорируется. Значение, записываемое в этот столбец, вычисляется как произведение количества на цену.
Ниже приведен триггер для Oracle. В триггере, который запускается оператором INSERT, мы можем обращаться по коррелированному имени только к новому значению в столбце - по коррелированному имени :new.total. Поскольку триггер выполняется перед запускающим его оператором, в таблицу будет записано значение, вычисленное в триггере. В Oracle один и тот же триггер будет выполняться и для оператора INSERT, и для оператора UPDATE.
CREATE TRIGGER T7221
BEFORE INSERT OR UPDATE ON item
REFERENCING NEW AS new
FOR EACH ROW
BEGIN
:new.total:=:new.actual_price*:new.quantity;
END;
/
В DB2 нам приходится делать два триггера: отдельно для INSERT и отдельно для UPDATE. Второе отличие триггера DB2 состоит в том, что коррелированное имя употребляется без двоеточия перед ним.
CREATE TRIGGER T7221i
NO CASCADE BEFORE INSERT ON item
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
SET new.total=new.actual_price*new.quantity
@
CREATE TRIGGER T7221u
NO CASCADE BEFORE UPDATE ON item
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
SET new.total=new.actual_price*new.quantity
@
7.2.2.2 Коррекция общей суммы заказа
Очевидное бизнес-правило: при добавлении новой строки заказа (строки в таблице item) или изменении суммы в строке заказа должна корректироваться общая сумма этого заказа (в таблице sales_order).
Если в таблицу item вставляется новая строка, то к значению total в таблице sales_order добавляется значение total из вставляемой строки. Если изменяется значение total в таблице item, то к значению total в таблице sales_order добавляется разность между старым и новым значениями. Модифицируется та строка таблицы sales_order, в которой значение order_id совпадает со значением order_id во вставляемой или изменяемой строке таблицы item.
В Oracle один триггер создается и для вставки, и для изменения. Триггер распознает операцию, которая его запустила, применяя предикаты inserting и updating, которые PL/SQL обеспечивает для триггеров.
CREATE TRIGGER T7222
AFTER INSERT OR UPDATE OF total ON item
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
IF inserting THEN
UPDATE sales_order
SET total=total+:new.total
WHERE order_id=:new.order_id;
END IF;
IF updating THEN
UPDATE sales_order
SET total=total+:new.total-:old.total
WHERE order_id=:new.order_id;
END IF;
END;
/
Поскольку в DB2 нам приходится создавать для решения этой задачи два триггера, необходимость в какой-либо процедурной логике в триггере отсутствует.
CREATE TRIGGER T7222i
AFTER INSERT ON item
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
UPDATE sales_order
SET total=total+new.total
WHERE order_id=new.order_id
@
CREATE TRIGGER T7222u
AFTER UPDATE OF total ON item
REFERENCING OLD AS old NEW AS new
FOR EACH ROW MODE DB2SQL
UPDATE sales_order
SET total=total+new.total-old.total
WHERE order_id=new.order_id
@
7.2.2.3 Коррекция зарплат сотрудников по зарплате президента
Предположим, что в "Корпорации Кинга" существует правило: если уменьшается зарплата президента, зарплаты всех сотрудников корпорации уменьшаются в той же пропорции.
Триггер должен активизироваться только в том случае, если изменяется зарплата президента (код должности президента - 672), причем, изменяется в сторону уменьшения. Эти условия могут быть вынесены в фразу WHEN оператора создания триггера. Если условие активизации соблюдено, то должна перевычисляться зарплата всех сотрудников, у которых код должности отличен от 672.
Исходя из этих соображений, триггер для Oracle должен быть таким:
CREATE TRIGGER T7221
BEFORE UPDATE OF salary ON employee
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.job_id=672 AND new.salary<old.salary)
BEGIN
UPDATE employee
SET salary=salary/:old.salary*:new.salary
WHERE job_id<>672;
END;
/
У нас не возникнет проблем с созданием этого триггера. Но при попытке изменить зарплату президента мы получим сообщение об ошибке, например:
UPDATE employee SET salary=4000 WHERE employee_id=7839
*
ERROR at line 1:
ORA-04091: table DEMO.EMPLOYEE is mutating, trigger/function may not see it
ORA-06512: at "DEMO.T7223", line 5
ORA-04088: error during execution of trigger 'DEMO.T7223'
Триггеры Oracle не могут работать с таблицей, которую изменяет оператор, запускающий триггер. Как решить эту задачу в Oracle, мы увидим в следующем разделе.
В DB2 эта задача разрешима при помощи триггера. Обратите внимание: триггер DB2 выполняется после запускающего его оператора, так как выполнять в триггере операции вставки, удаления и замены до запускающего оператора нельзя.
CREATE TRIGGER T7221
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS old NEW AS new
FOR EACH ROW MODE DB2SQL
WHEN (new.job_id=672 AND new.salary<old.salary)
UPDATE employee
SET salary=salary*FLOAT(new.salary)/old.salary
WHERE job_id<>672
@
7.2.2.4 Ограничение на зарплату
Предположим, что в "Корпорации Кинга" существует правило: зарплата сотрудника не должна превышать зарплаты президента.
Триггер должен активизироваться при вставке новой строки в таблицу employee или при изменении значения в столбце salary. Код должности во вставляемой или изменяемой строке не должен быть 672. При выполнении триггера должно быть выбрано значение зарплаты президента (из строки с кодом должности 672) и сопоставлено со значением зарплаты во вставляемой или изменяемой строке. Если зарплата президента меньше зарплаты во вставляемой или изменяемой строке, триггер должен генерировать исключительную ситуацию.
В Oracle условие активизации включает в себя только проверку условия "не президент". В триггере вводится переменная psalary, в которую оператором SELECT INTO выбирается зарплата президента. При невыполнении ограничения триггер генерирует исключение при помощи процедуры PL/SQL raise_application_error.
CREATE TRIGGER T7224
BEFORE INSERT OR UPDATE OF salary ON employee
REFERENCING NEW AS new
FOR EACH ROW
WHEN (new.job_id<>672)
DECLARE psalary NUMBER;
BEGIN
SELECT salary INTO psalary
FROM employee
WHERE job_id=672;
IF :new.salary>psalary THEN
raise_application_error( -20777, 'Salary limit');
END IF;
END;
/
Увы, при срабатывании этого триггера мы, как и в предыдущем случае получим сообщение ORA-04088: Oracle не разрешает даже читать данные из таблицы, изменяемой запускающим триггер оператором.
В триггере DB2 мы можем включить запрос, выбирающий зарплату президента и сравнение его с зарплатой во вставляемой или изменяемой строке в условие активизации WHEN. Тело триггера состоит, таким образом, из единственного оператора SIGNAL, генерирующего исключение. Нам понадобится два триггера, но мы приводим только триггер для INSERT.
CREATE TRIGGER T7224i
NO CASCADE BEFORE INSERT salary ON employee
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
WHEN (new.job_id<>672 AND
new.salary > (
SELECT salary INTO psalary
FROM employee
WHERE job_id=672)
)
SIGNAL SQLSTATE '75000' ('Salary limit');
@
Вот пример срабатывания этого триггера:
INSERT INTO employee (employee_id,job_id,salary)
VALUES(999,670,5100)@
SQL0438N Applicanion raised error whth diagnostic text: "Salary limit".
SQLSTATE=75000
7.2.2.5 Аудит доступа к таблице
Предположим, что нам нужно хранить информацию о том, кто и когда производит изменения в таблице employee.
Для отслеживания изменений создадим таблицу journal, в которой по каждому, сделанному в таблице employee изменению, сохраняется:
- тип выполненной операции;
- дата и время;
- имя пользователя, выполнившего изменение;
- значение
employee_id измененной строки.
Триггер Oracle создается один для всех операций изменения таблицы. Триггер распознает тип запустившей его операции и выполняет вставку новой строки в таблицу journal. Время выполнения и имя пользователя триггер выбирает из специальных функций Oracle, а значение employee_id - по коррелированным именам.
CREATE TABLE journal (
mod_oper CHAR(6),
mod_time DATE,
mod_user VARCHAR2(30),
mod_id NUMBER(6)
);
CREATE TRIGGER T7225
BEFORE INSERT OR DELETE OR UPDATE ON employee
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO journal VALUES ('INSERT',SYSDATE,USER,:new.employee_id);
END IF;
IF deleting THEN
INSERT INTO journal VALUES ('DELETE',SYSDATE,USER,:old.employee_id);
END IF;
IF updating THEN
INSERT INTO journal VALUES ('UPDATE',SYSDATE,USER,:old.employee_id);
END IF;
END;
/
Решение для DB2 отличается, во-первых, тем, что дата и время составляют два отдельных столбца в таблице journal, во-вторых, тем, что мы создаем отдельный триггер для каждой операции. Если триггер Oracle может быть BEFORE или AFTER - все равно, то триггер DB2 может быть только AFTER.
CREATE TABLE journal (
mod_oper CHAR(6),
mod_date DATE,
mod_time TIME,
mod_user VARCHAR(30),
mod_id INTEGER(6)
)@
CREATE TRIGGER T7225i
AFTER INSERT ON employee
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
INSERT INTO journal VALUES('INSERT',CURRENT DATE,CURRENT TIME,USER,new.employee_id)
@
CREATE TRIGGER T7225d
AFTER DELETE ON employee
REFERENCING OLD AS old
FOR EACH ROW
INSERT INTO journal VALUES('DELETE',CURRENT DATE, CURRENT TIME,USER,old.employee_id)
@
CREATE TRIGGER T7225u
AFTER UPDATE ON employee
REFERENCING OLD AS old
FOR EACH ROW
INSERT INTO journal VALUES('UPDATE',CURRENT DATE, CURRENT TIME,USER,old.employee_id)
@
