Что такое код drop trigger


Содержание

DROP TRIGGER

Описание

DROP TRIGGER удаляет определенный пользователем триггер из базы данных. Определенные системой триггеры, такие как созданные для CHECK ограничения, не могут быть удалены инструкцией DROP TRIGGER. Используйте ALERT TABLE, чтобы удалить предложение CHECK, которое определяет триггер.

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

Триггер может быть удален его создателем и пользователем SYSDBA.

Совет: Чтобы сделать триггер временно не активным, используйте инструкцию ALTER TRIGGER и определите INACTIVE в заголовке.

DROP TRIGGER

Только начинаю изучать SQL, облазила весь интернет, но нужной мне информации не нашла, помогите пожалуйста!
Нужно сделать следующее: создать хранимую процедуру с выходным параметром, которая уничтожает все SQL DDL триггеры (триггеры типа ‘TR’) в текущей базе данных. Выходной параметр возвращает количество уничтоженных триггеров. Созданную хранимую процедуру протестировать.

Рекомендации.
1. Для уничтожения триггера воспользоваться инструкцией DROP TRIGGER.
2. Системное представление sys.triggers содержит по одной строке для каждого объекта, являющегося триггером типа TR или TA. Имена триггеров DML существуют в пределах схемы и, следовательно, видимы в представлении sys.objects. Область существования имен триггеров DDL определяется родительской сущностью, поэтому эти имена видимы только в этом представлении. Столбцы parent_class и name однозначно идентифицируют триггер в базе данных.

Я написала вот такую процедуру:

24.11.2013, 12:35

Trigger for insert
Приятного времени суток, уважаемые форумчане. Такой вопрос: Есть две связанные таблицы.

Trigger на добавление
Друзья, есть триггер: USE GO ****** Object: Trigger . SET ANSI_NULLS ON GO SET.

Trigger ms sql
Доброго времени суток. Вопрос вот в чем. Имеется две связанные таблицы. Столбец.

Insert trigger
Добрый день. Помогите, пожалуйста, написать триггер на вставку записи с предварительной проверкой.

After insert trigger
Помогите нужно применить его ,то есть показать как он работает желательно скрин или отчет .

Введение в триггеры MySQL

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

Введение : что такое триггер

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

Для тех, кто не знает, триггер — это правило, которое помещается вами в таблицу, и при выполнении DELETE, UPDATE или INSERT совершает дополнительные действия. Например, мы можем делать запись в журнале об изменении. Но вместо написания двух отдельных запросов (один — для изменения данных, другой для внесения записи в журнал), можно написать триггер, который будет содержать правило: “Когда бы ни изменялась строка, создать новую строку в другой таблице, чтобы сообщить, что были сделаны изменения”. Такой подход создает некоторую избыточность в основном запросе, но теперь нет проходов двух разных пакетов до сервера вашей базы данных, чтобы выполнить два разных действия, что в целом способствует улучшению производительности.

Триггеры были введены в MySQL начиная с версии 5.0.2. Синтаксис триггеров несколько чужероден. MySQL использует стандарт ANSI SQL:2003 для процедур и других функций. Если вы работаете с языками программирования, то понять его будет не сложно. Спецификация отсутствует в свободном доступе, поэтому мы постараемся использовать простые структуры и будем объяснять, что происходит в триггере. Будут использоваться такие же структуры, как и в любом языке программирования.

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

Начало: структура таблиц, инструменты и заметки

В статье мы будем работать с выдуманной системой для корзины покупок, каждый элемент которой будет иметь цену. Структура данных будет проста, насколько это возможно с целью продемонстрировать процедуры работы с триггерами. Наименования таблиц и столбцов придуманы с целью облегчения понимания, а не для реальной работы. Также используется TIMESTAMPS для облегчения учебного процесса. Таблицы имеют имена carts, cart_items, cart_log, items, items_cost.

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

Для определения времени выполнения использовался Particle Tree PHP Quick Profiler. Для иллюстрации эффектов на базе данных использовался Chive. Chive предназначен только для MySQL 5+ и очень похож на PHPMyAdmin. Он имеет более выразительный интерфейс, но содержит значительно больше ошибок на текущий момент. Использование Chive обусловлено желанием представить более выразительные скрин шоты запросов.

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

Чтобы изменить разделитель, нужно выполнить команду перед командой триггера:

А после команды триггера надо ввести:

Простой триггер : целостность данных

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

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

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

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

