Trigger intitle все публикации пользователя предыдущая. Триггеры DDL уровня сервера

Windows 7, XP

Приднестровский Государственный Университет

по дисциплине «Базы и банки данных »

г. Тирасполь

ЛАБОРАТОРНАЯ РАБОТА № 8

Тема: «Триггеры: создание и применение»

ЦЕЛЬ РАБОТЫ: научиться работать с триггерами.

Теоретическая часть.

Определение триггера в стандарте языка SQL

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

Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных . Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.

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

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

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

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

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

· накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

· поддержка репликации.

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=

CREATE TRIGGER имя_триггера

BEFORE | AFTER <триггерное_событие>

ON <имя_таблицы>

<тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Ясно, что старые значения не применимы для событий вставки, а новые – для событий удаления.

Реализация триггеров в среде MS SQL Server

В реализации СУБД MS SQL Server используется следующий оператор создания или изменения триггера:

<Определение_триггера>::=

{CREATE | ALTER} TRIGGER имя_триггера

ON {имя_таблицы | имя_просмотра }

{ { FOR | AFTER | INSTEAD OF }

{ [ DELETE] [,] [ INSERT] [,] [ UPDATE] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

sql_ оператор [...n]

{ {FOR | AFTER | INSTEAD OF } { [,]

}

[ WITH APPEND]

[ NOT FOR REPLICATION]

{ IF UPDATE(имя _ столбца )

[ {AND | OR} UPDATE(имя _ столбца )] [...n]

IF (COLUMNS_UPDATES(){ оператор _ бит _ обработки }

бит_маска_изменения)

{оператор_бит_сравнения }бит_маска [...n]}

sql_оператор [...n]

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

Рассмотрим назначение аргументов из команды CREATE | ALTER TRIGGER.

Имя триггера должно быть уникальным в пределах базы данных. Дополнительно можно указать имя владельца .

При указании аргумента WITH ENCRYPTION сервер выполняет шифрование кода триггера, чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной .

Типы триггеров

В SQL Server существует два параметра, определяющих поведение триггеров:

· AFTER. Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера, то будут отклонены и пользовательские изменения. Можно определить несколько AFTER-триггеров для каждой операции (INSERT, UPDATE, DELETE). Если для таблицы предусмотрено выполнение нескольких AFTER-триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER-триггерами.

· INSTEAD OF. Триггер вызывается вместо выполнения команд. В отличие от AFTER-триггера INSTEAD OF-триггер может быть определен как для таблицы, так и для просмотра. Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD OF-триггер.

Триггеры различают по типу команд, на которые они реагируют.

Существует три типа триггеров:

· INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT.

· UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE.

· DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF } { [,] определяют, на какую команду будет реагировать триггер. При его создании должна быть указана хотя бы одна команда. Допускается создание триггера, реагирующего на две или на все три команды.

Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.

При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.

Конструкция AS sql_оператор[...n] определяет набор SQL - операторов и команд, которые будут выполнены при запуске триггера.

Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как:

· создание, изменение и удаление базы данных;

· восстановление резервной копии базы данных или журнала транзакций.

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

Программирование триггера

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

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

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

· команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера, можно использовать функцию @@ROWCOUNT; она возвращает количество строк, обработанных последней командой. Следует подчеркнуть, что триггер запускается не при попытке изменить конкретную строку, а в момент выполнения команды изменения. Одна такая команда воздействует на множество строк, поэтому триггер должен обрабатывать все эти строки.

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

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

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE, вызвавших выполнение триггера, можно использовать функцию COLUMNS_UPDATED(). Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение "1", то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца).

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

DROP TRIGGER {имя_триггера} [,...n]

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

Практическая часть .

Для того, чтобы создать триггер необходимо:

После выбора Создать триггер появляется заготовка для ввода текста вашего триггера

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

Пример 1.

Создать триггер для обработки операции удаления записи из таблицы Kafedrs , например, такой команды:

Для кафедры, код которой указан при удалении записи, необходимо проверить, а нет ли сотрудников, работающих на этой кафедре. Иначе запись с этой кафедрой просто нельзя удалять. Таблицы Kafedrs и Sotrudniki связаны, и поэтому база данных сама не позволит это сделать. Удалим связь между таблицами. Для этого зайдем в Диаграммы баз данных -> Diagram_1 и удалим связь:

Сохраним изменения.

Теперь свободно можем удалить запись с названием кафедры, даже если в таблице Sotrudniki есть сотрудники, работающие на этой кафедре. Напишем триггер, который этого не допустит. Триггер будет иметь следующий код:

Теперь, если попытаться удалить кафедру, на которой работает хотя бы один сотрудник, то появится сообщение:

Пример 2.

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

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

Создадим новый триггер и напишем следующий код:

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

Триггер откроется на изменения, об этом будет свидетельствовать слово ALTER вместо CREATE перед именем триггера. Допишем в коде, через запятую, после слова INSERT слова UPDATE.

Выполните запрос на обновления триггера.

Теперь, при некорректном вводе данных будет появляться следующее сообщения:

Пример 4.

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

Добавим в таблицу Sotrudniki новое поле Is_deleted, которое будет принимать значение True или False (подлежит сотрудник удалению или нет). Укажем ему тип bit и значение по умолчанию 0 (False).

Создадим новый триггер для таблицы Sotrudniki со следующим кодом.

Задание для самостоятельной работы.

1. Ознакомьтесь с теоретическим материалом.

2. Выполните задания из практической части.

3. Удалите связь между таблицей Sotrudniki и Dolghnosti. Напишите триггер не позволяющий удалять запись их Dolghnosti , если есть хотя бы один сотрудник, занимающий эту должность.

4. Напишите триггер, не позволяющий сотруднику, чей стаж меньше 3 лет занимать какую либо должность, например старшего преподавателя.

Изучить типы триггеров, которые могут создаваться на сервере MS SQL Server 2000.
Изучить операторы описания триггеров разных типов и ограничения, накладываемые на допустимые операторы внутри тела триггера.
Изучить порядок создания и методы отладки триггеров на сервере MS SQL Server 2000.
Разработать пять триггеров для учебной базы данных «Библиотека», предложенных вашим преподавателем из заданий, описанных в работе.
Подготовить отчет о проделанной работе в электронном виде.

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

Триггеры это методы, с помощью которых разработчик приложений для MS SQL Server может обеспечить целостность базы данных. Это тип хранимой процедуры, которая активизируется при попытке изменения данных в таблице, для которой определен триггер. SQL Server выполняет эту процедуру при операциях добавления, обновления и удаления (INSERT , UPDATE , DELETE ) в данной таблице. Поскольку триггер применяется после выполнения операции, он представляет собой последнее слово в модификации. Если триггер вызывает ошибку в запросе, SQL Server отказывается от обновления информации и возвращает приложению, выполняющему это действие, сообщение об ошибке. Если для таблицы определен триггер, то при выполнении соответствующей операции обойти его нельзя.

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

В MS SQL SERVER 2000 появился новый вид триггера INSTEAD OF -триггер. Его принципиальное отличие от обычных (AFTER ) триггеров состоит в том, что он выполняется не после выполнения операции вставки, изменения или удаления, а вместо нее.

Наиболее общее применение триггера поддержка целостности в базах данных.

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

Для создания триггера необходимо быть владельцем таблицы, для которой триггер создается, либо входить в роль db_owner или db_ddladmin , либо же являться администратором SQL-сервера, то есть входить в фиксированную роль сервера sysadmins . При добавлении триггера к таблице изменяется тип доступа, отношение к ней других объектов и т. д.

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

CREATE TRIGGER имя_триггера
ON таблица
{
{FOR | AFTER | INSTEAD OF} { [,] [,] }

AS
{IF UPDATE (столбец_i)
[{AND | OR} UPDATE (столбец_j)]
[… n]
| IF (COLUMNS_UPDATED() {побитовый_оператор} битовая_маска)
{оператор_сравнения} битовая_маска_столбца [… n]
}
инструкции_SQL [… n]
}
}

имя_триггера должно соответствовать стандартным соглашениям об именах объектов SQL Server и быть уникальным в базе данных;
таблица название таблицы, для которой создается триггер;
WITH ENCRYPTION эта опция дает разработчикам возможность запретить пользователям читать текст триггера после его загрузки на сервер. Опять же, отметим, что для того чтобы сделать текст триггера действительно невосстановимым, следует после шифрования удалить соответствующие ему строки из таблицы syscomments ;
FOR DELETE , INSERT , UPDATE ключевые слова, определяющие операцию модификации таблицы, при выполнении которой будет активизирован триггер;
WITH APPEND эта опция необходима, только если установленный уровень совместимости не превышает 65 и используется для создания дополнительных триггеров;
NOT FOR REPLICATION показывает, что триггер не активизируется при модификации таблицы в процессе репликации;
AS ключевое слово, задающее начало определения триггера;
инструкции_SQL в T-SQL триггер может содержать любое количество инструкций SQL, если они заключены в операторные скобки BEGIN ... END ;
IF UPDATE (столбец) для операций добавления и обновления данных можно определить дополнительные условия на конкретный столбец таблицы; при указании нескольких столбцов они разделяются логическими операторами;
IF (COLUMNS_UPDATED()) выше было показано, как можно с помощью конструкции IF UPDATE (столбец) определять, какие столбцы затрагиваются изменениями. Если необходимо проверять, изменяется ли какой-то один конкретный столбец, эта конструкция очень удобна. Однако при построении сложного условия, включающего много столбцов, данная конструкция получается слишком громоздкой. Для таких случаев предназначена конструкция IF (COLUMNS_UPDATED()) . Результатом функции COLUMNS_UPDATED() является набор битов, каждый из которых отвечает за один столбец таблицы; младший бит соответствует первому стобцу, старший последнему. Если в операции, вызвавшей срабатывание триггера, была попытка изменить некоторый столбец, то соответствующий бит будет установлен в 1;
побитовый_оператор побитовый оператор, определяющий операцию выделения нужных битов, полученных с помощью COLUMNS_UPDATED() . Обычно используется оператор & ;
битовая_маска в сочетании с побитовым оператором битовая маска позволяет выделить интересующие разработчика биты, то есть определить, изенялись ли в операции, вызвавшей срабатывание триггера, интересующие его столбцы;
оператор_сравнения и битовая_маска_столбца функция COLUMNS_UPDATED() дает набор битов, соответствующий изменяемым столбцам. С помощью битовой маски и побитового оператора над этим набором битов производится преобразование и получается некий промежуточный результат. С помощью оператора сравнения этот промежуточный результат сравнивается с битовой маской столбца. Если результат сравнения истина, то набор инструкций SQL, составляющий тело триггера, будет выполнен, иначе нет.

Пусть таблица имеет следующую структуру:

CREATE table mytable (a int, b int, c int, d int, e int)

Пять столбцов соответствуют пяти битам, из которых младший соответствует столбцу a , старший столбцу e . Пусть операция, приведшая к срабатыванию триггера, изменяет столбцы a , b и e . Тогда функция columns_updated даст значение 10011. Пусть нас не интересует изменение столбцов b и d , но интересует изменение всех остальных столбцов (a , c и e ), то есть маска будет 10101. Напомним, что на момент написания триггера мы не знаем, какие столбцы затронет та или иная операция изменения или вставки, то есть какой результат даст функция columns_updated . Задав побитовый оператор сравнения во время выполнения, получим 10011 & 10101, что даст в результате 10001, что в десятичном представлении составляет 17. Сравнив это значение с помощью оператора сравнения и битовой маски столбца получим ответ удовлетворяет ли операция изменения/вставки требуемым условиям. Так, например, если бизнес-логика требует, чтобы триггер сработал при изменении все интересующих нас столбцов (a , c , e ), то, естественно, параметры битовая_маска и битовая_маска_столбца должны иметь одинаковые значения, а оператором сравнения должен быть знак равенства. Таким образом, для нашего примера вся конструкция будет иметь вид:

IF (columns_updated & 17) = 17

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

IF (columns_updated & 17) > 0

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

Создавать триггеры можно и с помощью SQL Server Enterprise Manager.

Запустите SQL Server Enterprise Manager.
Щелкните правой кнопкой мыши на таблице, для которой хотите создать триггер, и в контекстном меню выберите команду Task > Manage Triggers . В результате этих действий появится диалоговое окно, в котором можно ввести текст триггера и присвоить ему имя.
После окончания ввода можно выполнить проверку синтаксиса и нажать кнопку OK для сохранения триггера в базе данных.

Ограничения при создании триггеров

Оператор CREATE TRIGGER может применяться только в одной таблице.
Триггер можно создавать только в текущей базе данных, но в нем можно ссылаться на внешние объекты.
В одном операторе создания триггера можно указывать несколько действий, на которые он будет реагировать.
В тексте триггера нельзя использовать следующие инструкции: ALTER DATABASE , ALTER PROCEDURE , ALTER TABLE , CREATE DEFAULT , CREATE PROCEDURE , ALTER TRIGGER , ALTER VIEW , CREATE DATABASE , CREATE RULE , CREATE SCHEMA , CREATE TRIGGER , CREATE VIEW , DISK INIT , DISK RESIZE , DROP DATABASE , DROP DEFAULT , DROP PROCEDURE , DROP RULE , DROP TRIGGER , DROP VIEW , RESOTRE DATABASE , RESTORE LOG , RECONFIGURE , UPDATE STATISTICS .
Любая правильная операция SET работает только в период существования триггера.
Нельзя выполнить триггер, анализируя в столбцах таблиц INSERTED и DELETED состояние большого двоичного объекта (BLOB ), имеющего тип данных text или image , независимо от того, записывается эта процедура в журнал или нет.
Не следует применять инструкции SELECT , возвращающие результирующие наборы из триггера, для приложения-клиента, требующего специального управления результирующими наборами, независимо от того, делается это в хранимой процедуре или нет.
Нельзя создавать INSTEAD OF UPDATE и DELETE триггеры на таблицы, имеющие внешние ключи с установленными опциями каскадного изменения или удаления соответственно.

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

Пример 1. Триггеры вставки и обновления

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

В приведенном ниже примере триггер выполняется всегда, когда в таблицу Sales вставляется строка или выполняется ее модификация. Если дата заказа не находится в пределах первых 15 дней месяца, строка в таблицу не вводится.

CREATE TRIGGER Tri_Ins_Sales
ON Sales
FOR INSERT, UPDATE
AS
/* Объявить необходимые локальные переменные */
DECLARE @nDayOfMonth TINYINT
/* Найти информацию о добавленной записи */
SELECT @nDayOfMonth = DatePart (day, i.ord_date)
FROM Sales s, Inserted i
WHERE s.stor_id = i.stor_id
AND s.ord_num = i.ord_num
AND s.title_id = i.title_id
/* Проверить критерий отказа и в случае необходимости
послать сообщение об ошибке */
IF @nDayOfMonth > 15
BEGIN
/* Примечание: всегда сначала производите откат. Вы можете не знать,
какого рода ошибка обработки произошла, что может вызвать
неоправданно продолжительное время блокировки */
ROLLBACK TRAN
RAISERROR("Выполняются только заказы, поданные в первые
15 дней месяца", 16, 10)
END

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

Обратите внимание, что фрагмент кода обращается к новой таблице, причем в списке таблиц базы данных эта таблица отсутствует. В данном случае таблица Inserted содержит копию каждой строки, которая будет добавлена только в случае успешного завершения транзакции. Эта таблица и ее значения применяются при выполнении любой операции сравнения для проверки правильности транзакции.

Столбцы таблицы Inserted в точности совпадают со столбцами рабочей таблицы. Сравнение можно выполнить по столбцам, как это сделано в данном примере, где для проверки правильности дат продажи сравниваются столбцы таблицы Sales .

Можно также создать триггеры, выполняющие работу только в случае обновления конкретного столбца. Для принятия решения о продолжении обработки в триггере может быть применена инструкция IF UPDATE :

IF UPDATE(au_lname)
AND (@@ROWCOUNT=1)
BEGIN

END

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

Операция UPDATE задействует обе системные таблицы. В таблице Inserted хранятся новые значения, а в таблице Deleted старые. Поэтому при анализе изменений вы можете использовать обе эти таблицы.

Часто бывает необходимо заменить некоторые значения на неопределенные. Это делается элементарной операцией присваивания, например:

NUM_READER = NULL

Пример 2. Триггеры удаления

Триггеры удаления (delete triggers ) обычно применяются в двух случаях: предотвращение удаления строк, которое может вызвать проблемы с целостностью данных, например строки, используемой в качестве внешнего ключа к другим таблицам, и выполнение каскадных операций удаления дочерних (children ) строк главной (master ) строки. Такой триггер можно использовать для удаления всей информации о заказах из главной строки продаж.

Триггеры учитывают общую сумму всех строк, на которые действует запрошенная операция. Таким образом, они должны иметь возможность работать с различными комбинациями информации в таблице и возвращать необходимые данные. Например, при выполнении инструкции DELETE FROM Authors , триггер должен учесть, что инструкция уничтожит все строки из таблицы.

В следующем примере использование переменной @@ROWCOUNT позволяет предотвратить удаление более одной строки. Этот триггер выполняется всегда, когда пользователь пытается удалить строку из таблицы Stores . Если информация касается продаж, то триггер препятствует выполнению этого запроса.

CREATE TRIGGER Tri_Del_Stores
ON Stores
FOR DELETE
AS
/* Проверка количества модифицируемых строк и запрещение удаления более одной строки за один раз */
IF @@ ROWCOUNT > 1
BEGIN
ROLLBACK TRAN
RAISERROR ("За один раз можно удалить только одну строку.", 16, 10)
END
/* Объявление временной переменной для сохранения уничтожаемой информации */
DECLARE @ StorID char (4)
/* Получение значения удаляемой строки */
SELECT @StorID = d.stor_id
FROM Stores s, Deleted d
WHERE s.stor_id *= d.stor_id
IF EXISTS (SELECT *
FROM Sales
WHERE stor_id = @storID)
BEGIN
ROLLBACK TRAN
RAISERROR ("Эта информация не может быть удалена, поскольку имеется соответствующая запись в таблице Sales.", 16, 10)
END

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

В этом примере используются также несколько инструкций управления транзакциями, которые позволяют остановить выполнение операций. Обратите внимание, что фрагмент кода обращается к новой таблице. В списке таблиц базы данных эта таблица отсутствует. В данном случае таблица Deleted содержит копию каждой строки, которая будет добавлена только в случае успешного завершения транзакции. Эта таблица и ее значения применяются при выполнении любого сравнения для проверки правильности транзакции.

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

Пример 3. INSTEAD OF-триггеры

INSTEAD OF-триггеры отличаются от обычных (AFTER ) триггеров тем, что они выполняются не после выполнения операции, приведшей к его срабатыванию, а вместо нее, cо всеми вытекающими последствиями, например, такими как возможность их использования совместно с ограничениями целостности. Системные таблицы Inserted и Deleted используются в них так же, как и в AFTER -триггерах. Тело триггера может дублировать операцию, которая вызвала его срабатывание, но это не обязательное условие. Другими словами, если мы описываем INSTEAD OF DELETE -триггер, то ничто не мешает нам выполнить в нем операцию DELETE , удаляющую все строки, которые должны были быть удалены в соответствии с вызвавшей триггер операцией, но можно этого и не делать.

Приведем пример использования INSTEAD OF -триггера.

Таблица Jobs связана отношением 1:M c таблицей Employees , поэтому невозможно удалить работу, если на нее уже назначены сотрудники. Создадим триггер, который при удалении работы будет проверять, назначены ли на нее сотрудники или нет. Если назначены, то работа не будет удаляться. В связи с тем, что имеется ограничение целостности (DRI ), то работа AFTER -триггера совместно с ним невозможна. То есть можно создать такой триггер:

FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e JOIN Deleted d ON e.job_id=d.job_id)
BEGIN
ROLLBACK TRAN
END

Кстати, отметим, что в отличие от примера 2, этот триггер позволяет удалять сразу несколько строк. Однако такой триггер сможет работать корректно, только если разорвать связь между таблицами Employees и Jobs , чтобы перед выполнением триггера не отрабатывались DRI .

Но можно создать INSTEAD OF -триггер:

CREATE TRIGGER Check_Job ON Jobs
INSTEAD OF DELETE
AS
DELETE FROM Jobs FROM Jobs j JOIN deleted d on d.job_id = j.job_id
WHERE j.job_id NOT IN (SELECT DISTINCT Job_id FROM Employee)

Такой триггер не будет иметь конфликтов с DRI и будет выполняться.

Проверка DRI выполняется сразу при выполнении операции, то есть раньше, чем выполнение AFTER -триггера. При использовании INSTEAD OF -триггера операция по сути не выполняется, а управление передается триггеру, поэтому DRI не будет выполняться.

Как уже было сказано, таблица Inserted содержит добавленные строки, а таблица Deleted удаленные. Нетрудно догадаться, что при выполнении операции изменения будет использована и таблица Inserted , и таблица Deleted . В этом случае старые значеня окажутся в таблице Deleted , а новые в таблице Inserted . Объединяя их по ключевому столбцу (столбцам), нетрудно определить, какие значения были изменены.

3. Использование вложенных триггеров

Триггеры можно встраивать друг в друга. Допускается 32 уровня вложенности. Если операции вложенного триггера нежелательны, SQL Server можно сконфигурировать так, чтобы отключить их.

Примечание: Уровень вложенности триггера можно проверить в любое время, опросив значение, установленное в переменной @@NESTLEVEL . Оно должно находиться а пределах от 0 до 32.

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

Прямую рекурсию можно отключить (и включить) с помощью опции базы данных RECURSIVE_TRIGGERS . Отключить (и включить) косвенную рекурсию, равно как и вложенность триггеров вообще, можно с помощью серверной опции nested triggers . Эта опция определяет возможность вложенности триггеров не для одной конкретной БД, а для всего сервера.

Следует отметить, что INSTEAD OF -триггеры по своей природе не подвержены прямой рекурсии.

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

Предположим, что таблица Table_A включает триггер trigger_A , который выполняется, когда происходит обновление Table_A . При выполнении trigger_a вызывает обновление таблицы Table_B . Эта таблица включает в себя триггер trigger_b , который выполняется, когда обновляется Table_B , и вызывает обновление таблицы Table_A . Таким образом, если пользователь обновляет любую из этих двух таблиц, два триггера продолжают бесконечно вызывать выполнение друг друга. При возникновении такой ситуации SQL Server закрывает или отменяет выполнение триггера.

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

/* Первый триггер уничтожает строки в таблице Stores,
если уничтожаются строки таблицы Sales */
CREATE TRIGGER Tri_Del_Sales
ON Sales
FOR DELETE
AS

PRINT "Выполняется триггер удаления для таблицы Sales..."
/* Объявление временной переменной для хранения удаляемой информации */
DECLARE @sStorID char(4),@sMsg varchar(40)
/* Получение значения ID удаляемой строки */

FROM Deleted

/* Удаление строки */
SELECT @sMsg = "Магазин " + @sStorID + " удален"
PRINT @sMsg
DELETE FROM Stores
WHERE stor_id = @sStorID
PRINT "Конец выполнения триггера для таблицы Sales"
GO
/* Второй триггер уничтожает строки одной таблицы,
если уничтожаются строки другой */

ON Stores
FOR DELETE
AS
/* Объявление выполняемого триггера */
PRINT "Выполняется триггер удаления для таблицы Stores..."
/* Объявление временной переменной для хранения информации,
уничтожаемой из таблицы */
DECLARE @sStorID char(4), @sMsg varchar (200)
/* Получение уничтожаемого значения */
SELECT TOP 1 @sStorID = stor_id
FROM Deleted
/* Deleted - это вспомогательная таблица, которую SQL Server
использует для хранения уничтоженных записей */
IF @@ROWCOUNT = 0
BEGIN
PRINT "В таблице Stores нет соответствующих строк"
RETURN
END
/* Удаление записи */
SELECT @sMsg = "Удаление скидок, относящихся к магазину " + @sStorID
PRINT @sMsg
DELETE Discounts
WHERE Stor_id = @sStorID
PRINT "Количество удаленных скидок: " + CONVERT(VARCHAR(4), @@ROWCOUNT)
PRINT "Конец выполнения триггера для таблицы Stores"

Если инструкция DELETE выполняется в таблице Sales , как показано в следующем примере, активизируется триггер, что в свою очередь вызывает выполнение триггера таблицы Stores .

Выполним:

DELETE FROM Sales WHERE stor_id = "8042"

Результат:

Выполняется триггер удаления для таблицы Sales...
Магазин 8042 удален
Выполняется триггер удаления для таблицы Stores...
Удаление скидок, относящихся к магазину 8042
(1 row(s) affected)
Количество удаленных скидок: 1
Конец выполнения триггера для таблицы Stores
(1 row(s) affected)
(4 row(s) affected)
Конец выполнения триггера для таблицы Sales

Обратите внимание на порядок выдаваемых сообщений. Сначала запускается триггер для таблицы Sales . Он удаляет строку из таблицы Stores , запуская таким образом для нее триггер. При этом фактически ни из таблицы Sales , ни из таблицы Stroes удаления еще не произошло (удаление в процессе) об этом свидетельствует отсутствие автоматического сообщения сервера (N row(s) affected) , которое появляется при удалении из любой таблицы и показывает, сколько строк было удалено.

После запуска триггер на таблицу Stores удаляет связанные строки из таблицы скидок (Discounts ), о чем выдается сообщение (1 row(s) affected) . Затем он выводит соответствующие сообщения и заканчивает свою работу. Как только он закончил свою работу, происходит удаление строки из таблицы Stores , удаление которой и вызвало его работу. Далее, поскольку эта строка удалена, происходит возврат к работе триггера на таблицу Stores . Этот триггер выдает свое последнее сообщение об окончании работы и завершается. Как только он завершился, выводится сообщение (1 row(s) affected) , свидетельствующее об удалении строки из таблицы Stores . И уже только после этого окончательно удаляются строки из таблицы Sales .

Примечание: Триггеры и механизм декларативной ссылочной целостности обычно не могут работать вместе. Например, в предыдущем примере показано, что перед выполнением инструкции DELETE необходимо сначала удалить условие на значение FOREIGN KEY в таблице Discounts . Везде, где это возможно, следует применять либо триггер, либо условие ссылочной целостности. Однако, как уже говорилось, в MS SQL Server 2000 появились INSTEAD OF -триггеры. Их можно использовать совместно с механизмами декларативной целостности, нельзя только использовать при этом каскадные операции в связях на ту же операцию, на которую создан INSTEAD OF -триггер. Например, если создан INSTEAD OF DELETE -триггер, то нельзя в связях, в которых эта таблица является подчиненной, использовать конструкцию ON DELETE CASCADE .

Пример 2

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

CREATE TRIGGER Del_Empl_Tr ON Employee
FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e
JOIN Deleted d on e.lname = d.lname OR e.Fname = d.fname)
DELETE FROM Employee
FROM Employee e JOIN Deleted d on e.lname = d.lname OR e.Fname = d.fname

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

Если теперь выполнить инструкцию:

DELETE FROM Employee WHERE Fname = "Иван" AND Lname = "Иванов"

то триггер, который запустится при удалении, кроме Ивана Иванова удалит также Ивана Сергеева и Михаила Иванова. После этого удаления триггер будет запущен опять и будет искать всех Иванов и Михаилов, а также Ивановых и Сергеевых. В результате его работы будут удален Петр Сергеев. Затем этот же триггер удалит Петра Васильева. После этого триггер будет искать Петров и Васильевых, но поскольку их больше нет в таблице, то на этом выполнение закончится.

Заметьте, что тут обязательно надо делать проверку IF EXISTS . Если ее не сделать, то когда дело дойдет до удаления Петра Васильева, будет выполнена инструкция DELETE и, хотя она фактически никого не удалит, вновь вызванный триггер опять вызовет самого себя (опять никого фактически не удаляя) и т. д., до превышения максимального уровня вложенности 32. После достижения уровня вложенности 32 произойдет ошибка и все действия будут отменены.

Пример 3. Косвенная рекурсия

Изменим пример 1 таким образом, чтобы, если удаляется строка из таблицы Sales , то удалялся бы и магазин, в котором была сделана удаляемая продажа. Поскольку отношение между этими таблицами 1:M, то в удаляемом магазине может быть множество продаж, а не только та, которую мы пытаемся удалять. Поэтому цепочка должна быть следующая: удаляем продажу → удаляется магазин, в котором она была сделана, → удаляются все остальные продажи, сделанные в этом магазине, → удаляются все скидки, привязанные к этому магазину. Кроме того, реализуем эти триггеры в виде INSTEAD OF -триггеров, чтобы не было необходимости разрывать связи между таблицами.

CREATE TRIGGER Tri_Del_Sales
ON Sales
INSTEAD OF DELETE
AS
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.ord_num = s.ord_num
IF EXISTS (SELECT * FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id)
DELETE FROM Stores FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id
GO

CREATE TRIGGER Tri_Del_Stores
ON Stores
INSTEAD OF DELETE
AS
DELETE FROM Discounts FROM Discounts di JOIN Deleted de on di.stor_id=de.stor_id
IF EXISTS(SELECT * FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id)
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id
DELETE FROM Stores FROM Stores s JOIN Deleted d on d.stor_id = s.stor_id

Для проверки можно выполнить команду:

DELETE FROM Sales WHERE ord_num = "P723"

В результате из таблицы Sales будет удалена не только строка с кодом заказа "P723", но и три другие строки, относящиеся к тому же магазину (код 8042). Также будет удален сам магазин 8042 и относящаяся к нему скидка.

В приведенном примере, кроме всего прочего, удалены все выводы сообщений и изменены вызовы операторов DELETE поскольку выводов сообщений нет, то нет и необходимости формировать значение локальной переменной @sStroID . Использование этой переменной в операторе DELETE несколько ограничивало применимость триггеров. Так, триггеры в примере 2 были рассчитаны на то, что будут удаляться записи только для одного магазина, и при удалении записей, относящихся сразу к нескольким магазинам, работали некорректно. Теперь же такого ограничения нет, поскольку удаляются все записи, связанные с записями в таблице Deleted (то есть со всеми фактически удаляемыми строками).

Можно задать вопрос: зачем использовать рекурсию? Не проще ли было бы при удалении из таблицы Sales удалять в триггере на нее все записи из самой себя, относящиеся к тому же магазину, что и удаляемая строка продажи, после этого удалять строку из таблицы Stores , а в триггере на таблицу Stores удалять связанные записи только из таблицы Discounts ? Да, так можно сделать, но только в том случае, если мы всегда будем давать команду удаления именно из таблицы Sales (как это было сделано выше при проведении проверки). Однако мы можем дать команду удаления и из таблицы Stores , например так:

DELETE FROM stores WHERE stor_id = 8042

И в этом случае мы также хотим, чтобы команда отработала корректно. Если же в триггере на таблицу Stores , как было предложено в вопросе, не будет включено удаление из Sales , то если для удаляемого магазина есть продажи, то такое удаление приведет к ошибке. Наш же пример позволяет решить эту проблему. Ну а если в триггер на Stores включена команда удаления из Sales , то в триггере на Sales нет необходимости включать удаление продаж в том же магазине, что и удаляемый, поскольку это будет автоматически выполнено через рекурсию.

Замечание 1: Чтобы уже созданные в предыдущих примерах триггеры не мешались, надо удалить их с помощью инструкции DROP TRIGGER имя_триггера .

Замечание 2: Еще раз обращаем ваше внимание, что для того чтобы рекурсия работала, должны быть выставлены соответствующие опции базы данных и сервера.

Пример 4

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

CREATE TRIGGER trig_del_l ON Authors FOR DELETE AS
PRINT "Триггер удаления №1"
GO

CREATE TRIGGER trig_del_2 ON Authors FOR DELETE AS
PRINT "Триггер удаления №2"
GO

CREATE TRIGGER trig_upd_l ON Authors FOR UPDATE AS
PRINT "Триггер обновления №1"
GO

CREATE TRIGGER trig_upd_3 ON Authors FOR UPDATE AS
PRINT "Триггер обновления №3" "
GO

CREATE TRIGGER trig_upd_2 ON Authors FOR UPDATE AS
PRINT "Триггер обновления №2"
GO

А теперь попробуем изменить какую-либо запись в таблице:

UPDATE Authors
SET au_fname = "Юрий" WHERE au_lname = "Тихомиров";

Cработают все три триггера обновления:

Триггер обновления №1

Триггер обновления №3

Триггер обновления №2

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

Триггер обновления №1

Триггер обновления №2

Триггер обновления №3

Множественные триггеры достаточно активно используются при репликации.

4. Отображение информации о триггере и изменение триггера

Для выяснения назначения триггера таблицы необходимо отобразить информацию, описывающую любой триггер, которым владеет таблица. Существует несколько путей получения информации о триггере конкретной таблицы. Одним из них является SQL Server Enterprise Manager, другим системные процедуры sp_help и sp_depends . Для того чтобы посмотреть текст триггера через Enterprise Manager, выполните следующие действия:

В Enterprise Manager выберите сервер и базу данных, с которой вы хотите работать.
Откройте таблицу в режиме проектирования командой Design Table и в ее окне нажмите кнопку Triggers на панели инструментов.
Появится диалоговое окно создания триггера, где можно посмотреть текст любого из установленных триггеров.

Системные хранимые процедуры sp_help и sp_depends уже были описаны в теме «Хранимые процедуры».

Для того чтобы изменить функциональность триггера, можно либо удалить его и создать новый с соответствующими изменениями, либо изменить уже существующий. Для того чтобы изменить существующий триггер в T-SQL существует команда ALTER TRIGGER . Ее синтаксис аналогичен синтаксису команды CREATE TRIGGER , за исключением ключевого слова ALTER вместо CREATE .

Можно также изменить триггер с помощью Enterprise Manager. Для этого после входа в Enterprise Manager надо просто внести изменения и применить их.

5. Удаление триггеров

Иногда нужно удалить триггеры из таблицы или таблиц. Например, при перемещении приложения в рабочую среду может потребоваться удаление триггеров, обеспечивавших высокое качество обработки, но сильно уменьшавших производительность. Можно просто удалить триггеры для замены их на более новую версию. Для удаления триггера применяется инструкция DROP TRIGGER :

DROP TRIGGER [владелец.]имя_ триггера [, n]

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

Пример удаления триггера Tri_Dei_Autnors :

DROP TRIGGER Tri_Del_Authors

6. Приостановка и возобновление работы триггеров

Часто бывает необходимо отключить на некоторое время работу триггера без его фактического удаления. Этого можно достигнуть используя конструкцию ALTER TABLE <имя_таблицы> DISABLE TRIGGER <имя триггера> для отключения триггера и ALTER TABLE <имя_таблицы> ENABLE TRIGGER <имя триггера> для возобновления его работы.

Задания для самостоятельной работы

Перед началом выполнения заданий напомним, что триггеры это системные хранимые процедуры, которые связаны с конкретной таблицей. Для вызова редактора триггеров необходимо выделить таблицу, по правой кнопке контекстного меню перейти в раздел Все задачи > Manage triggers , и вы попадаете в редактор триггеров (рис. 1).

Рис. 1. Начальное состояние редактора триггеров при создании нового триггера

Задание 1. Разработать триггер, который удалял бы запись о книге в том случае, если удаляется последний экземпляр данной книги. Для какой таблицы вы будете писать этот триггер? При написании триггера помните, что с таблицей «Книги» у нас связаны таблицы «Авторы» и «Системный каталог». Однако они связаны отношением «многие-ко-многим», для чего используются связующие таблицы. Удалить данные о книге нельзя, если на нее есть ссылки в этих связующих таблицах. Предусмотрите предварительное удаление данных из связующих таблиц. Проверьте работу данного триггера.

Задание 2. Разработать триггер, который не позволял бы удалить экземпляр книги, если этот экземпляр в данный момент находится на руках у читателя. Для отмены команды удаления применить команду отката транзакций ROLLBACK .

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

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

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

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

Задание 4. Разработать триггер, который добавлял бы один экземпляр при вводе новой книги. Действительно, мы определили, что книги у нас в каталоге присутствуют только в том случае, если они есть в нашей библиотеке, поэтому при вводе новой книги в таблицу «Экземпляр» должен добавляться один экземпляр данной книги.

Задание 5. Разработать триггер типа INSTEAD OF для таблицы «Читатели». Данный триггер должен проверять, есть ли информация хотя бы об одном из телефонов для оперативной связи с читателем, и если такой информации нет, то не вводить данные о читателе.

Задание 6. Разработать триггер, который при изменении значения поля, символизирующего присутствие экземпляра книги в библиотеке, например YES_NO , с "1" на "0" автоматически заменял бы значения в полях «Дата выдачи», «Дата возврата» и «Номер читательского билета» на неопределенное.

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

Задание 8. Разработать триггер, который при удалении экземпляра книги проверял бы, сколько экземпляров данной книги осталось в библиотеке, и если остался только один экземпляр, то повышал бы стоимость данной книги на 15 % как редкой и ценной.

Версия для печати

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

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

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

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

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

Уже много статей в интернете есть про sql триггеры, но добавлю еще одну с адекватными примерами, что бы закрепить материал для тех, кто «в теме» и что бы лучше понять материал тем, кто только начал постигать «дзен sql». Заодно и создам дискуссию по теме.

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

Из работы над ними вынесли один ценный опыт - следить за приоритетами и статистикой. Что это значит? Все просто: если у Вас блог и у него 2-3-4-10012 млн посетителей в сутки, а статьи пишутся всего 1-2-3-3435 раз в сутки (на порядок меньше чем число просмотров), то скорость сохранения статьи (и сложность этого) относительно скорости показа статьи может быть пропорционально меньше. Чем больше показываем, тем критичен именно показ, а не сохранение статьи/страницы/таблицы. Что не означает, что и расслабляться можно. Сохранение статьи за 3-5-10 секунд в блоге - это в рамках адекватности, но генерация страницы за срок более 2 секунды (+ пока скрипты и стили с картинками подгрузятся) - это на грани «какой тормознутый сайт, почитаю что-то иное», а еще хуже «пойду куплю в другом месте».

Если мы возьмем среднестатистический сайт с голосовалкой/кармой, комментариями, счетчиком показа страницы и т.п., то многим разработчикам сразу в голову приходят конструкции вроде SELECT count(*) FROM comment WHERE comment.page=page_id. Ну подумаешь на каждую статью посчитать сумму рейтинга, сумму комментариев. А, у нас на главной по 10 статей из каждого раздела. При посещаемости в 10 человек в секунду, на среднем VPS, можно себе позволить по 60-100 запросов к sql на страницу (привет, битрикс).

Но к черту лирику (достал уже, наверное). Голые данные:

Таблица blog

CREATE TABLE IF NOT EXISTS `blog` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `text` text NOT NULL, `creation` datetime NOT NULL, `modification` datetime NOT NULL, `img` varchar(128) NOT NULL DEFAULT "default.png", `status` tinyint(4) NOT NULL DEFAULT "2", `user_id` int(11) NOT NULL, `rate` int(11) NOT NULL, `relax_type` tinyint(4) NOT NULL, `timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint(1) NOT NULL DEFAULT "0", `views` int(11) NOT NULL DEFAULT "0", `comment` int(11) NOT NULL, `url` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`), KEY `country_id` (`country_id`), KEY `user_id` (`user_id`), KEY `status` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

Таблица comments

CREATE TABLE IF NOT EXISTS `comments` (`owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", PRIMARY KEY (`id`), KEY `owner_name` (`owner_name`,`owner_id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

Как видим, в таблице блога у каждой статьи есть счетчик комментариев (поле comment).
Обычная практика:
1. Добавили комментарий - увеличили счетчик для блога
2. Удалили/скрыли комментарий - уменьшили счетчик.
Делать это в коде удобно и привычно, но есть более удобный инструмент - триггеры.

И так, у нас есть 2 события (на самом деле 3): создание комментария и его удаление (третье событие - это изменение его статуса («удаление», бан и т. п.).
Рассмотрим только создание и удаление, а изменение статуса пусть будет домашним заданием.

В примере есть одна особенность: комментарии могут быть к нескольким типам статей.

Создание комментария:

CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments` FOR EACH ROW BEGIN // у пользователя в личном кабинете посчитаем сколько он комментариев написал UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // определяем к чему относится комментарий и сразу увеличиваем счетчик в данных таблицах CASE NEW.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog`.id = NEW.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; END CASE; // тут мы облегчаем себе работу с лентами новостей // url статьи сразу пишем, что бы ПОТОМ не делать выборок лишних CASE NEW.`owner_name` WHEN "Blog" THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`.id= NEW.`owner_id`); WHEN "Article" THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET userurl = ``; END CASE; // название статьи сразу пишем, что бы ПОТОМ не делать выборку CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`); WHEN "Article" THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET usertitle = ` `; END CASE; INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle); END

Аналогично и удаление комментария:

CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments` FOR EACH ROW BEGIN UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; END CASE; END

И так, что получили:
1. При вставке комментария у нас автоматически средствами sql сервера посчиталась сумма комментариев у конкретного объекта комментирования (статья, страница, заметка)
2. Мы сформировали ленту новостей (привет всем соцсетям и т. п.)
3. При удалении комментария у нас происходит вычет всех данных.
4. Мы не использовали средства фреймворка.
5. Выборка всех нужных данных происходит быстро (всего 1 запрос при показе страницы, за исключением прочих «левых» данных на ней.)

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

Добавлен триггер:

CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

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

CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

При изменении данных - обновим поисковый индекс тоже.

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

UPD: Холивар посвященный целесообразности усложнения структуры БД объявляется открытым.

SQL ,

Разработка веб-сайтов