Методическая разработка аудиторного занятия по МДК.02.02. Технология разработки и защита баз данных на тему "Создание и изменение триггеров. Примеры использования триггеров"

Тема: Создание и изменение триггеров. Примеры использования триггеров.

Цели урока:

1.      Образовательные цели:

·      сформировать знания о создании и изменении триггера;

·      привести примеры практического использования триггера;

·      повторить знания о создании хранимых процедур;

·      провести контроль операторов  хранимых процедур.

2.      Развивающие цели:

·      продолжить работу над развитием операционного стиля мышления через всестороннюю оценку ситуации, оптимальное планирование действий, поиск информации, необходимой для решения задачи – формирование компетентности в сфере познавательной деятельности;

·      работать над возможностью приобретения опыта создания и преобразования информационного объекта с помощью информационных технологий – формирование технологической компетентности,

·      развивать внимание, творческие способности обучающихся.

3. Воспитательные цели:

·      воспитание информационной культуры обучающихся, внимательности, аккуратности, дисциплинированности, усидчивости.

Тип урока: комбинированный урок.

Продолжительность урока: 2 урока по 45 мин.

Оборудование: ПК, ОС Windows 7,  мультимедиа-проектор, Delphi 7, утилита Interactive SQL,  презентация  «Создание и изменение триггеров».

Ход урока

I. Организационный момент (2 мин).

– приветствие,

– контроль присутствия студентов на занятии,

– создание деловой атмосферы на занятии,

Объявление темы и главной образовательной цели занятия, объяснение хода занятия.

II. Актуализация знаний (15 мин).

Ответить на вопросы:

1. Какие виды хранимых процедур мы изучили?
2. Какие значения возвращает хранимая процедура выбора?
3. Опишите выполнение следующих операторов:

procedure TForml.ButtonlClick(Sender: TObject);
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM pOklad2 (2000, 3000)');
Query1.Open;
end;

Ответ: При нажатии кнопки Button1 свойству SQL компонента Query1 присваивается код вызова хранимой процедуры pOklad2, затем запрос выполняется. В результате набор данных Query1 содержит записи, удовлетворяющие заданному в процедуре условию отбора. Отметим, что столбцы набора данных образуют выходные параметры процедуры, в данном случае ими являются столбцы opName и opOklad.

4. Как вызвать хранимую процедуру выбора?
5. Какой инструкцией вызывается хранимая процедура действия?
6. Для чего предназначен компонент storedProc?( Для вызова хранимой процедуры действия из приложения)
7. Укажите основные свойства компонента storedProc?

III. Изучение нового материала (55 мин).

Использование триггеров

Триггер представляет собой процедуру, которая находится на сервере БД и вызывается автоматически при модификации записей БД, т. е. при изменении столбцов или при их удалении и добавлении. В отличие от хранимых процедур, триггеры нельзя вызывать из приложения клиента, а также передавать им параметры и получать от них результаты.
Триггер по своей сути похож на обработчики событий BeforeEdit, AfterEdit, Beforelnsert, Afterlnsert, BeforeDelete и AfterDelete, связанных с модификацией таблиц. Триггер может вызываться при редактировании, добавлении или удалении записей до и/или после этих событий.

Замечание

Изменения, внесенные триггером в транзакции, которая оказалась отмененной, также отменяются.

Триггеры обычно используются для программной реализации так называемых бизнес-правил. С их помощью удобно реализовывать различные ограничения, например, ограничения на значения столбцов или ограничения ссылочной целостности, а также выполнять такие действия, как накапливание статистики, работы с БД или резервное копирование записей.

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

Создание триггера выполняется инструкцией CREATE TRIGGER, имеющей формат:

CREATE TRIGGER <Имя триггера> FOR <Имя таблицы>
                 [ACTIVE  |  INACTIVE]
                {BEFORE | AFTER}
                {UPDATE | INSERT | DELETE}

                    [POSITION <Число>]
                         AS <Тело триггера>

Описатели ACTIVE и INACTIVE определяют активность триггера сразу после его создания. По умолчанию действует ACTIVE, и созданный триггер активен, т. е. при наступлении соответствующего события будет выполняться. Если триггер неактивен, то при наступлении соответствующего события он не вызывается. Ранее созданный триггер можно активизировать или, наоборот, деактивизировать.

Описатели BEFORE и AFTER задают момент начала выполнения триггера до или после наступления соответствующего события, связанного с изменением записей.

Описатели UPDATE, INSERT и DELETE определяют, при наступлении какого события вызывается триггер — при редактировании, добавлении или удалении записей соответственно.

Для одного события можно создать несколько триггеров, каждый из которых будет автоматически выполнен (если находится в активном состоянии). При наличии нескольких триггеров порядок их вызова (выполнения) определяет число, указанное в операнде POSITION. Триггеры выполняются в порядке возрастания этих чисел.
Созданный триггер можно удалить или изменить. Удаляется триггер инструкцией

                          DROP TRIGGER <Имя триггера>.
Изменение триггера выполняется инструкцией ALTER TRIGGER, формат которой не отличается от формата инструкции создания триггера. После выполнения инструкции ALTER TRIGGER предыдущее описание триггера с указанным именем заменяется на новое.
Программирование триггера аналогично программированию хранимой процедуры, для чего используется специальный язык, позволяющий также создавать хранимые процедуры.
Отметим, что для доступа к значениям столбца используются конструкции формата:

OLD.<Имя столбца>
         NEW.<Имя столбца>

Первая из них позволяет обратиться к старому (до внесения изменений), а вторая — к новому (после внесения изменений) значениям столбца.

Значения OLD и NEW

Значение OLD.ИмяСтолбца позволяет обратиться к состоянию столбца, имевшему место до внесения изменений, а значение NEW.ИмяСтолбца — к состоянию столбца после внесения изменений.
Например, механизм обеспечения ссылочной целостности "cascade", в случае изменения значения первичного ключа родительской таблицы, на мнемоническом языке можно описать так:

IF (OLD.PrimaryKeyPoдитeля <> NEW.РгimагуКеуРодителя) THEN
UPDATE ДочерняяТаблица
SET ForeignКеуДочернейТаблицы = NEW.РгimагуКеуРодителя
WHERE ForeignКеуДочернейТаблицы = OLD.PrimaryKeyРодителя;
В качестве примера приведем код триггера для родительской таблицы Realty, который при изменении значения ее первичного ключа будет автомагически изменять значение внешнего ключа дочерней таблицы Lease. Другими словами, если в таблице Realty изменилось значение поля Adr, то триггер изменит значение поля Adr в соответствующей записи таблицы Lease.

CREATE TRIGGER UPDAT_REALTY FOR Realty
ACTIVE
BEFORE UPDATE
AS
BEGIN
IF (OLD.Adr <> NEW.Adr)
THEN UPDATE Lease
SET Adr = NEW.Adr
WHERE Adr = OLD.Adr;
END
В том случае, если значение в столбце не изменилось, то OLD.ИмяСтолбца будет равно NEW.ИмяСтолбца.

Примеры использования триггера

Рассмотрим использование триггера для реализации ограничений ссылочной целостности и для занесения в ключевые столбцы уникальных значений.

В связи с тем, что InterBase не поддерживает автоинкрементные поля, при создании ключевого столбца, требующего уникальности значений, рекомендуется поступать так:

1. При создании таблицы задать ключевой столбец целочисленного типа.
2. Создать генератор, который при обращении к нему возвращает уникальное целочисленное значение.
3. Создать триггер, который при добавлении к таблице новой записи обращается к генератору и заносит возвращаемое им значение в ключевое поле.

Следующий пример иллюстрирует описанную последовательность действий:
/* Создание таблицы */
CREATE TABLE Store
              (S_Code INTEGER NOT NULL,
          ...
          PRIMARY KEY (S_Code));

/* Создание генератора */
CREATE GENERATOR GenStore;
SET GENERATOR GenStore TO 1;

/* Создание триггера */
CREATE TRIGGER CodeStOre FOR Store
ACTIVE
BEFORE INSERT
        AS
        BEGIN
             NEW.S_Code = GEN_ID(GenStore, 1);
        END

После добавления к таблице store новой записи ключевому столбцу s_Сode этой записи автоматически присваивается уникальное значение. Это обеспечивается обращением GEN_ID к генератору GenStore, который создается отдельно от триггера (работа с генераторами рассмотрена ниже).

Ограничения ссылочной целостности для связанных таблиц включают в себя:
1. каскадное удаление записей;
2. запрет на редактирование ключевых столбцов.

Рассмотрим, как можно реализовать каскадное удаление записей с участием триггера.
Пусть имеются две таблицы: главная Store и подчиненная Cards, связанные по полям кода s_code и c_code2 соответственно:
CREATE TABLE Store
          (S_Code INTEGER NOT NULL,
        ...
        PRIMARY KEY (S_Code));
CREATE TABLE Cards
          (C_Code INTEGER NOT NULL,
         C_Code2 INTEGER NOT NULL,

           …
          PRIMARY KEY (C_Code) ) ;
Каскадное удаление записей для связанных таблиц заключается в том, что если из главной таблицы удаляется запись, то и в подчиненной таблице должны быть удалены все соответствующие ей записи.

В нашем случае это выполняется следующим образом:
CREATE TRIGGER DeleteStore FOR Store
ACTIVE
AFTER DELETE
             AS
             BEGIN
                    DELETE FROM Cards WHERE Store.S_Code = Cards.C_Code2;
             END
После удаления записи в таблице store (склад) будут автоматически удалены все соответствующие записи в таблице Cards (движение товара).

Замечание

Для таблиц не должны действовать ограничения ссылочной целостности, заданные на физическом уровне, например, так:
CONSTRAINT rStoreCards
FOREIGN KEY (C_Code2) REFERENCES Store
В противном случае при попытке удалить запись из главной таблицы генерируется исключение. Если есть такие ограничения, то их можно удалить, например, с помощью программы SQL Explorer.

Аналогичным способом можно реализовать и обновление столбцов связи (ключевых столбцов) связанных таблиц, заключающееся в том, что при изменении значения столбца связи главной таблицы соответственно изменяются значения столбца связи всех связанных записей подчиненной таблицы.
Например:
CREATE TRIGGER ChangeStore FOR Store
ACTIVE
BEFORE UPDATE
                 AS
                 BEGIN
                     IF (OLD.S_Code <> NEW.S_Code)
                          THEN UPDATE Cards
                                      SET C_Code2 = NEW.S_Code
                                      WHERE C_Code2 = OLD.S_Code;
                 END
При изменении столбца S_code, используемого для связи главной таблицы store с подчиненной таблицей cards, автоматически изменяются значения столбца связи C_code2 соответствующих записей подчиненной таблицы.

Замечание

Чтобы столбец связи главной таблицы можно было редактировать, по нему не должен быть создан ключ. Если ключ создан, то его следует удалить, например, с помощью программы SQL Explorer. Более кардинальным решением проблемы является создание таблицы заново с помощью инструкции ALTER TABLE.
Поскольку использование триггеров не допускает существования ограничений на физическом уровне (при определении структуры таблиц), необходимо обеспечить также занесение значения в столбец связи подчиненной таблицы при добавлении в нее новой записи.

Создание генераторов

Напомним, что, в отличие от базы данных Paradox, для таблиц InterBase отсутствует автоинкрементный тип, обеспечивающий автоматическую установку уникальных значений. Поэтому для обеспечения уникальности значений ключевых столбцов совместно с триггерами используются генераторы. Генератор возвращает уникальное целочисленное значение.

С помощью языка SQL-сервера можно создать генератор и установить для него начальное значение.

Генератор создается следующей инструкцией:
                                        CREATE GENERATOR <Имя генератора>;

Начальное значение задается инструкцией:
SET GENERATOR <Имя генератора> ТО <Начальное значение>;

Начальное значение представляет собой целое число, начиная с которого формируется числовой ряд.
Обращение к созданному генератору выполняется с помощью функции:

GET_ID (<Имя генератора>,<Шаг>);
Эта функция возвращает значение, увеличенное на целочисленный шаг относительно предыдущего значения генератора.

Пример:

CREATE GENERATOR GenStore;

SET GENERATOR GenStore TO 1;

Здесь создаётся генератор GenStore, имеющий начальное значение 1. Пример обращения к этому генератору GEN_ID(GenStore, 1).

IV. Закрепление изученного материала (15 мин.).

1. Запустите утилиту Interactive SQL.
2. Создадайте триггер UPDAT_REALTY.
3. Попробуйте изменить значение адреса в таблице Realty. Это не удается. Дело в том, что сервер InterBase поддерживает механизм обеспечения ссылочной целостности "prohibit". В данном случае он реализован на основании декларации связи таблиц FOREIGN KEY (AdR) REFERENCES Realty (AdR) в скрипте tables.sql при создании таблицы Lease.
4. Удалите эту связь (ограничение INTEG_7):

ALTER TABLE Lease
            DROP CONSTRAINT INTEG_7
5. Можете убедиться, что она отсутствует: теперь таблица Lease не связана с Realty.
6. Измените значение адреса в таблице Realty.
Как видите, теперь при изменении значения ее первичного ключа триггер автоматически изменяет значение внешнего ключа дочерней таблицы Lease. Другими словами, реализован механизм обеспечения ссылочной целостности "cascade".

V. Подведение итогов и домашнее задание (3 мин.). 

Конспект. Хомоненко А.Д. и др. Delphi 7 стр. 893-897.