триггера :

<Определение_триггера>::= {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 находятся старые значения строк, которые будут удалены при успешном завершении

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

163

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

Здесь в параметре schema_name указывается имя схемы, к которой принадлежит триггер, а в параметре trigger_name - имя триггера. В параметре table_name задается имя таблицы, для которой создается триггер. (Также поддерживаются триггеры для представлений, на что указывает наличие параметра view_name.)

Также можно задать тип триггера с помощью двух дополнительных параметров: AFTER и INSTEAD OF. (Параметр FOR является синонимом параметра AFTER.) Триггеры типа AFTER вызываются после выполнения действия, запускающего триггер, а триггеры типа INSTEAD OF выполняются вместо действия, запускающего триггер. Триггеры AFTER можно создавать только для таблиц, а триггеры INSTEAD OF - как для таблиц, так и для представлений.

Параметры INSERT, UPDATE и DELETE задают действие триггера. Под действием триггера имеется в виду инструкция Transact-SQL, которая запускает триггер. Допускается любая комбинация этих трех инструкций. Инструкция DELETE не разрешается, если используется параметр IF UPDATE.

Как можно видеть в синтаксисе инструкции CREATE TRIGGER, действие (или действия) триггера указывается в спецификации AS sql_statement.

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

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

Изменение структуры триггера

Язык Transact-SQL также поддерживает инструкцию ALTER TRIGGER , которая модифицирует структуру триггера. Эта инструкция обычно применяется для изменения тела триггера. Все предложения и параметры инструкции ALTER TRIGGER имеют такое же значение, как и одноименные предложения и параметры инструкции CREATE TRIGGER.

Для удаления триггеров в текущей базе данных применяется инструкция DROP TRIGGER .

Использование виртуальных таблиц deleted и inserted

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

    deleted - содержит копии строк, удаленных из таблицы;

    inserted - содержит копии строк, вставленных в таблицу.

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

Таблица deleted используется в том случае, если в инструкции CREATE TRIGGER указывается предложение DELETE или UPDATE, а если в этой инструкции указывается предложение INSERT или UPDATE, то используется таблица inserted . Это означает, что для каждой инструкции DELETE, выполненной в действии триггера, создается таблица deleted. Подобным образом для каждой инструкции INSERT, выполненной в действии триггера, создается таблица inserted.

Инструкция UPDATE рассматривается, как инструкция DELETE, за которой следует инструкция INSERT. Поэтому для каждой инструкции UPDATE, выполненной в действии триггера, создается как таблица deleted, так и таблица inserted (в указанной последовательности).

Таблицы inserted и deleted реализуются, используя управление версиями строк, которое рассматривалось в предыдущей статье. Когда для таблицы с соответствующими триггерами выполняется инструкция DML (INSERT, UPDATE или DELETE), для всех изменений в этой таблице всегда создаются версии строк. Когда триггеру требуется информация из таблицы deleted, он обращается к данным в хранилище версий строк. В случае таблицы inserted, триггер обращается к самым последним версиям строк.

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

Области применения DML-триггеров

Такие триггеры применяются для решения разнообразных задач. В этом разделе мы рассмотрим несколько областей применения триггеров DML, в частности триггеров AFTER и INSTEAD OF.

Триггеры AFTER

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

    создания журнала логов действий в таблицах базы данных;

    реализации бизнес-правил;

    принудительного обеспечения ссылочной целостности.

Создание журнала логов

В SQL Server можно выполнять отслеживание изменения данных, используя систему перехвата изменения данных CDC (change data capture). Эту задачу можно также решить с помощью триггеров DML. В примере ниже показывается, как с помощью триггеров можно создать журнал логов действий в таблицах базы данных:

USE SampleDb; /* Таблица AuditBudget используется в качестве журнала логов действий в таблице Project */ GO CREATE TABLE AuditBudget (ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL); GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(budget) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT Budget FROM deleted) SELECT @budgetNew = (SELECT Budget FROM inserted) SELECT @projectNumber = (SELECT Number FROM deleted) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END

