Организация Баз Данных и Знаний Лекция 11 Триггеры

Содержание лекции Назначение Использование вместо декларативной ссылочной целостности DRI Гибкая реализация ссылочной целостности Гибкие правила целостности данных Обновляемые представления (INSTEAD OF) Управление последовательностью запуска Вопросы быстродействия

Определение/свойства триггера специальный класс хранимой процедуры Содержат операторы T-SQL Отсутствие входных и выходных параметров Закрепляются за таблицей/представлением Нельзя создать для временной или системной таблицы Хранится в виде фрагментов кода запускается автоматически при изменении данных таблицы UPDATE, INSERT, DELETE Нельзя запустить вручную Обработка только журналируемых событий TRUNCATE TABLE не приведет к вызову триггеров

Сферы использования Поддержка ссылочной целостности декларативная ссылочная целостность не всегда применима Создание контрольного журнала запись промежуточных состояний данных (история) Реализация ограничений (проверок) Замена стандартного поведения СУБД Сложные обновляемые представления

Типы триггеров Вставки (INSERT) Удаления (DELETE) Обновления (UPDATE) Композитный Модификация данных Любое сочетание первых трех типов INSTEAD OF выполняется вместо операции вставки, обновления или удаления AFTER обеспечивает механизм управления порядком выполнения нескольких триггеров

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

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

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

Триггер обновления (FOR UPDATE) Активируется при обновлении существующих записей Рассматривается как операции удаления и вставки новых данных Измененные записи доступны в специальных таблицах называются INSERTED (для новых данных) и DELETED (для старых данных) видима только для триггера существует только на протяжении жизни триггера нет спец таблицы UPDATED UPDATE (имя колонки) Признак обновления данных в указанной колонке

Таблицы deleted и inserted Временные таблицы Хранятся в памяти Одинаковая структура с таблицей

Синтаксис создания CREATE TRIGGER <имя триггера> 1 ON <имя представления или таблицы> [WITH ENCRYPTION] 2 {{{FOR|AFTER} <[DELETE] [, ] [INSERT] [, ] [UPDATE]>} OF} [WITH APPEND] [NOT FOR REPLICATION] AS <оператор SQL. . . 4. . . > |INSTEAD 3

AFTER триггеры типа AFTER только для таблиц активизируется после успешного выполнения всех операций, включая Проверки по ссылкам Каскадные операции Если определено несколько триггеров для операции по таблице можно задавать порядок вызова, но только для первого Последнего С помощью sp_settriggerorder Остальные – в случайном порядке

$Порядок вызова триггеров Синтаксис sp_settriggerorder [@triggername =] 'имя_триггера', [@order=] {'first' | 'last' | 'none'}$

Порядок вызова триггеров Синтаксис sp_settriggerorder [@triggername =] 'имя_триггера', [@order=] {'first' | 'last' | 'none'} Примеры sp_settriggerorder go sp_settriggerorder 'none' go @triggername = 'Another. Trigger', @order = 'first' @triggername = 'My. Trigger', @order = 'last' @triggername = 'My. Other. Trigger', @order = 'none' @triggername = 'Yet. Another. Trigger', @order =

INSTEAD OF выполняется вместо запуска оператора SQL переопределяется действие запускающего оператора По одному триггеру на каждый тип операции Как для таблиц так и для представлений не с установленной опцией WITH CHECK

WITH ENCRYPTION Аналогичное поведение как и для Представлений хранимых процедур Сокрытие программного кода Удобно при создании коммерческого программного продукта особое внимание вопросам безопасности Рекомендуется создавать копии программного кода

WITH APPEND применяется только в режиме совместимости c версией 6. 5 устанавливаемом при помощи хранимой процедуры sp_dbcmptlevel Запуск нескольких триггеров одного типа для одной таблицы последовательно в режиме совместимости Для более новых версия в этом нет необходимости Поддерживаются несколько триггеров одного типа для таблицы

NOT FOR REPLICATION триггер не будет стартовать при выполнении над таблицей операций, связанных с репликацией данных

Вложенные триггеры sp_configure "nested triggers", 0 GO 1 - разрешает триггеры, которые активизируются другими триггерами 32 уровней вложенности Использование вложенных триггеров разрешается по умолчанию

Пример создания GO CREATE TABLE Bicycle_Inventory ( make_name char(10) NOT NULL, make_id tinyint NOT NULL, model_name char(12) NOT NULL, model_id tinyint NOT NULL, in_stock tinyint NOT NULL, on_order tinyint NULL) GO IF EXISTS (SELECT name FROM type = "TR") DROP TRIGGER Print_Update GO sysobjects WHERE name = "Print_Update" AND CREATE TRIGGER Print_Update ON Bicycle_Inventory FOR UPDATE AS PRINT "The Bicycle_Inventory table was updated" GO

Пример активации INSERT INTO Bicycle_Inventory VALUES ("Trek", 1, "5500", 5, 1, 0) GO UPDATE Bicycle_Inventory SET make_id = 2 WHERE model_name = "5500" GO Обычно триггер не возвращает данных

Пример DELETE триггера USE pubs GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "Delete_Title" AND type="TR") DROP TRIGGER Delete_Title GO CREATE TRIGGER Delete_Title ON titles FOR DELETE AS Эмуляция каскадного удаления (FK) DELETE sales FROM sales, deleted WHERE sales. title_id = deleted. title_id PRINT "Deleted from sales" DELETE roysched FROM roysched, deleted WHERE roysched. title_id = deleted. title_id PRINT "Deleted from roysched" DELETE titleauthor FROM titleauthor, deleted WHERE titleauthor. title_id = deleted. title_id PRINT "Deleted from titleauthor" GO 1 2 3

Активация Вызов DELETE WHERE GO titles title_id = "PC 1035" Результат (1 row(s) affected) Deleted from sales (5 row(s) affected) Deleted from roysched (1 row(s) affected) Deleted from titleauthor (1 row(s) affected)

Пример – сохранение копии удаленных строк USE pubs GO CREATE TABLE roysched_backup ( title_id tid NOT NULL, lorange int NULL, hirange int NULL, royalty int NULL) CREATE TRIGGER tr_roysched_backup ON roysched FOR DELETE AS INSERT INTO roysched_backup SELECT * FROM deleted GO SELECT * FROM roysched_backup GO

Пример INSERT триггера USE pubs GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "Update_ytd_sales" AND type = "TR") DROP TRIGGER Update_ytd_sales GO CREATE TRIGGER Update_ytd_sales ON sales FOR INSERT AS SELECT * FROM inserted UPDATE titles SET ytd_sales = ytd_sales + qty FROM inserted WHERE titles. title_id = inserted. title_id GO

Активация INSERT INTO sales VALUES(7066, 1, "March 7, 2000", 100, "Net 30", "BU 1111") GO Результат stor_id ord_num ord_date qty payterms title_id --------------------------------------------7066 1 2000 -03 -07 00: 00. 000 100 Net 30 BU 1111 (1 row(s) affected) С оператора UPDATE

Пример триггера UPDATE USE pubs GO IF EXISTS (SELECT name FROM sysobjects WHERE name = "Update_Price_Check" AND type = "TR") DROP TRIGGER Update_Price_Check GO CREATE TRIGGER Update_Price_Check ON titles FOR UPDATE AS DECLARE @orig_price money, @new_price money SELECT @orig_price = price from deleted PRINT "orig price =" PRINT CONVERT(varchar(6), @orig_price) SELECT @new_price = price from inserted PRINT "new price =" PRINT CONVERT(varchar(6), @new_price) IF (@new_price > (@orig_price * 1. 10)) BEGIN PRINT "Rollback occurred" ROLLBACK END ELSE PRINT "Price is OK" GO

Активация UPDATE titles SET price = price * 1. 15 WHERE title_id = "BU 1111" GO Результат orig price = (исходная цена) 11. 95 new price = (новая цена) 13. 74 Rollback occurred (Произошел откат)

Поддержка ссылочной целостности Работают медленней DRI Больше гибкость Примеры использования: Отношение один-к-одному Эксклюзивные подкатегории поддержка ограничений между разными базами данных, серверами

Вспомогательные процедуры Просмотр текста триггера sp_helptext Print_Update GO Какие триггеры определены для таблицы sp_helptrigger My. Table GO

Alter Trigger При модификации необходимо переопределить весь триггер Синтаксис похож на операцию создания Пример ALTER TRIGGER Print_Update ON Bicycle_Inventory FOR UPDATE, INSERT AS PRINT "Bicycle_Inventory was updated or a row was inserted" GO

Drop Trigger DROP TRIGGER имя_триггера При удалении таблицы удаляются все связанные с ней триггеры

Вкл. /выкл. триггеров Без удаления триггера Выключение ALTER TABLE имя_таблицы DISABLE TRIGGER имя_триггера Включение ALTER TABLE имя_таблицы ENABLE TRIGGER имя_триггера

Итоги Чрезвычайно гибкие Реагируют на события Могут управлять транзакциями Не возвращают данных (как правило) Не могут предотвращать изменение структуры БД Решение прикладных задач когда стандартные средства не подходят Ссылочная целостность Реализация бизнес правил Подготовка отчетов Общее управление системой …