Триггеры на добавление записи ms sql server. Триггеры. Oracle triggers отключение, включение

Распределенная файловая система (Distributed File System, DFS) настраивается в операционной системе Windows 2000 Server (Панель управления – Администрирование – Распределенная файловая система DFS ) и позволяет объединить файловые ресурсы, находящиеся на различных компьютерах, в одно пространство имен. Таким образом, вместо сети, состоящей из большого количества машин, пользователь видит структуру логических имен, связанных с общими ресурсами.

Преимущества DFS:

· возможность логического представления общих ресурсов, находящихся на различных серверах сети;

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

· наличие графического инструмента администрирования;

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

· сбалансированная нагрузка на общие ресурсы сети за счет связывания одного имени ресурса с разными репликами этого ресурса;

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

· интегрирование с моделью безопасности Windows 2000 – используются единые учетные записи пользователей;

· интеллектуальное кэширование данных на стороне клиента;

· возможность взаимодействия с другими сетевыми файловыми системами.

Необходимость в развертывании распределенной файловой системы может возникать при наличии в сети нескольких файловых серверов или при необходимости предоставить многим пользователям постоянный доступ к нескольким общим ресурсам.

Начальной точкой для логических имен дерева DFS служит корень распределенной файловой системы, для создания которого необходимо указать некоторый общий ресурс, находящийся на сервере. Все остальные имена DFS будут находиться на следующем иерархическом уровне. Общие ресурсы компьютерной сети в дереве DFS представляются с помощью логических имен, имеющих следующее место в полном имени ресурса в сети: \\Имя_Сервера\Логическое_Имя_DFS\ Путь\Файл.

Распределенная файловая система DFS обеспечивает подключение к одному логическому имени до 32 альтернативных общих ресурсов (реплик). Если реплики находятся в разделе NTFS 5.0 в распределенной файловой системе, созданной на серверах Windows 2000 и интегрированной с Active Directory, то для них можно настроить автоматическую синхронизацию – согласование данных (репликацию). В других случаях согласование реплик необходимо выполнять вручную .

Элементы системной интеграции

В распределенных вычислительных системах или общедоступных компьютерных сетях, например в Интернете, существует множество объектов, представляющих интерес – таких, как базы данных, принтеры, серверы службы факсов, приложения, пользователи. Пользователям желательно уметь находить и использовать эти объекты. Администраторам необходимо управлять использованием этих объектов.

Служба каталогов

Каталогом называется информационный ресурс, используемый для хранения сведений о представляющих интерес объектах. В файловых системах в каталоге хранятся сведения о файлах.

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

Служба каталогов может решать следующие задачи:

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

· Распределять каталог по различным компьютерам сети.

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

· Разбивать каталог на несколько разделов для обеспечения возможности хранения большого количества объектов.

Служба каталогов является как средством администратора, так и средством конечного пользователя. По мере роста числа объектов в сети повышается значение службы каталогов. Служба каталогов – это та ось, вокруг которой вращается большая распределенная система.

Active Directory – это служба каталогов, включенная в операционную систему Windows 2000/2003 Server. Она является наглядным примером системной интеграции – расширяет возможности существовавших ранее служб каталогов на базе Windows и добавляет совершенно новые возможности.

Служба каталогов Active Directory обеспечивает безопасность, распределенность, возможность разбиения на разделы и возможность репликации. Она рассчитана на установку в системе любого размера – от одиночного сервера с несколькими сотнями объектов до системы из тысяч серверов с миллионами объектов. Служба каталогов Active Directory предоставляет много новых возможностей, облегчающих поиск и управление большими объемами данных и позволяющих экономить время как администраторам, так и конечным пользователям.

Служба каталогов Active Directory образует пространство имен, в котором имя объекта в каталоге разрешается в сам объект.

Объект – это отдельный именованный набор атрибутов, представляющий нечто конкретное – например, пользователя, принтер, приложение.

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

