Задача. Триггер по поиску в справочнике.

Дана таблица REGION(ID, NAME) с кодами и названиями регионов. Создать таблицу CLIENTS(ID, NAME, REGION_ID, REGION_NAME). Пользователь через форму ввода вставляет в таблицу CLIENTS имя клиента и строку с регионом. Требуется написать триггер на таблицу CLIENTS. Триггер автоматом вставляет ID (из сиквенса) и REGION_ID (из справочника REGION). Если записи нет в справочнике, то триггер вставляет запись в справочник.

Создадим таблицы и соответствующие им сиквенсы по генерации первичных ключей, заполним справочник данными:

CREATE TABLE REGION (ID INTEGER, NAME VARCHAR2(30));

CREATE TABLE CLIENTS (ID INTEGER, NAME VARCHAR2(30), REGION_ID INTEGER, REGION_NAME VARCHAR2(30));

CREATE SEQUENCE SQ_REGION;

CREATE SEQUENCE SQ_CLIENTS;

insert into region(id,name) values (sq_region.nextval,'Москва');

insert into region(id,name) values (sq_region.nextval,'Петербург');

create or replace trigger trig_biu_clients

before insert or update on clients

for each row

declare

v_region_id integer;

begin

/* сгенерируем новый clients.id */

if inserting then

select sq_clients.nextval into :new.id from dual;

end if;

/* найдем, есть ли в справочнике region запись с вставляемым названием региона. используем max(), чтобы избежать exception, если данные не найдены */

select max(region.id)

into v_region_id

from region

where region.name = :new.region_name;

/* если записи нет, то сгенерим новый первичный ключ для справочника region и вставим в справочник запись с этим ключом */

if v_region_id is null then

select sq_region.nextval into v_region_id from dual;

insert into region (id, name) values (v_region_id, :new.region_name);

end if;

/* в поле clients.region_id триггер записывает найденный или вновь сгенерированный v_region_id из справочника*/

:new.region_id := v_region_id;

end trig_biu_clients;

Проверяем, как работает триггер:

insert into clients (name, region_name) values ('Вася', 'Москва');

select * from clients;

insert into clients (name, region_name) values ('Петя', 'Рязань');

select * from clients;

select * from region;

update clients set region_name = 'Петербург' where name = 'Вася';

select * from clients;

update clients set region_name = 'Новгород' where name = 'Петя';

select * from clients;

select * from region;