Очень простой синтаксис. Давайте разберем триггер подробно.

Первая строка“CREATE TRIGGER `tutorial`.`before_delete_carts`”. Это команда для MySQL создать триггер для базы данных “tutorial”, который будет иметь имя “before_delete_carts”. Будем использовать схему имен для триггеров “Когда_Что_Таблица”.

Вторая строка указывает для MySQL определение триггера “BEFORE DELETE ON `trigger_carts` FOR EACH ROW”. Мы говорим MySQL, что перед тем, как провести удаление из данной таблицы, для каждой строки нужно сделать что-то. Что нужно сделать, объясняется далее между BEGIN и END. “DELETE FROM trigger_cart_items WHERE OLD.cart_ >

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

Один запрос с триггером:

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

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

Чудесный простой триггер : журналирование и аудит

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

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

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

Первая строка “CREATE TRIGGER `after_insert_cart_items`”. Для MySQL задается команда, создать триггер с именем “after_insert_cart_items”. Имя может быть “Foo”, или “BullWinkle” или какое-то другое, но лучше использовать ранее описанную схему имен триггера. Далее следует “AFTER INSERT ON `trigger_cart_items` FOR EACH ROW”. Снова мы говорим, что после того, как что-то будет вставлено в trigger_cart_items, для каждой строки нужно выполнить операции между BEGIN и END.

Строка “INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);” является стандартным запросом с использованием двух переменных. Здесь используются значения NEW, которые вставляются в таблицу cart_items.

Снова выполнение нашего запроса осуществляется быстрее:

Для проверки, что триггер работает, посмотрим значения в таблице:

Более сложный триггер : бизнес логика

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

Бизнес логика — это место, где плодятся ошибки. Не смотря на осторожность и внимание к организации процесса, всегда что-то идет не так. Триггер для UPDATE позволяет несколько смягчить такое положение. У нас есть возможность в триггере вычислить значение OLD и установить значение NEW на основе оценки. Например, мы хотим всегда устанавливать цену на товар с 30% надбавкой к стоимости. Это приводит к тому, что когда мы изменяем (UPDATE) стоимость, мы должны изменить (UPDATE) цену. Давайте используем триггер.

Мы изменяем таблицу товаров с ценами, основанными на NEW.cost * 1.3. Если ввести стоимость $50, то цена должна быть $65.

Данный триггер работает отлично.

Давайте рассмотрим более сложный пример. У нас уже есть правило, которое изменяет цену товара на основе стоимости. Теперь мы хотим установить некоторую ярусность в ценах. Если цена меньше $50, то актуальное значение будет $50. Если цена больше $50, но меньше $100, то актуальное значение будет $100.

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

Вот текст триггера:

Это не запрос, а перекрытие значений. Если цена меньше $50, то устанавливаем ее $50. Если цена лежит между $50 и $100, то устанавливаем ее $100. Если она выше, то просто оставляем ее такой, какая она есть. Синтаксис не отличается от других серверных языков. Нужно закрыть выражение IF с помощью END IF.

Проверим работу нашего триггера. Если ввести значение стоимости $30, то цена должна быть $50:

Для значения стоимости $85:

Для проверки того, что триггер AFTER UPDATE все еще работает, цена должна быть $130:

Заключение

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

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: www.net.tutsplus.com
Перевел: Сергей Фастунов
Урок создан: 4 Июля 2010
Просмотров: 125470
Правила перепечатки

5 последних уроков рубрики «Разное»

Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

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

Разработка веб-сайтов с помощью онлайн платформы Wrike

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

20 ресурсов для прототипирования

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

Топ 10 бесплатных хостингов

Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

Быстрая заметка: массовый UPDATE в MySQL

Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

DROP TRIGGER

English-Russian SQL Server dictionary . 2013 .

Смотреть что такое «DROP TRIGGER» в других словарях:

Trigger (base de datos) — Un trigger (o disparador) en una Base de datos , es un procedimiento que se ejecuta cuando se cumple una condición establec >Wikipedia Español

Trigger — Триггер (англ. trigger) это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) по сути добавлением INSERT или удалением DELETE строки… … Википедия

Drop Dead Date — A provision in a contract or agreement that stipulates a finite deadline which, if not met, will automatically trigger adverse consequences. The drop dead date is the last possible date on which something must be completed. In most circumstances… … Investment dictionary