Класс объектов определяет тип информации, содержащейся в Active Directory для экземпляров (объектов) данного класса. Следовательно, каждый объект принадлежит, по крайней мере, к одному объектному классу, представляющему собой некоторое семейство объектов с определенными общими характеристиками.

Схема службы каталогов Active Directory реализована как набор экземпляров классов объектов, которые хранятся в каталоге. Следовательно, схема – совокупность (матрица) всех атрибутов и классов.

Контейнер подобен объекту в том, что у него есть атрибуты и он является частью пространства имен службы каталогов Active Directory. Но он, в отличие от объекта, не представляет собой нечто конкретное. Он лишь «оболочка» для группы объектов и других контейнеров.

Термин дерево используется для описания иерархии объектов и контейнеров. Вершины дерева обычно являются объектами. Узлы дерева (точки, где дерево ветвится) являются контейнерами. Дерево показывает связь между объектами или путь от одного объекта к другому. Простой каталог является контейнером. Компьютерная сеть или домен также являются контейнерами. Непрерывным поддеревом называется любой неразрывный путь в дереве, включая все составляющие каждого включенного в этот путь контейнера (рис. 3.5).

Рис 3.5. Непрерывное поддерево каталога файлов

Имя идентифицирует любой объект службы каталогов Active Directory. Имена бывают двух типов: различающееся имя и относительное различающееся имя.

Различающееся имя (DN – distinguished name) определяет домен, содержащий объект, а также полный путь по иерархии контейнеров, ведущий к данному объекту. Типичное DN может иметь вид:

/O=Internet/DC=COM/DC=Microsoft/CN=Users/CN=James Smith

Это DN определяет объект-пользователь «James Smith» в домене Microsoft.com. Здесь CN обозначает общее имя. (рис. 3.6)

Рис. 3.6. Графическое представление различающегося имени

Относительное различающееся имя (RDN – Relative Distinguished Name) объекта – это часть его имени, которая представляет собой атрибут самого объекта. В предыдущем примере RDN объекта-пользователя «James Smith» будет CN=James Smith. RDN его родительского объекта будет CN=Users.

Служба каталогов Active Directory обеспечивает эффективную работу сложной корпоративной среды, предоставляя следующие возможности.

· Единая регистрация в сети . Пользователи могут регистрироваться в сети с одним именем и паролем и получать при этом доступ ко всем сетевым ресурсам (серверам, принтерам, приложениям, файлам и т. д.) независимо от их расположения в сети.

· Безопасность информации . Средства аутентификации и управления доступом к ресурсам, встроенные в службу Active Directory, обеспечивают централизованную защиту сети. Права доступа можно определять не только для каждого объекта каталога, но и каждого свойства (атрибута) объекта.

· Централизованное управление . Администраторы могут централизованно управлять всеми корпоративными ресурсами. Рутинные задачи администрирования не нужно повторять для многочисленных объектов сети.

· Администрирование с использованием групповых политик. При загрузке компьютера или регистрации пользователя в системе выполняются требования групповых политик; их настройки хранятся в объектах групповых политик (GPO) и «привязываются» к сайтам, доменам или организационным единицам. Групповые политики определяют, например, права доступа к различным объектам каталога или ресурсам, а также множество других «правил» работы в системе.

· Гибкость изменений . Служба каталогов гибко следует за изменениями структуры компании или организации. При этом реорганизация каталога не усложняется, а может и упроститься. Кроме того, службу каталога можно связать с Интернетом для взаимодействия с деловыми партнерами и поддержки электронной коммерции.

· Интеграция с DNS . Служба Active Directory тесно связана с DNS. Этим достигается единство в именовании ресурсов локальной сети и глобальной сети Интернет, в результате чего упрощается подключение пользовательской сети к Интернету. Служба каталогов Active Directory использует систему DNS в качестве службы определения местоположения. Имена доменов Windows 2000/2003 являются именами доменов DNS.

· Расширяемость каталога . Администраторы могут добавлять в схему каталога новые классы объектов или добавлять новые атрибуты к существующим классам.

· Масштабируемость . Служба Active Directory может охватывать как один домен, так и множество доменов, один контроллер домена или множество контроллеров домена – т. е. она отвечает требованиям сетей любого масштаба. Несколько доменов можно объединить в дерево доменов, а несколько деревьев доменов можно связать в лес.

· Репликация информации . В службе Active Directory используется репликация служебной информации в схеме со многими ведущими (multi-master), что позволяет модифицировать каталог на любом контроллере домена. Наличие в домене нескольких контроллеров обеспечивает отказоустойчивость и возможность распределения сетевой нагрузки.

· Гибкость запросов к каталогу . Пользователи и администраторы сети могут быстро находить объекты в сети, используя свойства объекта (например, имя пользователя или адрес его электронной почты, тип принтера или его местоположение и т. п.). Это, в частности, можно сделать при помощи команды Пуск – Поиск папку Мое сетевое окружение или оснастку Active Directory – пользователи и компьютеры .Оптимальность процедуры поиска достигается благодаря использованию глобального каталога.

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

Основные компоненты любой службы каталога – база данных, содержащая нужную информацию, и один или несколько протоколов, обеспечивающих доставку данных пользователям. Active Directory обеспечивает хранение любой общедоступной информации. Как и другие службы каталогов, Active Directory обеспечивает некоторый механизм хранения информации и протоколы для доступа к ней.

Можно сказать, что служба Active Directory «стоит на трех китах»:

· Стандарт Х.500

· Служба DNS (Domain Name Service)

· Протокол LDAP (Lightweight Directory Access Protocol)

В Active Directory частично реализована модель данных, описываемая стандартом Х.500. Традиционная в сетях TCP/IP служба DNS используется, в частности, для поиска контроллеров домена, а благодаря протоколу LDAP клиенты могут по имени находить в каталоге Active Directory нужные объекты и получать доступ к их атрибутам.

Для понимания структуры Active Directory рассмотрим сначала отличия Windows 2000 от предыдущих версий серверных операционных систем Windows. Компьютеры на базе Windows 2000 по-прежнему объединяются в домены. Домены – это известное решение для администрирования групп, предоставляющее каждому пользователю учетную запись в конкретном домене. Однако, в отличие от Windows NT Server 4.0, где доменам давались простые строковые имена (имена NetBIOS), в среде Windows 2000 Server каждый домен должен иметь имя, отвечающее соглашениям именования доменов Domain Name System (DNS). Так, домен, имеющий имя NetBIOS MainOffice при обновлении может получить новое имя типа mainoffice.company.com.

В каждом домене один или несколько компьютеров должны выполнять функции контроллеров домена. В среде Windows 2000 Server каждый контроллер домена содержит полную копию базы данных Active Directory этого домена.

В Active Directory используются так называемое ядро Extended Storage Engine (ESE) и два различных протокола, обеспечивающих связь между клиентами и базой данных.

Для поиска контроллера домена клиент обращается к протоколу, описанному в DNS – «стандартной» службе каталогов, применяемой в настоящее время для сетей TCP/IP.

Для доступа к данным в Active Directory клиент использует протокол Lightweight Directory Access Protocol (LDAP) (рис. 3.7).

Рис 3.7. Доступ к данным с использованием LDAP

После того как с помощью DNS нужный контроллер домена обнаружен, для доступа к данным Active Directory используется протокол LDAP. Протокол LDAP работает поверх TCP/IP и – как следует из названия протокола – определяет способы доступа к каталогу со стороны клиентов.

Помимо механизма доступа данный протокол реализует соглашения по именованию информации в каталоге, в явном виде описывая структуру этой информации. Для клиента все данные, хранящиеся в базе LDAP, представляются в виде иерархического дерева. Каждый узел дерева (объект или элемент) может быть либо контейнером, либо листом. Различие между ними вполне очевидно: контейнеры могут содержать другие элементы, а листья – нет.

