Триггеры INSTEAD OF замещающие триггеры Предназначены для выполнения операций

Триггеры INSTEAD OF(замещающие триггеры) Предназначены для выполнения операций вставки, обновления и удаления элементов представлений. Создание триггера INSTEAD OF CREATE [OR REPLACE TRIGGER] имя_триггера INSTEAD OF операция ON имя_представления [DECLARE] BEGIN … END; Пример Система учета доставки базируется на трех таблицах: ü delivery(учет доставленной продукции), ü area(список районов доставки), ü driver(учет объемов работы курьеров).

Объединяем всю информацию в одно представление: CREATE OR REPLACE VIEW delivery_info AS SELECT d. delivery_id, d. delivery_start, d. delivery. end, a. area_desc, dr. driver_name FROM delivey d, area a, driver dr WHERE a. area_id = d. area_id AND dr. driver_id = d. driver_id; Триггер INSTEAD OF INSERT должен: ü Гарантировать, что столбец delivery. end будет содержать NULL. ü Определить идентификатор курьера по имени. Если указанное имя в таблице отсутствует, триггер присвоит курьеру новый идентификатор и добавит строку в таблицу курьеров. ü Определить идентификатор района по названию. Если указанное название в таблице отсутствует, триггер присвоит району новый идентификатор и добавит строку в таблицу районов.

курсор для получения идентификатора курьера по его имени: CURSOR cur_get_driver_id(cp_driver_name VARCHAR 2) IS SELECT driver_id FROM driver WHERE driver_name = cp_driver_name; v_driver_id NUMBER; курсор для получения идентификатора района по его названию: CURSOR cur_get_area_id(cp_area_desc VARCHAR 2) IS SELECT area_id FROM area WHERE area_desc = cp_area_desc; v_area_id NUMBER; значение столбца delivery_end должно быть NULL IF : new. delivery_end IS NOT NULL THEN RAISE_APPLICATION_ERROR(-20000, ‘Delivery end date value must be NULL when delivery created’); END IF;

/* Получить идентификатор курьера по его имени. Если имя не найдено, создать новый идентификатор на последовательности. Вставить строку в таблицу. */ OPEN cur_get_driver_id(: new. driver_name); FETCH cur_get_driver_id INTO v_driver_id; IF cur_get_driver_id%NOTFOUND THEN SELECT driver_id_seq. NEXTVAL INTO v_driver_id FROM DUAL; INSERT INTO driver(driver_id, driver_name) VALUES(v_driver_id, : new. driver_name); END IF; CLOSE cur_get_driver_id; /* Получить идентификатор района по его названию. Если название не найдено, создать новый идентификатор на последовательности. */ OPEN cur_get_area_id(: new. area_desc); FETCH cur_get_area_id INTO v_area_id; IF cur_get_area_id%NOTFOUND THEN SELECT area_id_seq. NEXTVAL INTO v_area_id FROM DUAL; INSERT INTO area(area_id, area_desc) VALUES(v_area_id, : new. area_desc); END IF; CLOSE cur_get_area_id;

Триггер INSTEAD OF UPDATE обновляет столбец delivery_end, если он содержит NULL CREATE OR REPLACE TRIGGER delvery_info_update INSTEAD OF UPDATE ON delivery_info DECLARE --курсор для получения строки доставки CURSOR cur_get_delivery(cp_delivery_id NUMBER) IS SELECT delivery_end FROM delivery WHERE delivery_id = cp_delivery_id FOR UPDATE OF delivery_end; v_delivery_end DATE; BEGIN OPEN cur_get_delivery(: NEW. delivery_id) FETCH cur_get_delivery INTO v_delivery_end; IF v_delivery_end IS NOT NULL THEN RAISE_APPLICATION_ERROR(-20000, ’The delivery end date has already been set’); ELSE UPDATE delivery SET delivery_end = : NEW. delivery_end WHERE CURRENT OF cur_get_delivery; END IF; CLOSE cur_get_delivery; END;

Триггер INSTEAD OF DELETE Следить, чтобы заполненные строки не удалялись. А затем самому удалить указанную строку доставки. CREATE OR REPLACE TRIGGER delivery_info_delete INSTEAD OF DELETE ON delivery_info BEGIN IF : OLD. delivery_end IS NOT NULL THEN RAISE_APPLICATION_ERROR(-20000, ’Completed deliveries cannot be deleted’); END IF; DELETE delivery WHERE delivery_id = : OLD. delivery_id; END;