Ms sql триггеры примеры. Триггеры базы данных. Области применения DML-триггеров

По материалам статьи Robert Marda на sqlservercentral.com: Auditing Through Triggers

В этой статье Роберт приводит примеры кода для нескольких триггеров, устанавливаемых на таблицы в целях аудита действий пользователей с записями MS SQL Server 7.0/2000.

Для пояснения работы триггеров вообще и как они работают в SQL Server 7.0 и SQL Server 2000, Вы можете обратиться к следующим статьям, написанным Брайеном Келлей (Brian Kelley):

Первая статья объясняет назначение специальных таблиц для вставки и удаления (inserted and deleted Tables).
Предлагаемые ниже примеры будут работать на SQL Server 2000, однако они были проверены только на SQL Server 7.0.
Сначала мы должны создать необходимые для дальнейшей работы таблицы. Выполните представленный ниже скрипт в Query Analyzer:

CREATE TABLE (
IDENTITY (1, 1) NOT NULL ,

NULL ,
(35) NULL
) ON
GO

CREATE TABLE (
NOT NULL ,
(25) NULL ,
(25) NULL ,
(75) NULL ,
(50) NULL ,
NULL ,
(35) NULL ,
) ON
GO

Триггер, отслеживающий операции удаления

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

Components: CREATE TRIGGER deletedby ON dbo.Components
FOR DELETE
AS
INSERT INTO ComponentsDeleted (Iden, ComponentName, SerialNumber,
Comments,
UserName, DeletedDate, DeletedBy)
SELECT Iden, ComponentName, SerialNumber, Comments, UserName, getdate(),
SYSTEM_USER
FROM deleted

Удалите одну или две строки из таблицы Components. Теперь посмотрите таблицу ComponentsDeleted, и Вы увидите там удаленные Вами строки с датой и временем того, когда они были удалены.

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

SQL ,

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

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

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

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

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

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

Таблица blog

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

Таблица comments

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (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. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

Триггер базы данных – это оформленный специальным образом именованный блок PL/SQL, хранящийся в базе данных. Каждый триггер связан с определенной таблицей и автоматически запускается ORACLE при выполнении одного из DML-операторов (INSERT, DELETE, UPDATE) или их совокупности над этой таблицей.

Назначение триггеров. Триггеры могут быть использованы:

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

2) предотвращения неверных транзакций;

3) выполнения процедур комплексной проверки прав доступа и секретности данных;

4) генерации некоторых выражений на основе значений, имеющихся в столбцах таблиц;

5) реализации сложных бизнес-правил для обработки данных (возможность отследить «эхо», т.е. возможность при изменении одной таблицы, обновлять данные связанных с ней таблиц).

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

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

{BEFORE | AFTER}

{INSERT | DELETE | UPDATE }

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

< PL/SQL_блок >

При наличии ключевых слов OR REPLACE триггер создается заново, если он уже существует.

Конструкция BEFORE | AFTER указывает на момент запуска триггера. Вариант BEFORE означает, что триггер будет запускаться перед выполнением активизирующего DML-оператора; вариант AFTER означает, что триггер будет запускаться после выполнения активизирующего DML-оператора.

Конструкция INSERT | DELETE | UPDATE указывает тип активизирующего триггер DML-оператора. Разрешается, используя логическую операцию OR, задать совокупность активизирующих операторов, например: INSERT OR DELETE. Если при использовании варианта UPDATE указан список столбцов, то триггер будет запускаться при модификации одного из указанных столбцов; если список столбцов отсутствует, то триггер будет запускаться при изменении любого из столбцов связанной с триггером таблицы.

Конструкция FOR EACH ROW указывает на характер воздействия триггера: строковый или операторный. Если конструкция FOR EACH ROW присутствует, то триггер является строковым; при отсутствии ее триггер является операторным. Операторный триггер запускается один раз до или после выполнения активизирующего триггер DML-оператора независимо от того, сколько строк в связанной с триггером таблице подвергается модификации. Строковый триггер запускается один раз для каждой из строк, которая подвергается модификации DML-оператором, активизирующим триггер.

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

Конструкция PL/SQL_блок представляет блок PL/SQL, который ORACLE запускает при активизации триггера.

Классификация триггеров. В основном различают двенадцатьтипов триггеров. Тип триггера определяется сочетанием следующих трех параметров:

1) характером воздействия триггера на строки связанной с ним таблицы (строковый или операторный);

2) моментом запуска триггера: до (BEFORE) или после (AFTER) исполнения активизирующего триггер DML-оператора;

3) типом активизирующего триггер DML-оператора (INSERT, DELETE, UPDATE);

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

1) выполняется операторный триггер BEFORE (если их несколько, то ничего о порядке их выполнения сказать нельзя);

2) выполняется строковый триггер BEFORE;

3) выполняется активизирующий триггер DML-оператор с последующей проверкой всех ограничений целостности данных;

4) выполняется строковый триггер AFTER с последующей проверкой всех ограничений целостности данных;

5) выполняется операторный триггер AFTER.

Триггерные предикаты. Если в триггере указывается совокупность активизирующих триггер DML-операторов (например, INSERT OR DELETE), то для распознавания того, какой конкретно из DML-операторов выполняется над связанной с триггером таблицей, используются триггерные предикаты: INSERTING, DELETING, UPDATING. Они представляют собой логические функции, возвращающие TRUE, если тип активизирующего оператора совпадает с типом предиката, и FALSE – в противном случае. Для задания одних и тех же действий в случае выполнения различных DML-операторов в условном операторе триггерные предикаты объединяются с помощью логических операций.

Псевдозаписи. Для строковых триггеров существуют специальные конструкции, которые позволяют при выполнении DML-операторов над строкой таблицы, обращаться как к старым значениям, которые находились в ней до модификации, так и к новым, которые появятся в строке после ее модификации. Эти конструкции называются псевдозаписями и обозначаются old и new. Структура этих псевдозаписей идентична структуре строки модифицируемой таблицы, но оперировать можно только отдельными полями псевдозаписи. Обращение к полям псевдозаписи происходит по следующей схеме: перед old или new ставится символ «:», далее через точку указывается название поля. Значения, которые принимают поля псевдозаписи при выполнении активизирующих DML-операторов, определяются следующим образом.

Оператор INSERT – псевдозапись:new эквивалентна вставляемой строке, а псевдозапись:old во всех полях имеет значение NULL.

Оператор DELETE – псевдозапись:old эквивалентна удаляемой строке, а псевдозапись:new во всех полях имеет значение NULL.

Оператор UPDATE – псевдозапись:new эквивалентна строке, полученной в результате модификации, а псевдозапись:old во всех полях имеет исходное значение строки.

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

ALTER TRIGGER имя_триггера DISABLE;

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

ALTER TRIGGER имя_триггера ENABLE;

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

ALTER TABLE имя_таблицы {DISABLE | ENABLE} ALL TRIGGERS;

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

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

DROP TRIGGER имя_триггера;

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

SELECT * FROM USER_TRIGGERS;

Примеры.

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

Добавление новых строк в таблицу KNIGA_POSTAVKA выполняется оператором INSERT. Поскольку триггер должен запускаться перед выполнением каждого оператора INSERT, следовательно, он должен быть строковым BEFORE-триггером. Для сохранения целостности данных необходимо проверить, имеются ли вносимые коды книг и в таблице KNIGA. Для этого с помощью однострочного оператора SELECT осуществляется выборка информации из таблицы KNIGA, где в условии выборки используется поле КОД_КНИГИ псевдозаписи:new. Если количество строк с данным кодом книги в таблице KNIGA окажется равным нулю, будет сгенерировано исключение и выдано соответствующее сообщение.

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

CREATE OR REPLACE TRIGGER TR1

BEFORE INSERT ON KNIGA_POSTAVKA

SELECT COUNT(*) INTO KOL FROM KNIGA

WHERE КОД_КНИГИ = :NEW.КОД_КНИГИ;

IF KOL = 0 THEN RAISE_APPLICATION_ERROR

(–20212,"В таблице KNIGA нет информации о данной книге");

Действие триггера TR1 может быть проверено выполнением следующего оператора, осуществляющего вставку строки в таблицу KNIGA_POSTAVKA и тем самым вызывающих активизацию триггера TR2:

INSERT INTO KNIGA_POSTAVKA VALUES(21,15,’Иванов’,15,

Поскольку код книги 15 отсутствует в таблице KNIGA, то будет сгенерировано исключение и выдано соответствующее сообщение.

2. Создать триггер, который запрещает вносить в таблицу KNIGA строки со значением поля ЦЕНА больше, чем 5000 рублей, а также осуществлять увеличение цены книг, информация о которых хранится в таблице KNIGA, более чем на 20 %. При нарушении данного требования должно генерироваться исключение с выдачей соответствующего сообщения.

Так как внесение новых строк в таблицу KNIGA осуществляется в результате выполнения оператора INSERT, а значение поля ЦЕНА в таблице KNIGA, содержащего цену книги, может быть изменено в результате выполнения оператора UPDATE, то в триггере указывается совокупность запускающих DML-операторов. Поскольку триггер должен запускаться перед выполнением каждого из указанных DML-операторов, следовательно, он является строковым BEFORE-триггером. Так как действия, выполняемые триггером, различны для каждого из запускающих DML-операторов, модифицирующих таблицу KNIGA, то для распознавания типа DML-оператора используются соответствующие триггерные предикаты INSERTING и UPDAITING. Вследствие того что при вставке новых строк проверке должно быть подвергнуто новое значение поля ЦЕНА, а при модификации значения поля ЦЕНА новое значение должно сравниваться со старым значением, необходимо использовать псевдозаписи:new и:old.

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

CREATE OR REPLACE TRIGGER TR2

BEFORE INSERT OR UPDATE OF ЦЕНА ON KNIGA

IF INSERTING THEN

IF:NEW.ЦЕНА > 5000 THEN

RAISE_APPLICATION_ERROR

(–20102, "В таблицу KNIGA нельзя вносить записи с ценой книги > 5000");

IF UPDATING THEN

IF:NEW.ЦЕНА > :OLD.ЦЕНА*1.2 THEN

RAISE_APPLICATION_ERROR

(–20103, "В таблице KNIGA нельзя изменять цену книги более чем на 20 %");

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

Оператор вставки строк в таблицу KNIGA, вызывающие активизацию триггера TR2:

INSERT INTO KNIGA VALUES(21, "Дюна", "Герберт", 5268, "Аст",

"Фантастика");

Оператор обновления строк в таблице KNIGA, вызывающие активизацию триггера TR2:

UPDATE KNIGA SET ЦЕНА=6000;

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

3. Создать триггер, который в созданную таблицу STAT, содержащую столбцы:

имя издательства – IZD,

количество книг в жанре «Роман» – KOL_ROM,

количество книг в жанре «Фантастика» – KOL_FAN,

при каждой модификации таблицы KNIGA формирует и заносит в соответствующие столбцы таблицы STAT суммарное количество книг по каждому из издательств в разрезе указанных тематик: «Роман» и «Фантастика».

Модификация таблицы KNIGA осуществляется выполнением следующих DML-операторов: INSERT, DELETE или оператора UPDATE, модифицирующего значения столбца ЖАНР в таблице KNIGA. Так как действия по формированию информации таблицы STAT выполняются после выполнения каждого из модифицирующих таблицу KNIGA операторов, то по типу это операторный AFTER-триггер. Поскольку действия, выполняемые триггером, одинаковы для всех типов активизирующих его операторов, то триггерные предикаты не используются. Перед созданием триггера должна быть создана таблица STAT.

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

DROP TABLE STAT;

CREATE TABLE STAT

(IZD VARCHAR2(15),

KOL_ROM NUMBER(7),

KOL_FAN NUMBER(7)

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

CREATE OR REPLACE TRIGGER TR3

AFTER INSERT OR DELETE OR UPDATE OF ЖАНР

CURSOR V1 IS SELECT ИЗДАТЕЛЬСТВО,

COUNT(НАЗВАНИЕ) KOL1

FROM KNIGA WHERE ЖАНР = "Роман"

GROUP BY ИЗДАТЕЛЬСТВО;

CURSOR V2 IS SELECT ИЗДАТЕЛЬСТВО,

COUNT(НАЗВАНИЕ) KOL2

FROM KNIGA WHERE ЖАНР = "Фантастика"

GROUP BY ИЗДАТЕЛЬСТВО;

DELETE FROM STAT;

FOR Z1 IN V1 LOOP

INSERT INTO STAT VALUES(Z1.ИЗДАТЕЛЬСТВО,

FOR Z1 IN V2 LOOP

UPDATE STAT SET KOL_FAN = Z1.KOL2

WHERE IZD = Z1.ИЗДАТЕЛЬСТВО;

IF SQL%NOTFOUND THEN

INSERT INTO STAT VALUES(Z1.ИЗДАТЕЛЬСТВО, 0,

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

Операторы вставки строк в таблицу KNIGA, вызывающие активизацию триггера TR3:

INSERT INTO KNIGA VALUES(46, "Еретики Дюны", "Герберт",368,

"Аст", "Фантастика");

INSERT INTO KNIGA VALUES(42, "Ингвар и Ольха",

"Никитин",168, "Аст", "Роман ");

Операторы удаления строк из таблицы KNIGA, вызывающие активизацию триггера TR3:

DELETE KNIGA WHERE НАЗВАНИЕ = "Казаки";

Операторы модификации строк в таблице KNIGA, вызывающие активизацию триггера TR3:

UPDATE KNIGA SET ЖАНР="Фантастика" WHERE НАЗВАНИЕ =

"Ингвар и Ольха";

Просмотр информации в таблице STAT можно выполнить следующим оператором.

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