trigger point order — See also stop loss order / release order Euroclear Clearing and Settlement glossary Order that becomes val >Financial and business terms

trigger point order — See also stop loss order / release order Order that becomes val >Euroclear glossary

drop lock — A bond initially issued with a variable rate of interest that becomes a fixed rate bond if the index or rate falls below a predetermined trigger level … Accounting dictionary

drop lock — A bond initially issued with a variable rate of interest which becomes a fixed rate bond if the index or rate falls below a trigger at a coupon reset date … Big dictionary of business and management

Database trigger — A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example,… … Wikipedia

Schmitt trigger — In electronics, a Schmitt trigger is a comparator circuit that incorporates positive feedback.When the input is higher than a certain chosen threshold, the output is high; when the input is below another (lower) chosen threshold, the output is… … Wikipedia

Hare Trigger — Infobox Hollywood cartoon cartoon name = Hare Trigger series = Merrie Melodies caption = Title Card for the cartoon director = Friz Freleng story artist = Michael Maltese animator = Manuel Perez Ken Champin Virgil Ross Gerry Chiniquy Jack… … Wikipedia

Rate Trigger — A sizeable decline in interest rates that may trigger or cause companies to call in bonds that otherwise pay high coupon or interest rates. Because these bonds are being called before their initial expiration date, theoretically, bondholders can… … Investment dictionary

Тема 10. Триггеры как механизм поддержки

Изучить принципы применения, создания и отладки триггеров для баз данных в MS SQL Server 2000.

Оглавление

Задания

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

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

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

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

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

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

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

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

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

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

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

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

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

IF (columns_updated & 17) = 17


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

IF (columns_updated & 17) > 0

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TRIGGER Check_Job ON Jobs
FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e JOIN Deleted d ON e.job_ > BEGIN
ROLLBACK TRAN
END

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

/* Первый триггер уничтожает строки в таблице Stores,
если уничтожаются строки таблицы Sales */
CREATE TRIGGER Tri_Del_Sales
ON Sales
FOR DELETE
AS
/* Объявление выполняемого триггера */
PRINT ‘Выполняется триггер удаления для таблицы Sales. ‘
/* Объявление временной переменной для хранения удаляемой информации */
DECLARE @sStorID char(4),@sMsg varchar(40)
/* Получение значения ID удаляемой строки */
SELECT TOP 1 @sStor > FROM Deleted
/* Deleted — это вспомогательная таблица, которую SQL Server
использует для хранения уничтоженных записей */
/* Удаление строки */
SELECT @sMsg = ‘Магазин ‘ + @sStorID + ‘ удален’
PRINT @sMsg
DELETE FROM Stores
WHERE stor_ > PRINT ‘Конец выполнения триггера для таблицы Sales’
GO
/* Второй триггер уничтожает строки одной таблицы,
если уничтожаются строки другой */
CREATE TRIGGER Tri_Del_Stores
ON Stores
FOR DELETE
AS
/* Объявление выполняемого триггера */
PRINT ‘Выполняется триггер удаления для таблицы Stores. ‘
/* Объявление временной переменной для хранения информации,
уничтожаемой из таблицы */
DECLARE @sStorID char(4), @sMsg varchar (200)
/* Получение уничтожаемого значения */
SELECT TOP 1 @sStor > FROM Deleted
/* Deleted — это вспомогательная таблица, которую SQL Server
использует для хранения уничтоженных записей */
IF @@ROWCOUNT = 0
BEGIN
PRINT ‘В таблице Stores нет соответствующих строк’
RETURN
END
/* Удаление записи */
SELECT @sMsg = ‘Удаление скидок, относящихся к магазину ‘ + @sStorID
PRINT @sMsg
DELETE Discounts
WHERE Stor_ > PRINT ‘Количество удаленных скидок: ‘ + CONVERT(VARCHAR(4), @@ROWCOUNT)
PRINT ‘Конец выполнения триггера для таблицы Stores’

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

DELETE FROM Sales WHERE stor_

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

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

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

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

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

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

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

Fname Lname
Петр Васильев
Иван Иванов
Михаил Иванов
Иван Сергеев
Петр Сергеев

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

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

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

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

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

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

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

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

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

DELETE FROM Sales WHERE ord_num = ‘P723’

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

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

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

DELETE FROM stores WHERE stor_ >

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


DROP TRIGGER Tri_Del_Authors

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оператор DROP TRIGGER

Читайте также:

  1. Агрегатные функции, вложенные запросы в операторе выбора.
  2. В настоящее время причиной абсолютного большинства обрушений складских стеллажей является неправильная работа операторов напольной техники.
  3. Вложенные операторы select.
  4. Деятельность администраторов и операторов
  5. Для операторів основними предметами технічного оснащення є індикатори та органи управління.
  6. З параметром (оператор for).
  7. З передумовою (оператор while).
  8. Законодательные основы страхования ответственности туроператоров
  9. Класифікація витрат за видами діяльності туристичного оператора
  10. Коефіцієнт надійності діяльності оператора визначається за формулою
  11. Коммутаторы операторов. Условие совместной измеримости наблюдаемых. Полный набор наблюдаемых.
  12. Логические операторы

Оператор CREATE TRIGGER

DELETE FROM T1

DELETE FROM T1

Команда DELETE

Команда предназначена для удаления записей, удовлетворяющих условиям подзапроса, из указанной таблицы. Директива DELETE не может удалять столбцы (изменяет структуру таблицы команда ALTER TABLE) и не может удалить саму таблицу (DROP TABLE). Команда DELETE не может также удалить или очистить содержимое отдельных полей (UPDATE).

DELETE FROM T1;

DELETE FROM T1 WHERE ( >

WHERE (FIO LIKE ‘%Иванов%’) or (PASPORT = 786324);

WHERE (OKLAD

Однако этот триггер будет работать только, если генератор GEN_ID_T1 существует и имеет определенное значение:

CREATE GENERATOR GEN_ID_T1; SET GENERATOR GEN_ID_T1 to 1;

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

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

DROP TRIGGER ИМЯ ТРИГГЕРА;

После всего этого, была получена директива начальства: «Удалить из списка постоянных покупателей отдел ЦКБ за несвоевременность расчетов и малые объемы заказов». Подразумевается, что наше руководство отдает себе отчет в том, что будет произведено не только удаление самого покупателя, но и всех сделок, которые он совершил. Следующий оператор, разумеется, приведет к очевидной ошибке:

DELETE FROM T_Customer

WHERE (Name LIKE ‘%ЦКБ%’);

Корректной будет лишь следующая цепочка операторов:

DELETE FROM T_Sell WHERE (Customer_ >

DELETE FROM T_Customer WHERE ( >

Если такая ситуация повторяется периодически рационально разработать триггер (BEFORE DELETE), который перед удалением записи в T_Customer: будет производить удаление всех связанных записей в T_Sell.

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

NEW — это «новая» запись или ее новое состояние;

OLD — это «старая» запись или ее первоначальное состояние. Например, увеличим стоимость на 25 %

NEW.Cost = OLD.Cost *1,25

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

DELETE FROM T_Customer WHERE (Name LIKE ‘%ЦКБ%’);

15. Модели «Клиент-сервер» в технологии баз данных

В зависимости от взаимного расположения программы и хранилища данных можно выделить типы БД: локальные и удаленные. Для работы с локальными БД используются «локальные» приложения, а для работы с удаленными данными — «клиент — серверные» приложения.

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

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

1) Пользователь работает с локальной копией БД, содержимое которой обновляется при формировании нового запроса. При этом с сервера пересылается новая копия всей таблицы. В результате — резко возрастает нагрузка сети.

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

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

Ввиду столь явных недостатков была предложена совершенно другая архитектура, названная «клиент — сервер». В соответствии с этой архитектурой, удаленная БД размещается на сервере, а приложение, осуществляющее работу с ней на компьютере пользователя (клиенте). В результате система делится на две разнородные и обладающие разным функционалом части.

Клиентом называется не только компьютер пользователя, но и приложение, которое формирует и отсылает запрос удаленному серверу. Запрос формируется на языке SQL, который является стандартным средством доступа к реляционным данным. После получения запроса удаленный сервер направляет его SQL-серверу (серверу БД), который выполняет запрос и возвращает результат.

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

1) Необычайно снижается трафик сети;

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

3) Резкое уменьшение сложности клиентских приложений за счет отсутствия в них кода, связанного с контролем за состоянием БД и разграничения доступа к ней.

Для реализации архитектуры «клиент-сервер» используются многопользовательские СУБД (Oracle, Microsoft SQL Server). Описанная архитектура является двухуровневой. Клиентское приложение в ней называют «сильным» или «толстым» клиентом.

Развитие архитектуры «клиент-сервер» привело к появлению трехуровневой архитектуры, в которой кроме сервера и приложений клиентов присутствует еще и сервер приложений. Он является промежуточным звеном между сервером БД и клиентами.

Выгоды этой технологии очевидны:

1) Явное упрощение клиентских приложений и их настроек;

2) Разгрузка сервера БД (часть функций выполняет сервер приложений);

3) Единое поведение всех клиентов и независимость от платформы. Такие системы, основанные на трехуровневой сетевой архитектуре, называют также распределенными (см. аналогию с распределенной БД). Однако разработка таких систем достаточно проблемна, так как требует знания технологии MIDAS (Multi-tier Distributed Application Services) — службы многоуровневых распределенных приложений. Эта технология состоит из ряда элементов:

1) Удаленный брокер данных (Remote Data Broker) — интерфейс обмена данными между сервером приложений и клиентом;

2) Брокер бизнес — объектов (Business Objects Broker) — позволяет размешать сервер приложений одновременно на нескольких компьютерах;

3) брокер ограничений (Constraints Broker) — распределяет ограничения между отдельными уровнями системы.

Методология MIDAS поддерживает разработку приложений на следующих технологиях межпрограммного и межкомпьютерного взаимодействия:

1) DCOM (Distributed Component Object Model) — модель распределенных (удаленных) объектов;

2) MTS (Microsoft Transaction Server) — дополнение технологии СОМ для управлениями транзакциями;

3) CORBA (Common Object Request Broker Architecture) – взаимодействие между объектами, расположенными на различных платформах. В заключении можно сказать, что выбор наиболее подходящей архитектуры («локальной» — одноуровневой или «клиент-серверной» — многоуровневой) должен зависеть только от поставленной перед разработчиком задачи.

Работа технологии «клиент-сервер»

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

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

– Обеспечивается более широкий доступ к существующим базам данных.

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

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

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

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

– Эта архитектура весьма естественно отображается на архитектуру откры­тых систем.

Некоторые разработчики баз данных использовали эту архитектуру для организации средств работы с распределенными базами данных, т.е. с набором нескольких баз дан­ных, логически связанных и распределенных в компьютерной сети. Однако, несмотря на то, что архитектура «клиент-сервер» вполне может быть использована для организации распределенной СУБД, сама по себе она не образует распределенную СУБД.

Дата добавления: 2015-05-09 ; Просмотров: 196 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Как создавать и использовать триггеры в Mysql.

Автор: admin | 14 июня (Чт.) 2020г. в 12ч.10м.

Что такое триггеры в Mysql.

Планируем структуру базы данных.

Создадим для примера базу данных для блога. Нам понадобится две таблицы:

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

-`audit`: хранит базовый набор исторических изменений с идентификатором записи,
идентификатором сообщения блога, типом изменения (NEW, EDIT или DELETE) и датой/временем этого изменения.

Создадим таблицу `blog`:

Далее напишем sql код для создания нашей таблицы `audit`. Нам нужно назначить для всех полей индексы и оприделить внешний ключ
как audit.blog_id который ссылается на поле id из таблицы `blog`.

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

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

Теперь мы создаем два триггера:
Первый — когда новая запись создается в таблице `blog`, создаем для нее запись в таблице `audit`
c `blog_id` соответствующей id новой записи блога и типом ‘NEW’ из набора enum(‘NEW’,’EDIT’,’DELETE’).

Второй — когда запись в блоге обновляется, то добавляем запись в `audit` с типом ‘EDIT’ из набора enum(‘NEW’,’EDIT’,’DELETE’).
Время при этом устанавливается автоматически, в момент создания записи.

Основной синтаксис триггера:

Итак, нам требуется два триггера AFTER INSERT и AFTER UPDATE на событие в таблице `blog`.
Нет необходимости определять триггер DELETE, поскольку пост помечен как удаленный,
установив для поля `deleted` значение true.

Первая команда MySQL, которую мы напишем, это разделитель:
DELIMITER $$

Для нашего тела триггера требуется несколько команд SQL, разделенных точкой с запятой (;).
Чтобы создать полный код триггера, мы должны изменить разделитель на что-то другое, например $.

Создаем AFTER INSERT триггер. Тут мы вводим переменную @changetyp, которая будет хранить значение из
enum(‘NEW’,’EDIT’,’DELETE’) в зависимости от условия, какое значение установлено в blog.deleted:

На столбцы таблицы, к которой привязан триггер (в данном случае `blog`) можно ссылаться с помощью псевдонимов OLD и NEW.
NEW — это вновь созданная таблица. Поэтому NEW.deleted содержит значение этого столбца при создании записи в `blog`.

OLD.col_name — указывает на данные стрлбца до удаления или изменения данных при соответствующих событиях триггеров UPDATE/DELETE.

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

Триггер AFTER UPDATE почти идентичен:

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

Проверка работы триггера.

Новая запись появляется в таблице `blog`, как и следовало ожидать:

Кроме того, в нашей таблице `audit` появляется новая запись:

Давайте обновим наш текст в блоге:

Помимо изменения поста, в таблице «audit» появляется новая запись:

Наконец, давайте отметим пост как удаленный:

Соответственно обновляется таблица «audit», и у нас есть запись о произошедших изменениях:


Вот так. Все работает и триггеры отлично справляются со своей задачей.

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

Приветствую!

Меня зовут Сергей. Я — автор этого блога.

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

3.4. Триггеры

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

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

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

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

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

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

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

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

Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

Листинг 3.2. Общий вид команды CREATE TRIGGER

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

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

  • ALTER DATABASE;
  • CREATE DATABASE;
  • DISK INIT;
  • DISK RESIZE;
  • DROP DATABASE;
  • LOAD DATABASE;
  • LOAD LOG;
  • RECONFIGURE;
  • RESTORE DATABASE;
  • RESTORE LOG.

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

3.4.2. Откат изменений в триггере

Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера — откатываются.

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

  • Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
  • Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
  • Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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

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

  • имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
  • после подчеркивания идет имя таблицы, для которого создается триггер.

После имени идет ключевое слово ON и имя таблицы, для которой создается триггер.

Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда — ROLLBACK TRANSACTION, т.е. откат.

Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:

В данном примере мы пытаемся изменить содержимое поля «vcFamil» для всех записей таблицы tbPeoples. Почему пытаемся? Да потому что при изменении срабатывает триггер с откатом транзакции. Выполните выборку данных, чтобы убедиться, что все данные на месте и не изменились:

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

3.4.3. Изменение триггера

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

Для обновления триггера используется оператор ALTER TRIGGER. Общий вид оператора можно увидеть в листинге 3.3.

Листинг 3.3. Оператор обновления триггера

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

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

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

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

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

В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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

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

Для удаления триггера вы можете воспользоваться оператором DROP TRIGGER. Он удаляется автоматически, когда связанная с ним таблица удаляется.

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

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

3.4.5. Как работают триггеры?

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

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

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

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

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

Таблица inserted всегда содержит такую же структуру, что и у таблицы, на которую установлен триггер.

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

Листинг 3.4. Использование таблицы inserted

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля «vcName» таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля «vcName». Код такого триггера можно увидеть в листинге 3.5.

Листинг 3.5. Запрет нулевых значений в поле с помощью триггера

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля «vcName», то откатываем попытку добавления.

Триггер DELETE

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

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

Удаляемые строки помещаются в таблицу deleted, с помощью которой вы можете увидеть удаляемые строки. Это логическая таблицf, которая ссылается на данные журнала оператора DELETE.

Вы должны учитывать:

  • когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
  • для таблицы deleted выделяется память, поэтому она всегда в кэше;
  • триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

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

Листинг 3.6. Пример запрета удаления с помощью триггера

В этом примере мы проверяем, если в таблице deleted существует запись с именем «рлр», то откатываем удаление. Добавьте в таблице запись с именем «рлр» и попытайтесь ее удалить. В ответ вы должны увидеть ошибку.

А что если попытаться удалить несколько записей? Например, в следующем примере удаляются записи две записи:

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

Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:

В этом примере, запрещается удаление записи, если поле «idPosition» равно 1. Попробуйте удалить такую запись:

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

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

Триггер UPDATE

Обновление происходит в два этапа – удаление и вставка. Нет, физически в базе данных происходит изменение, это триггер видит два этапа. Поэтому существующие строки помещаются в таблицу deleted (то есть то, что было), а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

Вы можете объявить триггер для мониторинга обновления определенного поля с помощью указания опции IF UPDATE. Это позволяет триггеру изолировать активность определенной колонки. Когда обнаруживается обновление определенной колонки, триггер может выполнить определенные действия, такие как выброс сообщения об ошибке, которое сообщит о невозможности обновления колонки.

Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле «vcName»

После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

Следующий запрос тестирует триггер:

Убедитесь, что сообщение из триггера выводится на экран.

Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО («vcFamil», «vcName» и «vcSurName»). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:

С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

3.4.6. INSTEAD OF

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

Каждая таблица или просмотрщик ограничены одним триггером INSTEAD OF на каждое событие. Вы не можете создавать триггеры INSTEAD OF на просмотрщик у которого включена опция CHECK OPTIONS.

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

Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:

Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

Листинг 3.7. Триггер INSTEAD OF для вставки данных

В этом примере интересности начинаются прямо со второй строки. Здесь указывается оператор INSTEAD OF и событие, на которое нужно реагировать. В данном случае в качестве события выступает вставка (INSERT).

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

Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:

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

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

Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:

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

Листинг 3.8. Обновление связанной вьюшки с помощью триггера

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

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

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

3.4.7. Дополнительно о триггерах

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

  • Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
  • Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
  • Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.


При разработке триггеров, вы должны учитывать, что таблицы могут иметь несколько триггеров для любого действия. Каждый триггер может быть объявлен для нескольких или одного действия. Например, в следующем примере обрабатывается два события INSERT и UPDATE:

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

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

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

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

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

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

3.4.8. Практика использования триггеров

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

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

Итак, давайте создадим триггер, который при изменении или удалении строк в таблице tbPeoples будет копировать их в таблицу истории tbpeoplesHistory. Если бы первичный ключ был в виде уникального идентификатора, то задача решалась бы следующим образом:

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

В данном примере содержимое таблицы Deleted копируется в таблице tbPeoplesHistory. Запрос упрощается тем, что первичный ключ можно сгенерировать с помощью функции newid().

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

Теперь посмотрим, как можно запретить удаление более чем одной строки:

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

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

Триггеры могут иметь вложения до 32 уровней. Если какой-нибудь триггер зациклится, то будет превышен предел. Триггер прерывается и транзакция откатывается.

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

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

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

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

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

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

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

  1. При добавлении записи в таблицу телефонов увеличиваем значение поля в таблицы работников;
  2. При удалении номера телефона, уменьшаем значения поля.

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

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

Для определения, какие триггеры существуют на определенную таблицу, и на какие действия выполните процедуру sp_helptrigger. Следующий пример отображает все триггеры, которые принадлежат объекту просмотра People (если нужно просмотреть триггеры таблицы, то укажите ее имя):

Для просмотра кода существующего триггера используйте sp_helptext. Например, следующая команда позволяет увидеть текст триггера u_People, которую мы создавали для объекта просмотра:

DROP TRIGGER

Use the DROP TRIGGER statement to remove a database trigger from the database.

The trigger must be in your own schema or you must have the DROP ANY TRIGGER system privilege. To drop a trigger on DATABASE in another user’s schema, you must also have the ADMINISTER DATABASE TRIGGER system privilege.

Specify the schema containing the trigger. If you omit schema , then Oracle Database assumes the trigger is in your own schema.

Specify the name of the trigger to be dropped. Oracle Database removes it from the database and does not fire it again.

Dropping a Trigger: Example The following statement drops the salary_check trigger in the schema hr :

Триггеры

— это механизм, который вызывается, когда в указанной таблице происходит определенное действие. Каждый триггер имеет следующие основные составляющие: имя, действие и исполнение. Имя триггера может содержать максимум 128 символов. Действием триггера может быть или инструкция DML (INSERT, UPDATE или DELETE), или инструкция DDL. Таким образом, существует два типа триггеров: триггеры DML и триггеры DDL. Исполнительная составляющая триггера обычно состоит из хранимой процедуры или пакета.

Компонент Database Engine позволяет создавать триггеры, используя или язык Transact-SQL, или один из языков среды CLR, такой как C# или Visual Basic.

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

Триггер создается с помощью инструкции CREATE TRIGGER, которая имеет следующий синтаксис:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Триггеры 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.)

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

В этом примере используется таблица 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 показано в примере ниже:

Для таблицы разрешается определить только один первый и только один последний триггер 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 имеют следующий синтаксис:

Как можно видеть по их синтаксису, триггеры 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, чья область действия распространяется на текущую базу данных:

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

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

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

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

Здесь сначала создается имя входа 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.

Пространство имен 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. Следующий шаг состоит в добавлении ссылки на скомпилированную сборку в базе данных:

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

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

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

Илон Маск рекомендует:  Уроки MS Excel
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL