Задача. Триггер зеркалирования.

Создать таблицу KK_CLIENT(CLIENT_ID INTEGER, CLIENT_NAME VARCHAR2(40), AMOUNT NUMBER). У таблицы KK_CLIENT имеется первичный ключ CLIENT_ID.

Создать таблицу KK_CLIENT_MINUS - копию таблицы KK_CLIENT.

Написать триггер на KK_CLIENT, который в случае insert-ов, update-ов и delete-ов в KK_CLIENT зеркалирует изменения в таблицу KK_CLIENT_MINUS, т.е. всё время поддерживает, что в KK_CLIENT_MINUS всегда лежит копия KK_CLIENT, но только для отрицательных значений поля AMOUNT.

Создаем таблицы:

CREATE TABLE KK_CLIENT(CLIENT_ID INTEGER, CLIENT_NAME VARCHAR2(40), AMOUNT NUMBER);

CREATE TABLE KK_CLIENT_MINUS(CLIENT_ID INTEGER, CLIENT_NAME VARCHAR2(40), AMOUNT NUMBER);

Создаем триггер:

CREATE OR REPLACE TRIGGER KK_TRIG_AIUD_KK_CLIENT

AFTER INSERT OR UPDATE OR DELETE ON KK_CLIENT

FOR EACH ROW

BEGIN

IF (DELETING OR UPDATING) AND :OLD.AMOUNT < 0 THEN

DELETE FROM KK_CLIENT_MINUS WHERE CLIENT_ID = :OLD.CLIENT_ID;

END IF;

IF (INSERTING OR UPDATING) AND :NEW.AMOUNT < 0 THEN

INSERT INTO KK_CLIENT_MINUS

(CLIENT_ID, CLIENT_NAME, AMOUNT)

VALUES

(:NEW.CLIENT_ID, :NEW.CLIENT_NAME, :NEW.AMOUNT);

END IF;

END KK_TRIG_AIUD_KK_CLIENT;

Проверим:

insert into kk_client(client_id, client_name, amount) values (1, 'a', 10);

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

insert into kk_client(client_id, client_name, amount) values (2, 'b', -10);

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

update kk_client set amount = 20 where client_id = 1;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

update kk_client set amount = -20 where client_id = 2;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

update kk_client set amount = 20 where client_id = 2;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

update kk_client set amount = -30;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

update kk_client set amount = 20 where client_id = 2;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

delete from kk_client where client_id = 2;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;

delete from kk_client where client_id = 1;

select client_id, client_name, amount from kk_client;

select client_id, client_name, amount from kk_client_minus;