Типы триггеров
Момент_срабатывания определяет, когда будет срабатывать триггер: до (BEFORE) или после (AFTER) наступления триггерного события (выполнения запускающего оператора). Если указано значение BEFORE, триггер выполняется до каких-либо проверок ограничений на строки, затрагиваемые триггерным событием. Никакие строки не блокируются. Триггер этого типа называется BEFORE-триггером (BEFORE trigger).
Если выбрать ключевое слово AFTER, то триггер будет срабатывать после того, как запускающий оператор завершит свою работу.и будут выполнены проверки всех ограничений. В этом случае затрагиваемые строки блокируются на время выполнения триггера. Триггер этого типа называется AFTER-триггером (AFTER trigger).
Триггерное_событие может принимать значения INSERT, UPDATE или DELETE.
Триггерное_ограничение — это одно и более дополнительных условий, которые должны быть выполнены для срабатывания триггера.
Необязательный набор ключевых слов FOR EACH ROW указывает на необходимость выполнить тело триггера для каждой строки, затрагиваемой запускающим оператором. Такие триггеры называются строчными (row triggers). Если опция FOR EACH ROW отсутствует, то при наступлении триггерного события триггер выполняется только один раз. В этом случае он называется операторным триггером (statement trigger), поскольку выполняется только один раз для каждого запускающего оператора.
Часть кода между DECLARE и END имя_триггера представляет собой обычный базовый блок PL/SQL.
Различные триггерные события можно комбинировать с помощью оператора OR. Например:
DELETE OR INSERT остальные_операторы
В случае использования UPDATE можно указать список столбцов:
UPDATE OF столбец_1, столбец_2,...
При использовании UPDATE в операторах PL/SQL обращение к новой и старой строкам выполняется с помощью слов "new" и "old", предваренных двоеточием. Так, :old.имя_столбца даст значение, которое столбец имел до обновления. Однако в триггерном ограничении имена "old" и "new" используются без двоеточий.
Пример триггера
В качестве примера рассматривается создание и использование триггера, отслеживающего действия торговых агентов. Соответствующий сценарий приведен ниже:
-- Добавить столбец к таблице покупок. Исходное значение — NULL.
ALTER TABLE purchase
ADD ORDER_NUMBER NUMBER(10);
-- Создать таблицу аудита.
CREATE TABLE audit
(ORDER_NUMBER NUMBER(10),
person_code VARCHAR2(3),
user_name CHAR(30),
user_machine CHAR(20),
change_in_quant NUMBER(5),
transaction_time DATE,
FOREIGN KEY (person_code) REFERENCES person);
-- Последовательность для нумерации заказов
CREATE SEQUENCE order_num_seq;
CREATE OR REPLACE TRIGGER audit_trigger
BEFORE INSERT OR UPDATE ON purchase
FOR EACH ROW
DECLARE
no_name_change EXCEPTION;
quant_change NUMBER(5) := 0;
BEGIN
/* He разрешать изменение product_name в заказах.
Возбудить исключение и вернуть значения к исходным.
*/
IF (UPDATING
AND
(:NEW.product_name <> :OLD.product_name))
THEN
RAISE no_name_change;
END IF;
/* Создать номер заказа для старых ненумерованных заказов,
а также для новых заказов, не имеющих номеров.
*/
IF (((UPDATING)
AND
(:OLD.ORDER_NUMBER IS NULL))
OR
((INSERTING)
AND
(:NEW.ORDER_NUMBER IS NULL)))
THEN
SELECT order_num_seq.NEXTVAL
INTO :NEW.ORDER_NUMBER
FROM dual;
END IF;
/* В заключение занести в таблицу аудита имя пользователя, имя его компьютера или терминала, изменение количества товара и время этого изменения. При вставке
изменение количества равно новому количеству.
*/
IF (UPDATING)
THEN
quant_change := :NEW. quantity - :OLD. quantity;
ELSE
quant_change := :NEW. quantity;
END IF;
INSERT INTO audit
VALUES ( :NEW.ORDER_NUMBER,
:NEW.salesperson,
USER,
USERENV ( 'TERMINAL' ) ,
quant_change,
SYSDATE) ;
EXCEPTION
WHEN no_name_change
THEN
dbms_output.put_line ('Change of product name not allowed');
dbms_output.put_line ('Aborting and resetting to old values');
:NEW.product_name := :OLD.product_name;
:NEW. salesperson := :OLD.salesperson;
:NEW.ORDER_NUMBER := :OLD.ORDER_NUMBER;
:NEW. quantity := :OLD.quantity;
END audit_trigger ;
/
SELECT * FROM purchase;
SELECT * FROM audit;
INSERT INTO purchase
VALUES ('Round Snaphoo', 'LN', '15-NOV-07' , 2, NULL) ;
SELECT * FROM purchase WHERE salesperson = 'LN';
SELECT * FROM audit;
UPDATE purchase SET salesperson = 'LB'
WHERE salesperson = 'CA' AND quantity = 1;
SELECT * FROM purchase WHERE salesperson ='CA';
SELECT * FROM audit;
UPDATE purchase SET quantity = 20
WHERE salesperson = 'BB';
SELECT * FROM purchase WHERE salesperson = 'BB';
SELECT * FROM audit;
UPDATE purchase SET product_name = 'Round Snaphoo'
WHERE salesperson = 'BB';
SELECT * FROM purchase WHERE salesperson = 'BB';
SELECT * FROM audit;
В сценарии сначала к таблице покупок добавляется столбец ORDER_NUMBER. Затем создается таблица для аудита записей, содержащая имя вошедшего в систему пользователя. Имя пользователя вводится с помощью функции USER, а имя его компьютера или терминала — с помощью функции USERENV, вызываемой с параметром TERMINAL. Также вводятся личный код продавца, изменение в количестве заказанного товара, номер заказа и дата изменения. Триггер не позволяет изменять product_name и автоматически генерирует номера для новых заказов, а также старых записей в таблице заказов, которые не были пронумерованы. При успешном обновлении или вставке триггер вставляет значения в таблицу аудита. Ключевые слова UPDATING и INSERTING служат для определения того, что послужило запускающим действием — обновление или вставка.
Сначала триггер тестируется путем вставки строки в таблицу PURCHASE и сравнения старых значений с новыми. Затем выполняются два простых обновления, а в заключение — попытка изменить название товара в заказе.