В этом примере создается таблица AuditBudget, в которой сохраняются все изменения столбца Budget таблицы Project. Изменения этого столбца будут записываться в эту таблицу посредством триггера trigger_ModifyBudget.

Этот триггер активируется для каждого изменения столбца Budget с помощью инструкции UPDATE. При выполнении этого триггера значения строк таблиц deleted и inserted присваиваются соответствующим переменным @budgetOld, @budgetNew и @projectNumber. Эти присвоенные значения, совместно с именем пользователя и текущей датой, будут затем вставлены в таблицу AuditBudget.

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

то содержимое таблицы AuditBudget будет таким:

Реализация бизнес-правил

С помощью триггеров можно создавать бизнес-правила для приложений. Создание такого триггера показано в примере ниже:

USE SampleDb; -- Триггер trigger_TotalBudget является примером использования -- триггера для реализации бизнес-правила GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE (Budget) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = (SELECT SUM(Budget) FROM inserted) SELECT @sum_old1 = (SELECT SUM(p.Budget) FROM project p WHERE p.Number NOT IN (SELECT d.Number FROM deleted d)) SELECT @sum_old2 = (SELECT SUM(Budget) FROM deleted) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT "Бюджет не изменился" ROLLBACK TRANSACTION END ELSE PRINT "Изменение бюджета выполнено" END

Здесь создается правило для управления модификацией бюджетов проектов. Триггер trigger_TotalBudget проверяет каждое изменение бюджетов и выполняет только такие инструкции UPDATE, которые увеличивают сумму всех бюджетов не более чем на 50%. В противном случае для инструкции UPDATE выполняется откат посредством инструкции ROLLBACK TRANSACTION.

Принудительное обеспечение ограничений целостности

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

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

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

USE SampleDb; GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, inserted WHERE Employee.Id = inserted.EmpId) IS NULL BEGIN ROLLBACK TRANSACTION PRINT "Строка не была вставлена/модифицирована" END ELSE PRINT "Строка была вставлена/модифицирована" END

Триггер trigger_WorksonIntegrity в этом примере проверяет ссылочную целостность для таблиц Employee и Works_on. Это означает, что проверяется каждое изменение столбца Id в ссылочной таблице Works_on, и при любом нарушении этого ограничения выполнение этой операции не допускается. (То же самое относится и к вставке в столбец Id новых значений.) Инструкция ROLLBACK TRANSACTION во втором блоке BEGIN выполняет откат инструкции INSERT или UPDATE в случае нарушения ограничения для обеспечения ссылочной целостности.

В этом примере триггер выполняет проверку на проблемы ссылочной целостности первого и второго случая между таблицами Employee и Works_on. А в примере ниже показан триггер, который выполняет проверку на проблемы ссылочной целостности третьего и четвертого случая между этими же таблицами (эти случаи обсуждались в статье "Transact-SQL - создание таблиц"):

USE SampleDb; GO CREATE TRIGGER trigger_RefintWorkson2 ON Employee AFTER DELETE, UPDATE AS IF UPDATE (Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, deleted WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK TRANSACTION PRINT "Строка не была вставлена/модифицирована" END ELSE PRINT "Строка была вставлена/модифицирована" END

Триггеры INSTEAD OF

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

Триггеры INSTEAD OF можно создавать как для таблиц, так и для представлений. Когда инструкция Transact-SQL ссылается на представление, для которого определен триггер INSTEAD OF, система баз данных выполняет этот триггер вместо выполнения любых действий с любой таблицей. Данный тип триггера всегда использует информацию в таблицах inserted и deleted, созданных для представления, чтобы создать любые инструкции, требуемые для создания запрошенного события.

Значения столбцов, предоставляемые триггером INSTEAD OF, должны удовлетворять определенным требованиям:

    значения не могут задаваться для вычисляемых столбцов;

    значения не могут задаваться для столбцов с типом данных timestamp;

    значения не могут задаваться для столбцов со свойством IDENTITY, если только параметру IDENTITY_INSERT не присвоено значение ON.

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

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

USE SampleDb; CREATE TABLE Orders (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate)); GO CREATE VIEW view_AllOrders AS SELECT * FROM Orders; GO CREATE TRIGGER trigger_orders ON view_AllOrders INSTEAD OF INSERT AS BEGIN INSERT INTO Orders SELECT OrderId, Price, Quantity, OrderDate FROM inserted END

В этом примере используется таблица Orders, содержащая два вычисляемых столбца. Представление view_AllOrders содержит все строки этой таблицы. Это представление используется для задания значения в его столбце, которое соотносится с вычисляемым столбцом в базовой таблице, на которой создано представление. Это позволяет использовать триггер INSTEAD OF, который в случае инструкции INSERT заменяется пакетом, который вставляет значения в базовую таблицу посредством представления view_AllOrders. (Инструкция INSERT, обращающаяся непосредственно к базовой таблице, не может задавать значение вычисляемому столбцу.)

Триггеры first и last

Компонент Database Engine позволяет создавать несколько триггеров для каждой таблицы или представления и для каждой операции (INSERT, UPDATE и DELETE) с ними. Кроме этого, можно указать порядок выполнения для нескольких триггеров, определенных для конкретной операции. С помощью системной процедуры sp_settriggerorder можно указать, что один из определенных для таблицы триггеров AFTER будет выполняться первым или последним для каждого обрабатываемого действия. Эта системная процедура имеет параметр @order, которому можно присвоить одно из трех значений:

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

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

    none - указывает, что для триггера отсутствует какой-либо определенный порядок выполнения. (Это значение обычно используется для того, чтобы выполнить сброс ранее установленного порядка выполнения триггера как первого или последнего.)

Изменение структуры триггера посредством инструкции ALTER TRIGGER отменяет порядок выполнения триггера (первый или последний). Применение системной процедуры sp_settriggerorder показано в примере ниже:

USE SampleDb; EXEC sp_settriggerorder @triggername = "trigger_ModifyBudget", @order = "first", @stmttype="update"

Для таблицы разрешается определить только один первый и только один последний триггер AFTER. Остальные триггеры AFTER выполняются в неопределенном порядке. Узнать порядок выполнения триггера можно с помощью системной процедуры sp_helptrigger или функции OBJECTPROPERTY.

Возвращаемый системной процедурой sp_helptrigger результирующий набор содержит столбец order, в котором указывается порядок выполнения указанного триггера. При вызове функции objectproperty в ее втором параметре указывается значение ExeclsFirstTrigger или ExeclsLastTrigger, а в первом параметре всегда указывается идентификационный номер объекта базы данных. Если указанное во втором параметре свойство имеет значение true, функция возвращает значение 1.

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

Триггеры DDL и области их применения

Ранее мы рассмотрели триггеры DML, которые задают действие, предпринимаемое сервером при изменении таблицы инструкциями INSERT, UPDATE или DELETE. Компонент Database Engine также позволяет определять триггеры для инструкций DDL, таких как CREATE DATABASE, DROP TABLE и ALTER TABLE. Триггеры для инструкций DDL имеют следующий синтаксис:

CREATE TRIGGER trigger_name ON {ALL SERVER | DATABASE } {FOR | AFTER } { event_group | event_type | LOGON} AS {batch | EXTERNAL NAME method_name} Соглашения по синтаксису

Как можно видеть по их синтаксису, триггеры DDL создаются таким же способом, как и триггеры DML. А для изменения и удаления этих триггеров используются те же инструкции ALTER TRIGGER и DROP TRIGGER, что и для триггеров DML. Поэтому в этом разделе рассматриваются только те параметры инструкции CREATE TRIGGER, которые новые для синтаксиса триггеров DDL.

Первым делом при определении триггера DDL нужно указать его область действия. Предложение DATABASE указывает в качестве области действия триггера DDL текущую базу данных, а предложение ALL SERVER - текущий сервер.

После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.