Каждый элемент (контейнер или лист) представляет собой некоторый объектный класс, определяющий атрибуты (называемые также свойствами) данного элемента. Поскольку атрибуты есть и у контейнеров, и у листьев, информация, хранящаяся в дереве каталога, распределена по всем узлам.

Тип информации (объектные классы и типы атрибутов), содержащейся в конкретной базе данных Active Directory, задается схемой, определенной для этого каталога. В Active Directory схема каждого каталога представлена элементами, хранящимися непосредственно в самом каталоге. Компания Microsoft определяет стандартную схему, однако пользователи и разработчики программных средств могут добавлять новые классы и типы атрибутов. Изменение схемы каталога – полезная возможность, которой нужно пользоваться очень осторожно, поскольку такие изменения могут иметь весьма значительные последствия.

Три́ггер (trigger ) - это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) - по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификации UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Триггеры создаются отдельно для каждой таблицы и располагаются в обозревателе объектов в папке «Triggers». Создадим триггеры для таблицы «teachers». Папка «Triggers» будет входить в состав таблицы «teachers»:

Создадим триггер, выводящий сообщение «Запись добавлена» при добавлении записи в таблицу. Создадим новый триггер, щёлкнув ПКМ по папке «Triggers» в таблице «teachers» и в появившемся меню выбрав пункт «New Trigger». Появится следующее окно с новым триггером:

Рассмотрим структуру триггеров:

1) Область определения имени функции (Trigger_Name);

2) Область, показывающая для какой таблицы создаётся триггер (Table_Name);

3) Область, показывающая когда выполнять триггер (INSERT – при создании записи в таблице, DELETE – при удалении и UPDATE – при изменении) и как его выполнять (ALTER – после выполнения операции, INSTEAD OF – вместо выполнения операции);

4) Тело триггера, содержит команды языка программирования запросов TSQL.

В окне нового триггера наберем код:

CREATE TRIGGER tr_add

ON dbo.teachers

PRINT "Новая запись добавлена"

Из рисунка видно, что создаваемый триггер «Индикатор добавления» выполняется после добавления записи (AFTER INSERT) в таблицу «dbo.teachers» (ON dbo.teachers). После добавления записи триггер выведет на экран сообщение "Новая запись добавлена" (PRINT "Новая запись добавлена"). Выполним набранный код, нажав кнопку на панели инструментов. В нижней части окна с кодом появиться сообщение «Command(s) completed successfully.».

Проверим, как работает новый триггер. Создадимновый пустой запрос и в нём наберем следующую команду для добавления новой записи в таблицу «dbo.teachers»:

insert into dbo.teachers

"Сидоров"

Выполним набранную команду, нажав кнопку на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение «Новая запись добавлена»

Аналогичным образом создаются триггеры с UPDATE и DELETE.

Задание: в соответствии со своей темой создайте в SQL Server Management Studio представления (3-4), запросы(3-4), хранимые процедуры(3-4), пользовательские функции(3-4), триггеры (INSERT, UPDATE и DELETE ). Используйте при создании операторы условия и циклы.

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

Генератор – это хранящаяся в БД программа, выдающая при каждом обращении к ней уникальное число.

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

CREATE GENERATOR <Имя генератора>

Начальное значение задается инструкцией:

SET GENERATOR <Имя генератора> TO <Начальное значение (целое число)>

CREATE GENERATOR GenStore

SET GENERATOR GenStore TO 1

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

GEN_ID (<Имя генератора>, <Шаг>)

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

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

CREATE TRIGGER <> FOR <>

{BEFORE | AFTER}

{UPDATE | INSERT | DELETE}

AS <Тело триггера>

Описатели ACTIVE | INACTIVE определяют активность триггера сразу после его создания. По умолчанию действует ACTIVE.

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

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

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

Удаление триггера:

DROP TRIGGER <Имя триггера>

Изменение триггера:

Для доступа к значениям столбца используются инструкции формата:

OLD.<Имя столбца> - обращается к старому (до внесения изменений) значению столбца,

NEW.<Имя столбца> - обращается к новому (после внесения изменений) значению столбца.

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

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

NEW.S_Code = GEN_ID (GenStore, 1);

При добавлении к таблице Store новой записи ключевому столбцу S_Code этой записи автоматически присваивается уникальное значение. Это обеспечивается обращением GEN_ID к генератору GenStore.

Реализация каскадного удаления записей с участием триггера

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

DELETE FROM Cards WHERE Store.S_Code = Cards.C_Code2;

После удаления записи в таблице Store буду автоматически удалены все соответствующие записи в таблице Cards.

Замечание : для таблиц не должны действовать ограничения ссылочной целостности, заданные на физическом уровне.

Обновление столбцов связи (ключевых столбцов) связанных таблиц, заключающееся в том, что при изменении значения столбца связи главной таблицы соответственно изменяются значения столбца связи всех связанных записей подчиненной таблицы.

CREATE TRIGGER ChangeStore FOR Store

IF (OLD.S_Code <> NEW.S_Code)

THEN UPDATE Cards

SET C_Code2 = NEW.S_Code

WHERE C_Code2 = OLD.S_Code;

При изменении столбца S_Code, используемого для связи главной таблицы Store с подчиненной таблицей Cards, автоматически изменяются значения столбца связи C_Code2 соответствующих записей подчиненной таблицы.

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

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

Триггер всегда работает с текущей записью и реализует конкретное действие.

Триггеры могут выполняться до наступления события (параметр BEFORE) или после наступления события (параметр AFTER).

Триггеры различают по направлению действия:

• INSERT - на добавление записи;
• UPDATE - на редактирование записи;
• DELETE - на удаление записи.

Триггер создается для конкретной таблицы и принадлежит ей. Если таблица имеет несколько триггеров одного направления действия, то время их срабатывания определяется в первую очередь параметрами BEFORE и AFTER , а при одинаковом значении параметра наступления события - параметром POSITION с указанием номера (порядка) срабатывания триггера.

При работе с триггерами следует иметь в виду, что:

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

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

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

Для создания триггера используется оператор CREATE TRIGGER.

Формат оператора

CREATE TRIGGER FOR

[ ACTIVE | INACTIVE ]

[ BEFORE |AFTER]

[ INSERT | UPDATE DELETE ]

[ POSITION ]

Назначение опций:

ACTIVE - триггер активен, т. е. при обращении к указанной таблице выполняется процедура, записанная в стеле триггерам

INACTIVE - триггер пассивен, т. е. триггер создан и хранится на сервере, но при обращении к указанной таблице стело триггера> не выполняется;

BEFORE - до наступления события;

AFTER - определяет время срабатывания после наступления события;

INSERT - определяет для триггера событие добавления записи в таблицу;

UPDATE - определяет для триггера событие редактирования записи в таблице;

DELETE - определяет для триггера событие удаления записи из таблицы;

POSITION - определяет номер (позицию) срабатывания триггера внутри определенного события срабатывания (BEFORE или AFTER).

Для описания процедуры стело триггера> используются те же операторы и конструкции, которые используются при создании хранимой процедуры. В заголовке триггера определяют его активность, событие срабатывания, действие, на которое он реагирует, и, при необходимости, позицию срабатывания триггера.

При написании стела триггера> дополнительно можно использовать ключевые слова OLD (до события) и NEW (после события) с последующим указанием имени поля.

Так как в удаленной базе данных все изменения в таблицах (добавление, редактирование и удаление записи) производятся в выборках (в оперативной памяти), то, например, при изменении значения поля можно обратиться как к старому (до изменения) значению поля - ОЬО.симя поля>, так и к новому (после изменения) значению поля - NEW-симя полях Если в указанное поле изменение не вносилось, то ОЬО.симя поля> будет равно NEW.chmb полях

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

CREATE TRIGGER T_COMPCODE FOR COMPOSERS

ACTIVE BEFORE INSERT POSITION 0

new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data born is not null) then begin

new.actuallyage = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =(COMPOSERS.data_day-COMPOSERS. data born) /365; else new.age=null; end

Триггер срабатывает до наступления события добавления новой записи и вычисляет возраст композитора. Вычисленный возраст записывает в поле «actually_age ».

Изменение триггера

Для изменения триггера используется команда ALTER TRIGGER , имеющая аналогичный формат и аналогичный принцип работы, что и команда ALTER PROCEDURE для изменения тела хранимой процедуры.

Формат команды

ALTER TRIGGER FOR [ ACTIVE I INACTIVE

BEFORE I AFTER

INSERT I UPDATE DELETE

[ POSITION 1

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

Пример 6.11. Редактирование триггера.

ALTER TRIGGER T_COMPCODE FOR COMPOSERS ACTIVE BEFORE INSERT POSITION 5 AS

new.code_composer=gen_id(g_composers, 1); if(COMPOSERS.data_born is not null) then begin

new.actually_age = (CAST("NOW" AS DATE)-COMPOSERS.data_born)/365; if(COM POSERS.dataday is not null) then COM POSERS.age =

(COMPOSERS.data_day-COMPOSERS.data born) /365; else new.age=null; end

else begin new.age=null; new.actually_age=null; end end

В триггер, созданный в примере 6.10, внесено изменение: задана новая позиция (POSITION ) срабатывания триггера - 5.

Удаление триггера

Для удаления триггера используют команду

Пример 6.12. Удаление триггера.

DROP TRIGGER T_COMPCODE

Восстановить удаленный триггер нельзя.

Использование триггера в каскадных воздействиях

Как отмечалось выше, предусмотрено два вида каскадных воздействий: каскадное изменение и каскадное удаление. Поэтому для каждой родительской таблицы надо предусмотреть два триггера: один - для каскадного изменения, второй - для каскадного удаления. При этом условия ссылочной целостности должны быть удалены.

Пример 6.13. Каскадное удаление записей.

При удалении фамилии из родительской таблицы ГАМ необходимо удалить соответствующие фамилии (по ключам фами-

лии) во всех дочерних таблицах (в примере таблицы AUTHOR и BOOK).

CREATE TRIGGER DEL FAM FOR FAM ACTIVE

DELETE FROM AUTHOR

WHERE FAM.KEYFAM = AUTHOR. KEYFAM; DELETE FROM BOOK

WHERE FAM.KEY FAM = BOOK.KEY FAM;

Пример 6.14. Каскадное редактирование записей.

При изменении значения ключевого поля (KEY FAM) в родительской таблице FAM необходимо изменить соответствующие значения внешних ключей во всех дочерних таблицах (в примере таблицы AUTHOR и BOOK).

CREATE TRIGGER UPD FAM FOR FAM ACTIVE

BEFORE UPDATE AS

IF (OLD.KEYFAM NEW.KEY FAM) THEN BEGIN

UPDATE AUTHOR SET KEY FAM = NEW.KEY FAM

WHERE KEY FAM - OLD.KEY FAM; UPDATE BOOK SET KEYFAM - N EW. KEYFAM

WHERE KEY FAM = OLD.KEY FAM;

Особенности использования каскадных воздействий:

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

Обеспечение достоверности данных с помощью триггера

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

а. Обеспечение уникальности значения поля

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

Пример 6.15. Заполнение поля первичного ключа.

Написать триггер по добавлению уникального значения первичного ключа KEYFAM. Генератор GFAM уже создан.

CREATE TRIGGER К РАМ FOR FAM

N EW. KEYFAM = GEN_ID(G_FAM, 1);

Пример 6.16. Заполнение информационного поля.

CREATE TRIGGER TCOMPDATE FOR COMPOSERS

ACTIVE BEFORE UPDATE POSITION 0

if(COMPOSERS.data born is not null) then begin

new.actually_age = (CAST("NOW" AS

DATE)-COMPOSERS.data_born)/365; if(COMPOSERS.data day is not null) then COM POSERS.age =

(COM POSERS.data_day-COMPOSERS.data_born)/365; else new.age=null; end

else begin new.age=null; new.actually_age=null; end

В данном примере вычисляется возраст человека и заполняется поле actually age.

Ведение журнала аудита с помощью триггера

В удаленных базах данных особый интерес представляет ведение журнала изменений таблиц базы данных с целью определения источника недостоверных данных. При ведении журнала изменений в специальной таблице фиксируется:

• выполненное действие над таблицей;
• новое значение поля;
• старое значение поля;
• дата внесения изменения;
• фамилия, имя и отчество пользователя (USER NAME );
• номер (имя) рабочей станции.

Пример 6.17. Автоматическое заполнение журнала аудита.

CREATE TRIGGER AFTJNS_DOGS FOR DOGS ACTIVE AFTER INSERT POSITION 0 AS begin

insert into log (act, table_name,record_id) values("INSERT","DOGS",DOGS. ID); end

CREATE TRIGGER AFT_UPD_DOGS FOR DOGS ACTIVE AFTER UPDATE POSITION 0 AS begin

insert into log (act,table_name,record_id) values(’UPDATE’,"DOGS’,DOGS.ID); end

CREATE TRIGGER AFT DEL DOGS FOR DOGS ACTIVE AFTER DELETE POSITION 0 AS

insert into log (act,table_name,record_id) values(’DELETE","DOGS’,DOGS.ID); end

В этом примере для таблицы DOGS созданы три триггера (по одному на каждое событие INSERT, UPDATE и DELETE). Каждый из триггеров добавляет в таблицу аудита log одну строку, которая содержит поля «выполненное действие», «имя таблицы» и «номер записи». При желании количество полей в таблице аудита можно увеличить.

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

Назначение триггеров

Предотвращение изменения (например, предотвратить счета от изменений после того как они разосланы).
. Журналирование изменения (например, хранить копии старых данных).
. Аудит изменений (например, вести лог пользователей и ролей, участвующих в изменениях).
. Фиксация изменений (например, обеспечить, чтобы все изменения датировались по часам сервера, а не клиента).
. Реализация бизнес-правил.
. Репликация данных (например, хранить записи всех изменений, которые будут отправлены в другой базе данных более поздней версии).
. Повышение производительности (например, обновление сальдо после каждой детали сделки, для ускорения запросов).

Объявление триггеров

CREATE TRIGGER {BEFORE|AFTER } {DELETE|INSERT|UPDATE [OF ]} ON REFERENCING {OLD {[ROW ]|TABLE [AS ] } NEW {ROW|TABLE } [AS ] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC ]

[END ]

Ключевые слова

. BEFORE|AFTER – время запуска триггера – до | после операции обновления.
. DELETE|INSERT|UPDATE = событие срабатывания триггера.
. FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
. FOR EACH STATEMENT – для всей команды (действует по умолчанию).
. REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

Ограничения триггеров

Тело триггера не может содержать операторов:
. Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
. Обработки транзакций (COMMIT, ROLLBACK)
. Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
. Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
. Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
. Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
. Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @ .
. В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
. В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
. Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
. Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

Недостатки триггеров

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

Изменение и удаление триггеров

Для удаление триггера используется оператор DROP TRIGGER
. Для изменения триггера используется оператор ALTER TRIGGER …
. Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE } {BEFORE|AFTER } {INSERT|DELETE|UPDATE } [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION ] {FOR|AFTER|INSTEAD OF } {INSERT|UPDATE|DELETE }
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
. В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER