Уже много статей в интернете есть про 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: Холивар посвященный целесообразности усложнения структуры БД объявляется открытым.

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

Тема: Процедуры и триггеры

(на примере MS SQL Server)

составитель: Л. В. Щёголева

ПетрГУ, кафедра прикладной математики и кибернетики

Введение.........................................................................................................
	Описание структуры базы данных............................................................
	Понятие процедуры....................................................................................
	Команды работы с процедурами...............................................................
	Понятие триггера........................................................................................
	Команды работы с триггерами..................................................................
	Примеры реализации триггеров..............................................................
	Пример 1...............................................................................................
	Пример 2...............................................................................................
	Пример 3...............................................................................................
	Пример 4...............................................................................................
	Пример 5...............................................................................................

ПетрГУ, кафедра прикладной математики и кибернетики

Введение

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

с описанием их работы.

Все команды написаны в синтаксисе MS SQL Server.

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

разделе 1.

ПетрГУ, кафедра прикладной математики и кибернетики

1 Описание структуры базы данных

Таблица tblFaculty содержит информацию о факультетах университета.

Наименование			Описание
атрибута
			Идентификатор факультета
			Название факультета
			ФИО декана
			Номер кабинета деканата
			Телефон деканата
			Количество студентов факультета

Таблица tblStudent содержит информацию о студентах университета в одном учебном году.

Наименование атрибута			Описание
			Идентификатор студента
			ФИО студента
			Стипендия

Таблица tblGroup содержит информацию о студенческих группах университета в одном учебном году.

Наименование атрибута			Описание
			Идентификатор группы
			Староста
			Номер группы
			Факультет

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

	Наименование атрибута			Описание
				Идентификатор предмета
				Название предмета
				Количество часов лекций
				Количество часов практик
				Факультет
	Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

ПетрГУ, кафедра прикладной математики и кибернетики

Таблица tblRoom содержит информацию об аудиториях университета.

Таблица tblSchedule содержит информацию о расписании занятий студенческих групп.

Наименование атрибута			Описание
			Идентификатор
			Аудитория
			День недели
			ФИО преподавателя

ПетрГУ, кафедра прикладной математики и кибернетики

2 Понятие процедуры

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

Преимущества использования процедур:

– централизованный контроль доступа к данным;

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

– снижает трафик в сети в системах «клиент-сервер» за счет передачи только имени процедуры и ее параметров вместо обмена данными, а процедура выполняется на сервере;

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

– большая безопасность данных, пользователь может иметь право вызывать

процедуру, но не управлять данными, которые вызываются этой процедурой; Недостаток: отсутствие стандартов в реализации процедур.

ПетрГУ, кафедра прикладной математики и кибернетики

3 Команды работы с процедурами в MS SQL Server

Создание процедуры

CREATE PROCEDURE <имя процедуры>

[@<имя параметра> <тип данных> , ...]

BEGIN

<операторы>

Имена всех переменных в MS SQL Server должны начинаться с символа

Вызов процедуры

EXECUTE <имя процедуры> [{@<имя переменной> | <значение параметра>}, ...]

Удаление процедуры

DROP PROCEDURE <имя процедуры>

Процедура подсчитывает количество студентов, обучающихся на факультете, идентификатор которого является входным параметром процедуры @id, и возвращает это значение в параметре @total_sum.

Create Procedure prStudentsOfFaculty @id int, @total_sum int output AS

Set @total_sum = 0

Set @total_sum = (Select count(*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = @id)) End

ПетрГУ, кафедра прикладной математики и кибернетики

4 Понятие триггера

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

Особенности реализации триггеров в MS SQL Server

В MS SQL Server:

– триггер может быть вызван либо после выполнения операции, либо вместо выполнения операции;

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

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

o таблица Inserted – содержит измененные или добавленные записи таблицы;

o таблица Deleted – содержит записи до выполнения изменений или удаленные записи таблицы;

– в теле триггера, определенного для операции Insert, доступна только таблица

– в теле триггера, определенного для операции Delete, доступна только таблица

– в теле триггера, определенного для операции Update, доступны обе таблицы

Inserted и Deleted;

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

ПетрГУ, кафедра прикладной математики и кибернетики

5 Команды работы с триггерами

Создание

CREATE TRIGGER <имя триггера> ON <имя таблицы>

{ FOR | AFTER | INSTEAD OF }

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] AS

DECLARE @<имя переменной> <тип данных>, ...

BEGIN <операторы>

Удаление

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

ПетрГУ, кафедра прикладной математики и кибернетики

6 Примеры реализации триггеров

Ограничение предметной области: стипендия студента не может быть увеличена более чем на 5% от предыдущей стипендии.

CREATE TRIGGER tgrStudentGrantUpdate

ON tblStudent AFTER UPDATE

DECLARE @Grant_old float, @Grant_new float, @Id int;

Select @Grant_old = Grant from Deleted

Select @Grant_new = Grant, @Id = StudentId from Inserted

IF (@Grant_new - @Grant_old > 0.05 * @Grant_old)

UPDATE tblStudent SET Grant = 1.05 * @Grant_old

WHERE StudentId = @Id

Триггер tgrStudentGrantUpdate создан для таблицы tblStudent. Триггер будет срабатывать после выполнения операции изменения данных.

В триггере определены три локальные переменные: @Grant_old (вещественного типа) для хранения старой стипендии студента, @Grant_new (вещественного типа) для хранения новой стипендии студента, @Id (целого типа) для хранения идентификатора студента.

При вызове триггера СУБД создает две таблицы: Deleted, содержащую измененные записи до их изменения, и Inserted, содержащую измененные записи после их изменения.

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

Далее, в теле триггера проверяется условие о величине изменения стипендии. Если стипендия изменилась более чем на 5%, то триггер вносит поправку в данные – увеличивает стипендию только на 5% по сравнению со предыдущим значением стипендии студента. Это действие выполняется посредством вызова операции Update в таблице tblStudent для соответствующего студента.

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

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

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

Триггер – это откомпилированная 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 ] AS sql_оператор[...n] } | { {FOR | AFTER | INSTEAD OF } { [,] } [ WITH APPEND] [ NOT FOR REPLICATION] AS { 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]

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

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

Команда вставки записи в таблицу Сделка может быть, например, такой:

INSERT INTO Сделка VALUES (3,1,-299,"01/08/2002")

Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице Склад величина остатка товара оказалась меньше продаваемого количества товара с введенным кодом (в примере код товара=3 ). Во вставляемой записи количество товара указывается со знаком "+", если товар поставляется, и со знаком "-", если он продается. Представленный триггер настроен на обработку только одной добавляемой записи.

CREATE TRIGGER Триггер_ins ON Сделка FOR INSERT AS IF @@ROWCOUNT=1 BEGIN IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Пример 14.1. Использование триггера для реализации ограничений на значение.

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

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

INSERT INTO Сделка VALUES (3,1,200,"01/08/2002")

поставляется товар с кодом 3 от клиента с кодом 1 в количестве 200 единиц.

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

ALTER TRIGGER Триггер_ins ON Сделка FOR INSERT AS DECLARE @x INT, @y INT IF @@ROWCOUNT=1 --в таблицу Сделка добавляется запись --о поставке товара BEGIN --количество проданного товара должно быть не --меньше, чем его остаток из таблицы Склад IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y END END Пример 14.2. Использования триггера для сбора статистических данных.

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

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

CREATE TRIGGER Триггер_del ON Сделка FOR DELETE AS IF @@ROWCOUNT=1 -- удалена одна запись BEGIN DECLARE @y INT,@x INT --определяется код и количество товара из --удаленной из таблицы Склад записи SELECT @y=КодТовара, @x=Количество FROM deleted --в таблице Склад корректируется количество --товара UPDATE Склад SET Остаток=Остаток-@x WHERE КодТовара=@y END Пример 14.3. Триггер для обработки операции удаления записи из таблицы

Пример 14.4. Создать триггер для обработки операции изменения записи в таблице Сделка , например, такой командой:

во всех сделках с товаром, имеющим код, равный 3, уменьшить количество товара на 10 единиц.

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

CREATE TRIGGER Триггер_upd ON Сделка FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT -- курсор с новыми значениями DECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted -- курсор со старыми значениями DECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 -- перемещаемся параллельно по обоим курсорам FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN --для старого кода товара уменьшается его --количество на складе UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old --для нового кода товара, если такого товара --еще нет на складе, вводится новая запись IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE --иначе для нового кода товара увеличивается --его количество на складе UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.4. триггер для обработки операции изменения записи в таблице

В рассмотренном триггере отсутствует сравнение количества товара при изменении записи о сделке с его остатком на складе.

Пример 14.5. Исправим этот недостаток. Для генерирования сообщения об ошибке используем в теле триггера команду MS SQL Server RAISERROR , аргументами которой являются текст сообщения, уровень серьезности и статус ошибки.

ALTER TRIGGER Триггер_upd ON Сделка FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE кодтовара=@x IF @o<-@y BEGIN RAISERROR("откат",16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.5. Исправленный вариант триггера для обработки операции изменения записи в таблице

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

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

ALTER TRIGGER Триггер_upd ON Сделка INSTEAD OF UPDATE AS DECLARE @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE КодТовара=@x IF @o>=-@y BEGIN RAISERROR("изменение",16,10) UPDATE Сделка SET количество=@y, КодТовара=@x WHERE КодСделки=@k UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара, Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x END ELSE RAISERROR("запись не изменена",16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.6. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

База данных Oracle позволяет определять триггеры, срабатывающие при выполнении команды DDL - проще говоря, любых команд SQL, создающих или модифицирующих объекты базы данных (таблицы, индексы и т. д.). Несколько примеров команд DDL: CREATE TABLE , ALTER INDEX , DROP TRIGGER - каждая из них создает, изменяет или удаляет объект базы данных. Синтаксис создания триггеров команд DDL почти не отличается от синтаксиса триггеров PL/SQL для DML . Они различаются лишь перечнем инициирующих событий и тем, что триггеры DDL не связываются с конкретными таблицами.

Весьма специфический триггер INSTEAD OF CREATE TABLE , описанный в конце раздела, позволяет манипулировать со стандартным поведением события CREATE TABLE . Не все аспекты синтаксиса и использования триггеров, описанные далее, применимы к этому типу триггеров.

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

Команда создания (или замены) триггера DDL имеет следующий синтаксис :

1 CREATE TRIGGER имя_триггера 2 {BEFORE | AFTER } { событие_DDL} ON {DATABASE | SCHEMA} 3 4 DECLARE 5 Объявления переменных 6 BEGIN 7 ...код триггера... 8 END;

Элементы триггера описаны в следующей таблице.

Строки	Описание
1	Создание триггера с заданным именем. Если триггер существует, а секция REPLACE отсутствует, попытка создания триггера приведет к ошибке ORA-4081
2	Строка определяет, должен ли триггер запускаться до или после наступления заданного со­бытия DDL , а также должен ли он запускаться для всех операций в базе данных или только в текущей схеме. Секция INSTEAD OF поддерживается только в Oracle9i Release 1 и последу­ющих версиях
3	Необязательное условие WHEN , позволяющее задать логику для предотвращения ненужно­го выполнения триггера
4-7	Образец тела триггера

Приведем пример простого триггера, оповещающего о создании любых объектов:

SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE("I believe you have created something!"); 5 END; 6 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 (col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /* Очистка буфера DBMS_OUTPUT */ SQL> BEGIN NULL; END; 2 / I believe you have created something! I believe you have created something! I believe you have created something! PL/SQL procedure successfully completed.

Текст, возвращаемый встроенным пакетом DBMS_OUTPUT , не будет выводиться триггером DDL до успешного выполнения блока PL/SQL, даже если этот блок не выполняет никаких действий.

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

SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 -- Использование атрибутов события для получения более полной информации 5 DBMS_OUTPUT.PUT_LINE("I believe you have created a " || 6 ORA_DICT_OBJ_TYPE || " called " || 7 ORA_DICT_OBJ_NAME); 8 END; 9 / Trigger created. SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE a_table 2 col1 NUMBER); Table created. SQL> CREATE INDEX an_index ON a_table(col1); Index created. SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS 2 BEGIN 3 RETURN(TRUE); 4 END; 5 / Function created. SQL> /*-- Очистка буфера DBMS_OUTPUT */ SQL> BEGIN NULL; END; 2 / I believe you have created a TABLE called A_TABLE I believe you have created a INDEX called AN_INDEX I believe you have created a FUNCTION called A_FUNCTION PL/SQL procedure successfully completed.

В этих примерах представлены два важнейших аспекта триггеров DDL: события, с ко­торыми они связываются, и атрибуты событий, которые в них доступны.

События триггеров

Список событий, которые можно связать с триггерами DDL, приведен в табл. 1. Лю­бой триггер DDL может вызываться как до (BEFORE), так и после (after) наступления указанного события.

Таблица 1. События DLL

Событие DDL	описание
ALTER	Создание объекта базы данных командой SQL ALTER
ANALYZE	Анализ состояния объекта базы данных командой SQL
ASSOCIATE STATISTICS	Связывание статистики с объектом базы данных
AUDIT	Включение аудита базы данных командой SQL AUDIT
COMMENT	Создание комментария для объекта базы данных
CREATE	Создание объекта базы данных командой SQL CREATE
DDL	Любое из перечисленных событий
DISASSOCIATE STATISTICS	Удаление статистики,связанной с объектом баз данных
DROP	Удаление объекта базы данных командой SQL DROP
GRANT	Назначение прав командой SQL GRANT
NOAUDIT	Отключение аудита базы данных командой SQL NOAUDIT
RENAME	Переименование объекта базы данных командой SQL RENAME
REVOKE	Отмена прав командой SQL REVOKE
TRUNCATE	Очистка таблицы командой SQL TRUNCATE

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

Атрибутные функции

Oracle предоставляет набор функций (определенных в пакете DBMS_STANDARD) для получения информации о причине запуска триггера DDL и других связанных с ним параметрах (например, имя удаляемой таблицы). Перечень этих атрибутных функций приведен в табл. 2, а примеры их использования - в следующих разделах.

Таблица 2. События триггеров DDL и атрибутные функции

Функция	Что возвращает
ORA_CLIENT_IP_ADDRESS	IP-адрес клиента
ORA_DATABASE_NAME	Имя базы данных
ORA_DES_ENCRYPTED_PASSWORD	Пароль текущего пользователя, зашифрованный с использованием алгоритма DES
ORA_DICT_OBJ_NAME	Имя объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера
ORA_DICT_OBJ_NAME_LIST	Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_DICT_OBJ_OWNER	Имя владельца объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера
ORA_DICT_OBJ_OWNER_LIST	Количество обработанных командой объектов. В параметре NAME_LIST возвращается полный список имен этих объектов в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_DICT_OBJ_TYPE	Тип объекта базы данных, связанного с командой DDL, вызвавшей запуск триггера (например, TABLE или INDEX)
ORA_GRANTEE	Количество пользователей, получивших привилегии. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_INSTANCE_NUM	Номер экземпляра базы данных
ORA_IS_ALTER_COLUMN	TRUE , если изменяется столбец, заданный параметром COLUMN_NAME ; FALSE в противном случае
ORA_IS_CREATING_NESTED_TABLE	TRUE , если создается вложенная таблица; FALSE в противном случае
ORA_IS_DROP_COLUMN	TRUE , если удаляется столбец, заданный параметром COLUMN _ NAME; FALSE в противном случае
ORA_LOGIN_USER	Имя пользователя Oracle , для которого запущен триггер
ORA_PARTITION_POS	Позиция команды SQL для корректной вставки секции PARTITION
ORA_PRIVILEGE_LIST	Количество предоставленных или отмененных привилегий. В аргументе PRIVILEGE_LIST содержится полный список привилегий в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_REVOKEE	Количество пользователей, лишенных привилегий. В аргументе USER_LIST содержится полный список этих пользователей в виде коллекции типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_SQL_TXT	Количество строк в команде SQL, которая вызвала запуск триггера. Аргумент SQL_TXT возвращает каждую строку команды в виде аргумента типа DBMS_STANDARD.ORA_NAME_LIST_T
ORA_SYSEVENT	Тип события, вызвавшего запуск триггера DDL (например, CREATE , DROP или ALTER)
ORA_WITH_GRANT_OPTION	TRUE , если привилегии предоставлены конструкцией GRANT ; FALSE в противном случае

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

• Тип данных 0RA_NAME_LIST_T определен в пакете DBMS_STANDARD так:

TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

Иными словами, это вложенная таблица строк, каждая из которых может содержать до 64 символов.

• События триггеров DDL и атрибутные функции также определены в пакете DBMS_ STANDARD . Для каждой из функций этого пакета Oracle создает независимую функцию, добавляя к ее имени префикс 0RA_, для чего при создании базы данных выполняется сценарий $ORACLE_HOME/rdbms/dbmstrig.sql. В некоторых версиях Oracle этот сценарий содержит ошибки, из-за которых независимые функции не видны или не выполняют­ся. Если вы сомневаетесь в правильности определения этих элементов, попросите ад­министратора базы данных проверить сценарий и внести необходимые исправления.
• Представление словаря данных USER_S0URCE не обновляется до срабатывания обо­их триггеров DDL, BEFORE и AFTER . Иначе говоря, вы не сможете использовать эти функции для реализации системы контроля версий «до и после», построенной ис­ключительно в границах базы данных и основанной на триггерах.

Применение событий и атрибутов

Возможности триггеров DDL лучше всего продемонстрировать на примерах. Для на­чала рассмотрим триггер, который блокирует создание любых объектов базы данных:

TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN RAISE_APPLICATI0N_ERR0R (-20000, "ERROR: Objects cannot be created in the production database."); END;

После его создания в базе данных не удастся создать ни один объект:

SQL> CREATE TABLE demo (coll NUMBER); * ERROR at line 1: ORA-20000: Objects cannot be created in the production database.

Сообщение об ошибке получилось кратким и не слишком содержательным. Произошел сбой, но какой именно? Хорошо бы включить в сообщение дополнительную информа­цию, например указать тип объекта, который пытался создать пользователь:

TRIGGER no_create AFTER CREATE ON SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, "Cannot create the " || ORA_DICT_OBJ_TYPE || " named" || ORA_DICT_OBJ_NAME || " as requested by" || ORA_DICT_OBJ_OWNER || " in production."); END;

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

SQL> CREATE TABLE demo (coll NUMBER); * ERROR at line 1: ORA-20000: Cannot create the TABLE named DEMO as requested by SCOTT in production

Можно было бы даже реализовать эту логику в виде триггера BEFORE и воспользоваться событием ora_sysevent:

TRIGGER no_create BEFORE DDL ON SCHEMA BEGIN IF ORA_SYSEVENT = "CREATE" THEN RAISE_APPLICATION_ERROR (-20000, "Cannot create the " || ORA_DICT_OBJ_TYPE || " named " ||ORA_DICT_OBJ_NAME || " as requested by " || ORA_DICT_OBJ_OWNER); ELSIF ORA_SYSEVENT = "DROP" THEN -- Логика операций DROP ... END IF; END;

Какой столбец был изменен?

Для получения информации о том, какой столбец был изменен командой ALTER TABLE , можно воспользоваться функцией ORA_IS_ALTER_COLUMN . Пример:

TRIGGER preserve_app_cols AFTER ALTER ON SCHEMA DECLARE -- Курсор для получения информации о столбцах таблицы CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) IS SELECT column_name FROM all_tab_columns WHERE owner = cp_owner AND table_name = cp_table; BEGIN -- Если была изменена таблица... IF ora_dict_obj_type = "TABLE" THEN -- Для каждого столбца в таблице... FOR v_column_rec IN curs_get_columns (ora_dict_obj_owner, ora_dict_obj_name) LOOP -- Является ли текущий столбец измененным? IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name) THEN -- Отклонить изменения в "критическом" столбце IF mycheck.is_application_column (ora_dict_obj_owner, ora_dict_obj_name, v_column_rec.column_name) THEN CENTRAL_ERROR_HANDLER ("FAIL", "Cannot alter core application attributes"); END IF; -- критическая таблица/столбец END IF; -- текущий столбец был изменен END LOOP; -- для каждого столбца в таблице END IF; -- таблица была изменена END;

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

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

IF ORA_IS_DROP_COLUMN ("COL2") THEN что-то сделать ELSE сделать что-то другое! END IF;

Функции ORA_IS_DROP_COLUMN и ORA_IS_ALTER_COLUMN не обращают внимания на то, к какой таблице присоединен столбец; они работают исключи­тельно по имени столбца.

Списки, возвращаемые атрибутными функциями

Некоторые атрибутные функции возвращают данные двух типов: список элементов и количество элементов. Например, функция ORA_GRANTEE возвращает список и ко­личество пользователей, которым предоставлены определенные права, а функция 0RA_PRIVILEGE_LIST - список и количество предоставленных прав. Обычно обе эти функции применяются в триггерах AFTER GRANT . Пример использования этих функций представлен в файле privs.sql на сайте github. Фрагмент кода этого примера:

TRIGGER what_privs AFTER GRANT ON SCHEMA DECLARE v_grant_type VARCHAR2 (30); v_num_grantees BINARY_INTEGER; v_grantee_list ora_name_list_t; v_num_privs BINARY_INTEGER; v_priv_list ora_name_list_t; BEGIN -- Получение информации о типе с последующей выборкой списков. v_grant_type:= ORA_DICT_OBJ_TYPE; v_num_grantees:= ORA_GRANTEE (v_grantee_list); v_num_privs:= ORA_PRIVILEGE_LIST (v_priv_list); IF v_grant_type = "ROLE PRIVILEGE" THEN DBMS_OUTPUT.put_line ("The following roles/privileges were granted"); -- Вывод привилегии для каждого элемента списка. FOR counter IN 1 .. v_num_privs LOOP DBMS_OUTPUT.put_line ("Privilege " || v_priv_list (counter)); END LOOP;

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

SQL> GRANT DBA TO book WITH ADMIN OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE("Flush buffer"); The following roles/privileges were granted Privilege UNLIMITED TABLESPACE Privilege DBA Grant Recipient BOOK Flush buffer SQL> GRANT SELECT ON x TO system WITH GRANT OPTION; Grant succeeded. SQL> EXEC DBMS_OUTPUT.PUT_LINE("Flush buffer"); The following object privileges were granted Privilege SELECT On X with grant option Grant Recipient SYSTEM Flush buffer

Можно ли удалить неудаляемое?

Я показал, что триггеры DDL могут использоваться для предотвращения выполнения определенного типа операций DDL с конкретными объектами или типами объектов. А если я создам триггер, который предотвращает DDL -операции DROP , а затем попытаюсь удалить сам триггер? Не появится ли триггер, который по сути невозможно удалить? К счастью, в Oracle этот сценарий был предусмотрен:

SQL> CREATE OR REPLACE TRIGGER undroppable 2 BEFORE DROP ON SCHEMA 3 BEGIN 4 RAISE_APPLICATION_ERROR(-20000,"You cannot drop me! I am invincible!"); 5 END; SQL> DROP TABLE employee; * ERROR at line 1: ORA-20000: You cannot drop me! I am invincible! SQL> DROP TRIGGER undroppable; Trigger dropped.

При работе с подключаемыми базами данных (Oracle Database 12c и выше) можно вставить ключевое слово PLUGGABLE перед DATABASE в определении триггера. DATABASE (без PLUGGABLE) определяет триггер на корневом уровне. В мультиарендной (multitenant) контейнерной базе данных (CDB) только пользователь, подключившийся к корневому уровню, может создать триггер для всей базы данных. PLUGGABLE DATABASE определяет триггер для подключаемой базы данных, к которой вы подключены. Триггер срабатывает каждый раз, когда любой пользователь заданной базы данных или PDB инициирует активизирующее событие.

Триггер INSTEAD OF CREATE

Oracle предоставляет триггер INSTEAD OF CREATE для автоматической группировки данных таблиц. Для этого триггер должен перехватить выполняемую команду SQL , вставить в нее условие группировки, а затем выполнить при помощи функции ORA_SQL_TXT . Сле­дующий пример показывает, как это делается.

TRIGGER io_create INSTEAD OF CREATE ON DATABASE WHEN (ORA_DICT_OBJ_TYPE = "TABLE") DECLARE v_sql VARCHAR2 (32767); -- Генерируемый код sql v_sql_t ora_name_list_t; -- таблица sql BEGIN -- Получение выполняемой команды SQL FOR counter IN 1 .. ora_sql_txt (v_sql_t) LOOP v_sql:= v_sql || v_sql_t (counter); END LOOP; -- Для определения условия PARTITION будет вызвана -- функция magic_partition. v_sql:= SUBSTR (v_sql, 1, ora_partition_pos) || magic_partition_function || SUBSTR (v_sql, ora_partition_pos + 1); /* Вставить перед именем таблицы имя пользователя. | Комбинации CRLF заменяются пробелами. | Операция требует наличия явной привилегии CREATE ANY TABLE, | если только вы не переключились на модель AUTHID CURRENT_USER. */ v_sql:= REPLACE (UPPER (REPLACE (v_sql, CHR (10), " ")) , "CREATE TABLE " , "CREATE TABLE " || ora_login_user || "."); -- Выполнение сгенерированной команды SQL EXECUTE IMMEDIATE v_sql; END;

Теперь группировка таблиц будет осуществляться автоматически в соответствии с ло­гикой функции my_partition .

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

Если не включить только что приведенную секцию WHEN , при попытке создания объектов, отличных от таблиц, происходит ошибка:

ORA-00604: error occurred at recursive SQL level 1 ORA-30511: invalid DDL operation in system triggers

Кроме того, при попытке создания триггера INSTEAD OF для любой другой операции DDL , кроме CREATE , компилятор выдает сообщение об ошибке (ORA-30513).

Триггеры INSTEAD OF для операций DML (вставка, обновление и удаление) будут рас­сматриваться мною далее в блоге. Эти триггеры используют некоторые элементы синтаксиса триггера INSTEAD OF CREATE для таблиц, но этим сходство между ними ограничивается.

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

Создает триггер языка обработки данных, DDL или входа. Триггер - это особая разновидность хранимой процедуры, выполняемая автоматически при возникновении события на сервере базы данных. Триггеры языка обработки данных выполняются по событиям, вызванным попыткой пользователя изменить данные с помощью языка обработки данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Эти триггеры срабатывают при запуске любого допустимого события независимо от того, влияет ли оно на какие-либо строки таблицы.

Триггеры DDL срабатывают в ответ на ряд событий языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции. Триггеры входа могут срабатывать в ответ на событие LOGON, возникающее при установке пользовательских сеансов. Триггеры могут быть созданы непосредственно из Transact-SQL инструкций или методов сборок, созданных в Microsoft .NET Framework общеязыковая среда выполнения (CLR) и передачи в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой инструкции.

SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name

SQL Server Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table (DML Trigger on memory -optimized tables) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table } [ WITH [ ,...n ] ] { FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] } ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]

Trigger on a CREATE , ALTER , DROP , GRANT , DENY , REVOKE or UPDATE statement (DDL Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

Trigger on a LOGON event (Logon Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ ,...n ] ] { FOR | AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] [ ; ] > } ::= [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP , GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON { DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] [ ; ] } ::= [ EXECUTE AS Clause ]

Условно изменяет триггер только в том случае, если он уже существует.

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

имя_триггера
Имя триггера. Объект имя_триггера должно соответствовать правилам для , за исключением того, что имя_триггера не может начинаться с символов # или ##.

table | view
Таблица или представление, в которых выполняется триггер DML, иногда указывается как таблица триггера или представление триггера. Указание уточненного имени таблицы или представления не является обязательным. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах.

DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если указано, триггер срабатывает всякий раз, когда event_type или event_group происходит в текущей базе данных.

Применяет область действия триггера DDL или триггера входа к текущему серверу. Если указано, триггер срабатывает всякий раз, когда event_type или event_group возникновении в любом месте на текущем сервере.

Затемняет текст инструкции CREATE TRIGGER. Использование параметра WITH ENCRYPTION не позволяет публиковать триггер как часть репликации SQL Server. Параметр WITH ENCRYPTION не может быть указан для триггеров CLR.

EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, ссылаемые триггером.

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

NATIVE_COMPILATION
Указывает, что триггер компилируется в собственном коде.

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

SCHEMABINDING
Гарантирует, что таблицы, на которые ссылается триггер не может быть удалена или изменена.

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

FOR | AFTER
Тип AFTER указывает, что триггер DML срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Все каскадные действия и проверки ограничений, на которые имеется ссылка, должны быть успешно завершены, прежде чем триггер сработает.

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

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

INSTEAD OF
Указывает, что триггер DML выполняется вместо запуск инструкции SQL, поэтому, переопределение действия запускающих инструкций. Аргумент INSTEAD OF не может быть указан для триггеров DDL или триггеров входа.

На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEAD OF.

Использование триггеров INSTEAD OF не допускается в поддерживающих обновление представлениях, которые используют параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к поддерживающему обновление представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON DELETE. Аналогично параметр UPDATE не разрешен в таблицах, у которых есть ссылочная связь с указанием каскадного действия ON UPDATE.

Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND не может быть использован для триггеров INSTEAD OF или при явном указании триггера AFTER. Аргумент WITH APPEND может использоваться только при указании параметра FOR без INSTEAD OF или AFTER из соображений поддержки обратной совместимости. Аргумент WITH APPEND не может быть указан, если указан параметр EXTERNAL NAME (в случае триггера CLR).

event_type
Имя языкового события Transact-SQL, которое после выполнения вызывает срабатывание триггера DDL. Допустимые события для триггеров DDL, перечислены в .

event_group
Имя стандартной группы событий языка Transact-SQL. Триггер DDL срабатывает после выполнения какого-либо Transact-SQL событий языка, к которому принадлежит event_group . Список групп событий для триггеров DDL, перечислены в .

После завершения работы, инструкции CREATE TRIGGER event_group также функционирует в качестве макроса, добавляя события соответствующих типов в представление каталога sys.trigger_events.

NOT FOR REPLICATION

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

sql_statement
Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события - DML, DDL или событие входа - вызывают срабатывание триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

Триггеры могут содержать любое количество инструкций языка Transact-SQL любого типа, за некоторыми исключениями. Дополнительные сведения см. в подразделе «Примечания». Триггеры разработаны для контроля или изменения данных на основании инструкций модификации или определения данных; они не возвращают пользователю никаких данных. Transact-SQL Часто содержат инструкции в триггере языка управления потоком .

Триггеры DML используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. Например, для запроса всех значений таблицы deleted можно использовать инструкцию:

SELECT * FROM deleted;

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

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

SQL Serverпозволяет обновить текст , ntext , или изображение триггер столбцов с помощью INSTEAD OF для таблиц или представлений.

Для триггеров в таблицах, оптимизированных для памяти единственным sql_statement разрешено на верхнем уровне-АТОМАРНОГО блока. Допускается внутри блока ATOMIC T-SQL, ограничивается допускается внутри собственного процедуры T-SQL.

< method_specifier >

Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым SQL Server идентификатором и существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки () или двойные кавычки (" "). Класс не может быть вложенным.

Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. В SQL Server декларативное ограничение ссылочной целостности обеспечивается инструкциями ALTER TABLE и CREATE TABLE. Однако декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после срабатывания триггера INSTEAD OF и до выполнения триггера AFTER. В случае нарушения ограничения выполняется откат действий триггера INSTEAD OF, и триггер AFTER не срабатывает.

Первые и последние триггеры AFTER, которые будут выполнены в таблице, могут быть определены с использованием процедуры sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если в таблице есть другие триггеры AFTER, они будут выполняться случайным образом.

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

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

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

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

Например, если триггер определен как INSTEAD OF UPDATE для представления и выполняет инструкцию UPDATE для этого же представления, инструкция UPDATE, выполняемая триггером, не вызывает нового срабатывания триггера. Инструкция UPDATE, выполняемая в триггере, обрабатывает представление так, как если бы у представления не имелось триггера INSTEAD OF. Столбцы, измененные с помощью инструкции UPDATE, должны принадлежать одной базовой таблице. Каждая модификация базовой таблицы вызывает применение последовательности ограничений и взвод триггеров AFTER, определенных для данной таблицы.

Проверка действий инструкций UPDATE или INSERT на указанные столбцы

Триггер языка Transact-SQL можно сконструировать для выполнения конкретных действий, основанных на изменении определенных столбцов с помощью инструкций UPDATE или INSERT. Используйте или в теле триггера для этой цели. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. С помощью конструкции COLUMNS_UPDATED проверяются действия инструкций UPDATE или INSERT, проводимых на нескольких столбцах, и возвращается битовый шаблон, показывающий, какие столбцы были вставлены или обновлены.

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

Инструкция CREATE TRIGGER должна быть первой инструкцией в пакете и может применяться только к одной таблице.

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

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

Одно и то же действие триггера может быть определено более чем для одного действия пользователя (например, INSERT и UPDATE) в одной и той же инструкции CREATE TRIGGER.

Триггеры INSTEAD OF DELETE/UPDATE нельзя определить для таблицы, у которой есть внешний ключ, определенный для каскадного выполнения операции DELETE/UPDATE.

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

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

Хотя инструкция TRUNCATE TABLE по своей сути является инструкцией DELETE, она не активирует триггер, поскольку операция не записывает удаление отдельных строк. Однако беспокоиться о случайном обходе триггера DELETE таким образом нужно только пользователям с разрешениями на выполнение инструкции TRUNCATE TABLE.

Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.

Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:

Триггеры DDL, как и стандартные триггеры, выполняют хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают в ответ на выполнение инструкций UPDATE, INSERT или DELETE по отношению к таблице или представлению. Вместо этого триггеры срабатывают в первую очередь в ответ на инструкции языка определения данных (DDL). Это инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

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

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

В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Поэтому для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в разделе .

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

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

В триггерах входа не поддерживаются распределенные транзакции. Если срабатывает триггер входа, содержащий распределенную транзакцию, возвращается ошибка 3969.

Отключение триггера входа

Триггер входа может эффективно запрещать подключения к службам Компонент Database Engine для всех пользователей, в том числе членов предопределенной роли сервера sysadmin . Если триггер входа запрещает соединения, члены предопределенной роли сервера sysadmin могут подключаться с помощью выделенного административного соединения или путем вызова Компонент Database Engine в режиме минимальной конфигурации (-f). Дополнительные сведения см. в разделе .

Возвращаемые результаты

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

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

Несколько триггеров

SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL и LOGON. Например, если инструкция CREATE TRIGGER FOR UPDATE выполняется в таблице, уже имеющей триггер UPDATE, дополнительно создается триггер обновления. В более ранних версиях SQL Server был разрешен только один триггер в каждой таблице для каждого события изменения данных INSERT, UPDATE или DELETE.

Рекурсивные триггеры

SQL Server разрешает рекурсивный вызов триггеров, если с помощью инструкции ALTER DATABASE включена настройка RECURSIVE_TRIGGERS.

В рекурсивных триггерах могут возникать следующие типы рекурсии:

Косвенная рекурсия

При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Это вызывает срабатывание триггера T2 и обновление таблицы T1.

Прямая рекурсия

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

В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет каждый из триггеров TR1 и TR2 один раз. В дополнение к этому срабатывание триггера TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.

Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.

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

Вложенные триггеры

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

Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. В конфигурации по умолчанию вложенные триггеры разрешены. Если вложенные триггеры отключены, рекурсивные триггеры тоже будут отключены, вне зависимости от настройки RECURSIVE_TRIGGERS, установленной с помощью инструкции ALTER DATABASE.

Первый триггер AFTER, вложенный в INSTEAD OF, триггер срабатывает, даже если вложенные триггеры параметр конфигурации сервера задано значение 0. Однако при таком значении параметра последующие триггеры AFTER не срабатывают. Рекомендуется проверить приложения на наличие вложенных триггеров определить, соответствуют ли приложения бизнес-правилам это поведение при вложенные триггеры параметр конфигурации сервера имеет значение 0 и внесите соответствующие изменения.

Отложенная интерпретация имен

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

Для создания триггера DML требуется разрешение ALTER на таблицу или представление, в которых создается триггер.

Для создания триггера DDL с областью действия в пределах сервера (ON ALL SERVER) или триггера входа требуется разрешение CONTROL SERVER на сервер. Для создания триггера DDL с областью видимости в пределах базы данных (ON DATABASE) требуется разрешение ALTER ANY DATABASE DDL TRIGGER на текущую базу данных.

A. Использование триггера DML с предупреждающим сообщением

Следующий триггер DML отправляет клиенту сообщение, когда кто-то пытается добавить или изменить данные в таблице Customer в базе данных AdventureWorks2012.

CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT , UPDATE AS RAISERROR ("Notify Customer Relations" , 16 , 10 ); GO

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

В следующем примере указанному пользователю (MaryM) по электронной почте отправляется сообщение при изменении таблицы Customer .

CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT , UPDATE , DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = "AdventureWorks2012 Administrator" , @recipients = "[email protected]" , @body = "Don""t forget to print a report for the sales force." , @subject = "Reminder" ; GO

В. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor

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

В следующем примере создается триггер DML в базе данных AdventureWorks&2012;. Этот триггер выполняет проверку кредитоспособности поставщика является хорошим (не 5) при попытке вставить новый заказ на покупку в PurchaseOrderHeader таблицы. Для получения сведений о кредитоспособности поставщика требуется ссылка на таблицу Vendor . В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не выполняется.

This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table -- when the credit rating of the specified vendor is set to 5 (below average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p .PurchaseOrderID = i .PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p .VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ("A vendor""s credit rating is too low to accept new purchase orders." , 16 , 1 ); ROLLBACK TRANSACTION ; RETURN END ; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status , EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES (2 ,3 ,261 ,1652 ,4 ,GETDATE () ,GETDATE () ,44594.55 ,3567.564 ,1114.8638 ); GO

Г. Использование триггера DDL уровня базы данных

В следующем примере триггер DDL используется для предотвращения удаления синонимов в базе данных.

CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS RAISERROR ("You must disable Trigger "safety" to drop synonyms!" ,10 , 1 ) ROLLBACK GO DROP TRIGGER safety ON DATABASE ; GO

Д. Использование триггера DDL уровня сервера

В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Дополнительные примеры использования функции EVENTDATA в триггерах DDL см. .

CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created." SELECT EVENTDATA ().value ("(/EVENT_INSTANCE/TSQLCommand/CommandText)" ,"nvarchar(max)" ) GO DROP TRIGGER ddl_trig_database ON ALL SERVER ; GO

Е. Использование триггера входа

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

USE master ; GO CREATE LOGIN login_test WITH PASSWORD = "3KHJ6dhx(0xVYsdf" MUST_CHANGE, CHECK_EXPIRATION = ON ; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS "login_test" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "login_test" AND (SELECT COUNT (*) FROM sys .dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "login_test" ) >