Кроме сходства триггеров DML и DDL, между ними также есть несколько различий. Основным различием между этими двумя видами триггеров является то, что для триггера DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект. Кроме этого, триггеры DDL не поддерживают триггеров INSTEAD OF. Как вы, возможно, уже догадались, для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.

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

Триггеры DDL уровня базы данных

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

USE SampleDb; GO CREATE TRIGGER trigger_PreventDrop ON DATABASE FOR DROP_TRIGGER AS PRINT "Перед тем, как удалить триггер, вы должны отключить "trigger_PreventDrop"" ROLLBACK

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

Триггеры DDL уровня сервера

Триггеры уровня сервера реагируют на серверные события. Триггер уровня сервера создается посредством использования предложения ALL SERVER в инструкции CREATE TRIGGER. В зависимости от выполняемого триггером действия, существует два разных типа триггеров уровня сервера: обычные триггеры DDL и триггеры входа. Запуск обычных триггеров DDL основан на событиях инструкций DDL, а запуск триггеров входа - на событиях входа.

В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:

USE master; GO CREATE LOGIN loginTest WITH PASSWORD = "12345!", CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO loginTest; GO CREATE TRIGGER trigger_ConnectionLimit ON ALL SERVER WITH EXECUTE AS "loginTest" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "loginTest" AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "loginTest") > 1 ROLLBACK; END;

Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.

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

Триггеры и среда CLR

Подобно хранимым процедурам и определяемым пользователем функциям, триггеры можно реализовать, используя общеязыковую среду выполнения (CLR - Common Language Runtime). Триггеры в среде CLR создаются в три этапа:

    Создается исходный код триггера на языке C# или Visual Basic, который затем компилируется, используя соответствующий компилятор в объектный код.

    Объектный код обрабатывается инструкцией CREATE ASSEMBLY, создавая соответствующий выполняемый файл.

    Посредством инструкции CREATE TRIGGER создается триггер.

Выполнение всех этих трех этапов создания триггера CLR демонстрируется в последующих примерах. Ниже приводится пример исходного кода программы на языке C# для триггера из первого примера в статье. Прежде чем создавать триггер CLR в последующих примерах, сначала нужно удалить триггер trigger_PreventDrop, а затем удалить триггер trigger_ModifyBudget, используя в обоих случаях инструкцию DROP TRIGGER.

Using System; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class Triggers { public static void ModifyBudget() { SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2)) // Столбец Budget { float budget_old; float budget_new; string project_number; SqlConnection conn = new SqlConnection("context connection=true"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Budget FROM DELETED"; budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Budget FROM INSERTED"; budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Number FROM DELETED"; project_number = Convert.ToString(cmd.ExecuteScalar()); cmd.CommandText = @"INSERT INTO AuditBudget (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", project_number); cmd.Parameters.AddWithValue("@budgetOld", budget_old); cmd.Parameters.AddWithValue("@budgetNew", budget_new); cmd.ExecuteNonQuery(); } } }

Пространство имен Microsoft.SQLServer.Server содержит все классы клиентов, которые могут потребоваться программе C#. Классы SqlTriggerContext и SqlFunction являются членами этого пространства имен. Кроме этого, пространство имен System.Data.SqlClient содержит классы SqlConnection и SqlCommand, которые используются для установления соединения и взаимодействия между клиентом и сервером базы данных. Соединение устанавливается, используя строку соединения "context connection = true".

Затем определяется класс Triggers, который применяется для реализации триггеров. Метод ModifyBudget() реализует одноименный триггер. Экземпляр context класса SqlTriggerContext позволяет программе получить доступ к виртуальной таблице, создаваемой при выполнении триггера. В этой таблице сохраняются данные, вызвавшие срабатывание триггера. Метод IsUpdatedColumn() класса SqlTriggerContext позволяет узнать, был ли модифицирован указанный столбец таблицы.

Данная программа содержит два других важных класса: SqlConnection и SqlCommand. Экземпляр класса SqlConnection обычно применяется для установления соединения с базой данных, а экземпляр класса SqlCommand позволяет исполнять SQL-инструкции.

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

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, на основе которого создается триггер CLR. Предложение WITH PERMISSION_SET в примере указывает, что разрешениям доступа присвоено значение SAFE.

Наконец, в примере ниже посредством инструкции CREATE TRIGGER создается триггер trigger_modify_budget:

USE SampleDb; GO CREATE TRIGGER trigger_modify_budget ON Project AFTER UPDATE AS EXTERNAL NAME CLRStoredProcedures.Triggers.ModifyBudget

Инструкция CREATE TRIGGER в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME . Этот параметр указывает, что код создается средой CLR. Имя в этом параметре состоит из трех частей. В первой части указывается имя соответствующей сборки (CLRStoredProcedures), во второй - имя открытого класса, определенного в примере выше (Triggers), а в третьей указывается имя метода, определенного в этом классе (ModifyBudget).

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

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

1) Триггеры на вставку. Запускаются при попытке вставки данных с помощью команды Insert.

2) Триггеры на обновление. Запускаются при попытке изменения данных с помощью команды Update.

3) Триггеры на удаление. Запускаются при попытке удаления данных с помощью команды Delete.

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

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

CREATE TRIGGER trigger_name

sql_statement [...n]

Второй вариант данной команды:

CREATE TRIGGER trigger_name

{FOR { [ [,] }

{ IF UPDATE (column)

UPDATE(column) ]

sql_statement [...n]

Рассмотрим работу первого варианта работы команды Create trigger.

Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД.

TABLE – имя таблицы БД, к которой будет привязан триггер.

WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера.

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


WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер.

NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации.

sql_statement – определяет набор команд, которые будут выполняться при запуске триггера.

Рассмотрим использование второго способа:

FOR { [ [,] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер.

IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы.

AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column).

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

ALTER TRIGGER trigger_name

{FOR { [,] [,] }

sql_statement [...n]

Перед тем как выполнить команду ALTER TRIGGER следует убедиться в существовании в таблице table триггера.

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

DROP TRIGGER {trigger} [..n]

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

Обзор триггеров Триггеры DML-триггеры DDL-триггеры DML-события: Insert, Delete, Update Logon-триггеры DDL-события: Create, Drop, Alter Logon Появились в SQL Server 2005

DML - trigger Объект - таблица, VIEW Событие - insert, update, delete для таблицы и для VIEW. Время активации – до (вместо) или после выполнения оператора.

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

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

Когда нужны триггеры Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия. Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.

Когда нужны триггеры Для каскадных изменений в связанных таблицах БД (если их нельзя выполнить при помощи каскадных ограничений ссылочной целостности). Для предотвращения случайных или неправильных операций INSERT, UPDATE и DELETE Для реализации ограничений целостности, которые нельзя определить при помощи ограничения CHECK. DML-триггеры могут ссылаться на столбцы других таблиц.

Еще… Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, время операции обновления; сами обновляемые данные и т. д. Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.

Когда не надо использовать триггеры Не нужно реализовывать триггерами возможности, достигаемые использованием декларативных средств СУБД (ограничения целостности или внешние ключи) Избегайте сложных цепочек триггеров

Советы Не используйте триггеры, если можно применить проверочное ограничение CHECK Не используйте ограничение CHECK, если можно обойтись ограничением UNIQUE.

Основные параметры триггера Имя таблицы (или представления) Время срабатывания: AFTER(FOR) или INSTEAD OF Событие: INSERT, UPDATE, DELETE (TRUNCATE TABLE – это не удаление!) Тело триггера! Последовательность срабатывания однотипных триггеров произвольна

Группировка событий Например, вы можете создать триггер, который будет активизироваться, когда происходит выполнение оператора UPDATE или INSERT, и такой триггер мы будем называть триггером UPDATE/INSERT. Вы можете даже создать триггер, который будет активизироваться при возникновении любого из трех событий модификации данных (триггер UPDATE/INSERT/DELETE).

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

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

Пример CREATE TRIGGER trg ON my_table FOR INSERT, UPDATE, DELETE AS select "this is trigger"

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

Выбор типа триггера Триггеры INSTEAD OF используются для: – Выборочного запрещения исполнения команды, для которой определен триггер (проверки предусловия); – Подсчета значений столбцов до завершения команды INSERT или UPDATE. Триггеры AFTER используются для: – Учета выполненных операций; – Проверки пост-условий исполнения команды.

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

Триггер INSTEAD OF Триггер INSTEAD OF выполняется вместо запуска оператора SQL. Тем самым переопределяется действие запускающего оператора. Можно задать по одному триггеру INSTEAD OF на один оператор INSERT, UPDATE или DELETE. Триггер INSTEAD OF можно задать для таблицы и/или представления Можно использовать каскады триггеров INSTEAD OF, определяя представления поверх представлений, где каждое представление имеет отдельный триггер INSTEAD OF. Триггеры INSTEAD OF не разрешается применять для модифицируемых представлений, содержащих опцию WITH CHECK.

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

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

Порядок AFTER-триггеров sp_settriggerorder @triggername = "Another. Trigger", @order = "first" sp_settriggerorder @triggername = "My. Trigger", @order = "last" sp_settriggerorder @triggername = "My. Other. Trigger", @order = "none" sp_settriggerorder @triggername = "Yet. Another. Trigger", @order = "none"

Использование inserted, deleted Специальные таблицы: inserted – вставленные значения (для INSERT, UPDATE) deleted – удаленные значения (для UPDATE, DELETE)

Использование таблиц deleted и inserted При создании триггера вы имеете доступ к двум временным таблицам с именами deleted и inserted. Они хранятся в памяти, а не на диске. Эти две таблицы имеют одинаковую структуру с таблицей (одинаковые колонки и типы данных), по которой определяется данный триггер.

Использование таблиц deleted и inserted Таблица deleted содержит копии строк, на которые повлиял оператор DELETE или UPDATE. Строки, удаляемые из таблицы данного триггера, перемещаются в таблицу deleted. После этого к данным таблицы deleted можно осуществлять доступ из данного триггера. Таблица inserted содержит копии строк, добавленных к таблице данного триггера при выполнении оператора INSERT или UPDATE. Эти строки добавляются одновременно в таблицу триггера и в таблицу inserted.

Использование таблиц deleted и inserted Поскольку оператор UPDATE обрабатывается как DELETE, после которого следует INSERT, то при использовании оператора UPDATE старые значения строк копируются в таблицу deleted, а новые значения строк – в таблицу триггера и в таблицу inserted. Триггер INSERT => deleted пуст Триггер DELETE => inserted пуст но сообщение об ошибке не возникнет!

Создание триггера CREATE TRIGGER [ schema_name. ]trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement}

CREATE TRIGGER plus_1 ON table 1 instead of insert AS insert table 1 (id, col 1) select id+1, col 1 from inserted;

Обработка исключений Команда ROLLBACK указывает серверу остановить обработку модификации и запретить транзакцию. Существует также команда RAISEERROR, с помощью которой вы можете отправить сообщение об ошибке пользователю. TRY…CATCH

Обработка исключений сообщение об ошибке RAISERROR ("Error raised because of wrong data. ", -- Message text. 16, -- Severity. 1 -- State.); Severity – число от 0 до 25 Определенный пользователем уровень серьезности ошибки. 0 до 18 может указать любой пользователь. 19 до 25 могут быть указаны только sysadmin 20 до 25 считаются неустранимыми - соединение с клиентом обрывается и регистрируется сообщение об ошибке в журналах приложений и ошибок. State Целое число от 0 до 255. Отрицательные значения или значения больше 255 приводят к формированию ошибки. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.

Функции об ошибках Функция ERROR_LINE() возвращает номер строки, в которой произошла ошибка. Функция ERROR_MESSAGE() возвращает текст сообщения, которое будет возвращено приложению. Текст содержит значения таких подставляемых параметров, как длина, имена объектов или время. ERROR_NUMBER() возвращает номер ошибки. Функция ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, в котором произошла ошибка. Эта функция возвращает значение NULL, если данная ошибка не была совершена внутри хранимой процедуры или триггера. ERROR_SEVERITY() возвращает уровень серьезности ошибки. ERROR_STATE() возвращает состояние.

Пример триггера CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header AFTER INSERT AS BEGIN DECLARE @creditrating tinyint, @vendorid int ; SELECT @creditrating = v. Credit. Rating, @vendorid = p. Vendor. ID FROM Purchasing. Purchase. Order. Header p JOIN inserted i ON p. Purchase. Order. ID = i. Purchase. Order. ID JOIN Purchasing. Vendor v ON v. Vendor. ID = i. Vendor. ID ; IF @creditrating = 5 RAISERROR ("This vendor""s credit rating is too low to accept new purchase orders. ", 16, 1) ; END

Управление триггерами Отключение/включение триггера: – DISABLE/ENABLE TRIGGER trigger_name ON object_name Отключение/включение всех триггеров таблицы: – DISABLE/ENABLE TRIGGER ALL ON object_name Изменение триггера: – ALTER TRIGGER trigger_name … Удаление триггера: – DROP TRIGGER trigger_name

Активация/деактивация триггера DISABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name} ; ENABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name}

Применение триггеров Защита – Запрещение доступа в зависимости от значений данных Учет – Ведение журналов изменений Целостность данных – Сложные правила целостности – Сложная ссылочная целостность Производные данные – автоматическое вычисление значений

Типы триггеров Функция Триггер AFTER Триггер INSTEAD OF Сущности Таблицы и представления Количество триггеров на таблицу/представление Несколько на одно событие Один триггер на одно событие Нет ограничений INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности. Каскадные ссылки После следующих операций: Обработка ограничений. Выполнение Декларативные ссылочные действия. Создание таблиц inserted и deleted. Действие, запускающее триггер. Перед следующей операцией: Обработка ограничений. Вместо следующей операции: Действие, запускающее триггер. После следующих операций: Создание таблиц inserted и deleted.

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

DDL - триггеры Триггеры DDL, как и обычные триггеры, вызывают срабатывание хранимых процедур в ответ на событие. Срабатывают в ответ на разнообразные события языка определения данных (DDL). Эти события в основном соответствуют инструкциям языка Transact-SQL, начинающимся ключевыми словами CREATE, ALTER или DROP.

Задачи для DDL - триггеров Предотвратить внесение определенных изменений в схему базы данных. Выполнить в базе данных некоторые действия в ответ на изменения в схеме базы данных. Записывать изменения или события схемы базы данных. Триггеры DDL срабатывают только после выполнения соответствующих инструкций DDL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF.

CREATE TRIGGER trigger_name ON { DATABASE | ALL SERVER } { FOR | AFTER } { event_type | event_group } AS { sql_statement [ ; ] [ , . . . n ] [ ; ] }

Создание/удаление DDL-тр CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created. " DROP TRIGGER ddl_trig_database ON ALL SERVER;

DDL - trigger CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT "You must disable Trigger "safety" to drop or alter tables!" ROLLBACK ;

Для одной инструкции Transact-SQL можно создать несколько триггеров DDL. Триггер DDL и инструкция, приводящая к его срабатыванию, выполняются в одной транзакции. Откат событий ALTER DATABASE, возникших внутри триггера DDL, невозможен. Триггеры DDL выполняются только после завершения инструкции Transact-SQL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF. Триггеры DDL не создают таблицы inserted и deleted.

Logon - trigger Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие вызывается при установке пользовательского сеанса с экземпляром SQL Server. Триггеры входа срабатывают после завершения этапа проверки подлинности при входе, но перед тем, как пользовательский сеанс реально устанавливается.

Logon - trigger CREATE TRIGGER trigger_name ON ALL SERVER { FOR| AFTER } LOGON AS { sql_statement }