Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Триггер в программировании что это

Триггеры: создание и применение

Реализация триггеров в среде MS SQL Server

В реализации СУБД MS SQL Server используется следующий оператор создания или изменения триггера :

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

Рассмотрим назначение аргументов из команды CREATE | ALTER TRIGGER .

Имя триггера должно быть уникальным в пределах базы данных . Дополнительно можно указать имя владельца.

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

Типы триггеров

В SQL Server существует два параметра, определяющих поведение триггеров :

  • AFTER . Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера , то будут отклонены и пользовательские изменения. Можно определить несколько AFTER -триггеров для каждой операции ( INSERT , UPDATE , DELETE ). Если для таблицы предусмотрено выполнение нескольких AFTER -триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER -триггерами.
  • INSTEAD OF . Триггер вызывается вместо выполнения команд. В отличие от AFTER -триггера INSTEAD OF -триггер может быть определен как для таблицы, так и для представления. Для каждой операции INSERT , UPDATE , DELETE можно определить только один INSTEAD OF -триггер.

Триггеры различают по типу команд, на которые они реагируют.

Существует три типа триггеров :

  • INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT .
  • UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE .
  • DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE .

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF > триггер . При его создании должна быть указана хотя бы одна команда. Допускается создание триггера , реагирующего на две или на все три команды.

Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.

При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.

Конструкция AS sql_оператор[. n] определяет набор SQL- операторов и команд, которые будут выполнены при запуске триггера .

Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как:

  • создание, изменение и удаление базы данных;
  • восстановление резервной копии базы данных или журнала транзакций.

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

Программирование триггера

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

  • команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;
  • команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;
  • команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера . Новые значения строк содержатся в таблице inserted . Эти строки добавятся в исходную таблицу после успешного выполнения триггера .

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

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

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

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE , вызвавших выполнение триггера , можно использовать функцию COLUMNS_UPDATED() . Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение «1», то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца).

Триггер (базы данных)

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

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

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

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

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Читать еще:  Моделирование задачи линейного программирования

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

Содержание

SQL синтаксис определения триггеров

Полный синтаксис выглядит так:

Естественно, в языке имеется и конструкция, отменяющая определение триггера:
DROP TRIGGER trigger_name. (Конструкция ALTER TRIGGER в языке SQL не поддерживается.)

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

Триггеры BEFORE и AFTER

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

Триггеры INSERT, UPDATE и DELETE

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

Заметим, что в стандарте SQL:1999 отсутствует возможность определения триггеров, для которых событием было бы выполнение операции выборки из предметной таблицы. Разработчики стандарта сочли, что область применения триггеров такого рода чересчур узка (трудно придумать какое-либо применение, кроме как для журнализации и аудита).

Триггеры ROW и STATEMENT

Если в определении триггера присутствует конструкция FOR EACH ROW , то триггер будет вызываться для каждой строки предметной таблицы, обновляемой инициирующим SQL-оператором. Если же задано FOR EACH STATEMENT (или явная спецификация FOR EACH отсутствует), то триггер сработает один раз на всем протяжении процесса выполнения инициирующего SQL-оператора.

Раздел WHEN

Включение в определение триггера раздела WHEN с соответствующим условным выражением позволяет более точно специфицировать условие применимости триггера. Вычисление условного выражения производится над строками предметной таблицы, и триггер срабатывает только в том случае, когда значением условного выражения является true . Понятно, что виды и интерпретация логических выражений, допускаемых в разделе WHEN , различаются у триггеров с FOR EACH ROW и у триггеров с FOR EACH STATEMENT . В первом случае условное выражение вычисляется для одной строки, которая должна быть обновлена инициирующим SQL-оператором. Во втором – условное выражение вычисляется для всей предметной таблицы целиком и, по всей видимости, должно базироваться на «кванторных» предикатах. Следует также понимать, что вычисление условия раздела WHEN данного триггера производится только в том случае, если произошло событие срабатывания триггера.

Тело триггера

Операции, которые должны быть выполнены при срабатывании триггера, специфицируются в синтаксической конструкции triggered_SQL_statement (будем называть ее инициируемым SQL-оператором).Как видно из синтаксических правил, возможны два вида построения этой конструкции: в виде одиночного оператора SQL и в виде списка операторов со скобками BEGIN ATOMIC и END .

Недоумение читателей может вызвать неуточненная конструкция SQL_procedure_statement . Постараемся объяснить ее происхождение и смысл. Дело в том, что в стандарте SQL:1999 определено процедурное расширение SQL, называемое SQL/PSM (от Persistent Stored Modules ). Это достаточно большой язык, который мы не будем подробно рассматривать в этом курсе лекций. Тем не менее для понимания синтаксиса определения триггеров необходимо отметить, что: (a) SQL/PSM включает основные операторы SQL, связанные с обновлением данных; (b) язык является вычислительно полным, т.е. включает развитые средства вычислений; (c) в языке содержатся средства определения и вызова функций ипроцедур, и (d) SQL/PSM содержит стандартный комплект управляющих конструкций – циклы, ветвления разных типов и т. д. Тем самым, SQL_procedure_statement – это любая процедура, определенная на языке SQL/PSM. В частности, эта процедура может представлять собой оператор SQL обновления базы данных.

Простой синтаксис создания триггера

  • trigger_name — название триггера
  • trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события.
  • trigger_event — Событие:
  • insert — событие возбуждается операторами insert, data load, replace
  • update — событие возбуждается оператором update
  • delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера
  • tbl_name — название таблицы
  • trigger_stmt выражение, которое выполняется при активации триггера

Примеры

В этом случае для отличия табличных триггеров от строчных вводится дополнительные ключевые слова при описании строчных триггеров. В Oracle это словосочетание FOR EACH ROW.

Подробнее о триггерах

История появления триггеров

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

Конечно, термин триггер в данном контексте является жаргонным. Но, с другой стороны, он достаточно точно соответствует ситуации: для применения процедуры должны быть произведены «возбуждающие» ее действия. Как отмечалось в лекции 15, после завершения проекта System R на протяжении более десяти лет триггеры не поддерживались ни в одной коммерческой SQL-ориентированной СУБД. Но затем практически во всех ведущих СУБД механизм триггеров в том или ином виде был реализован.

В стандарте же языка SQL спецификации триггеров отсутствовали до принятия стандарта SQL:1999. По словам главного редактора стандартов SQL/92 и SQL:1999 Джима Мелтона, эта спецификация была уже полностью готова к моменту принятия SQL/92 и не вошла в текст стандарта только по причине ограниченности его объема. Однако, как мне кажется, этому препятствовали и расхождения в подходах, существовавшие между основными компаниями-производителями СУБД.

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

Понятие триггера в SQL:1999

В языке обеспечиваются возможности определения триггеров, которые вызываются («срабатывают») при вставке одной или нескольких строк в указанную таблицу, при модификации одной или нескольких строк в указанной таблице или при удалении одной или нескольких строк из указанной таблицы. Вообще говоря, триггер может производить любое действие, необходимое для соответствующего приложения. Можно определить триггеры, срабатывающие по одному разу для операций INSERT , UPDATE или DELETE , но существует и возможность определения триггеров, вызываемых при вставке, модификации или удалении каждой отдельной строки. Таблица, с которой связывается определение триггера, называется предметной таблицей (subject table), а оператор SQL, выполнение которого приводит к срабатыванию триггера, мы будем называть инициирующим (triggering SQL statement).

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

Читать еще:  Программируемый контроллер прерываний

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

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

На практике при определении триггеров в качестве SQL_procedure_statement чаще всего используются операторы SQL обновления базы данных. Иногда (и мы покажем это на примере) для корректного определения функциональности триггера одного оператора не хватает, а в SQL отсутствует возможность определения составных операторов. Поэтому допускается использование средств определения составных операторов, присутствующих в SQL/PSM ( BEGIN ATOMIC и END ).

Как создать триггер на Transact-SQL

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

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

Определение и назначение триггера

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

  • INSERT – добавление записей в таблицу;
  • DELETE – удаление записей из таблицы;
  • UPDATE – обновление записей таблицы.

С определением разобрались, теперь давайте разберемся, для чего нужны эти триггеры. А нужны они всего лишь для одной цели, выполнение дополнительных действий до или после самой sql инструкции в которой присутствуют вышеперечисленные события. Имеется в виду, Вы сами можете указать, когда выполнять и на какое событие реагировать. Вы можете написать триггер сразу на все события (insert, delete, update) а можете только на одно или на два.

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

Пример создания триггера в MS SQL 2008

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

Мы с Вами уже реализовывали подобную задачу, которую рассматривали в материале Журналирование изменений данных в таблице на Transact-SQL. Но там мы не использовали триггеры, и использовали своего рода импровизированный метод хранения всех изменений, но сегодня как в материале Transact-sql – работа с xml мы будем использовать XML данные именно в триггере.

Примечание! Все действия мы будем выполнять в Management Studio SQL сервера.

И для начала, давайте создадим таблицу, на которую будем вешать триггер:

Код создания таблицы:

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

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

Код создания таблицы audit:

  • id – это идентификатор с автоинкрементном;
  • table_name – название таблицы, в которой произошло изменение;
  • oper – операция, в данном случае у нас туда будет записывать update;
  • record_old – xml данные старой строки;
  • record_new — xml данные новой строки;
  • username – кто изменил;
  • date_ch – когда изменили.

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

CREATE TRIGGER название триггера ON таблица на которую вешать

FOR на какие операции (update, insert, delete)

AS

BEGIN

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

END

Теперь пришло время привести пример кода самого триггера. SQL инструкции я прокомментировал.

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

И посмотрим, что у нас появилось в таблице audit.

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

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

И проверяем audit

Как видите у нас добавилась всего одно строка, но мы же обновили две строки, все очень просто все наши данные записались в xml документ, и соответственно в record_old хранятся старые две строки, а в record_new измененные две строки, т.е. сколько бы мы не обновили строк они будут отображены в xml документе. И теперь давайте все-таки нажмем на xml данные и посмотрим что будет.

А здесь отображены все строки, которые мы затронули.

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

Заметка! Для профессионального изучения языка T-SQL рекомендую пройти наши онлайн-курсы по T-SQL.

Что такое триггер?

Дата добавления: 2013-12-23 ; просмотров: 1088 ; Нарушение авторских прав

Читать еще:  Алгоритм в программировании это

Что такое триггер?

Введение

Цели урока

Урок 3: Реализация триггеров

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

■ Определять триггеры DML.

■ Описывать, как работает триггер вставки INSERT.

■ Описывать, как работает триггер удаления DELETE.

■ Описывать, как работает триггер обновления UPDATE.

■ Описывать, как работает триггер INSTEAD OF.

■ Описывать, как работают вложенные триггеры.

Приводить основные сведения о рекурсивных триггерах.

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

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

Рассмотрим следующие факты о триггерах:

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

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

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

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

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

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

Выполнение триггеров в определенном порядке

Введение

Проблема, с которой я столкнулся, довольно известна. Я имею два триггера, которые должны отработать в предопределенном порядке, т.е. триггер a должен выполниться сначала, а после него должен отработать триггер b. Вы можете поинтересоваться, а почему бы не иметь один триггер, который объединит триггеры a и b в один триггер ab? Хороший вопрос. К сожалению, триггер a используется для репликации (for replication), в то время как более поздний триггер — не для репликации, что определяет наличие именно двух триггеров.

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

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

Последняя строка триггера журнализирует имя триггера и время его срабатывания в таблице triggerlog. Следующий триггер используется для обновления столбца second значением из столбца first.

Последний триггер используется для обновления столбца last суммой значений столбцов first и second.

Теперь, чтобы получить ожидаемые результаты, триггеры trg_updatefisrt, trg_updatesecond и trg_updatelast должны выполняться в вышеперечисленном порядке. Что вы об этом думаете? Каков будет порядок? Случайным образом или в некотором особом порядке?

Прежде чем ответить на этот вопрос, давайте посмотрим, что произойдет. После вставки одной записи в таблицу [trigger priority] первый столбец содержит пятерку, что нормально, и второй тоже — 5, что также правильно. Однако в последнем столбце находится null! Почему? Разве не должно быть 10?

Теперь давайте проверим таблицу triggerlog. Порядок столбцов — trg_updatelast, trg_updatefirst и trg_updatesecond. После небольшого исследования выясняется, что триггеры выполняются в том порядке, в котором они были созданы. Таким образом, в идеале триггеры следует создавать в таком порядке: trg_updatefirst, trg_updatesecond и trg_updatelast. Это ни в коем случае не является простой задачей в силу динамического характера процесса разработки, который по большей части не контролируется разработчиками.

Другой вопрос. Как на более поздней стадии Вы собираетесь узнать о порядке срабатывания триггеров?

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

Установить порядок

Теперь вопрос о том, как мы можем задать порядок выполнения. Имеется хранимая системная процедура, которая для того и существует, чтобы ответить на подобный вопрос. Эта хранимая процедура — sp_settriggerorder. У этой sp есть три параметра.

Первый параметр — это имя триггера, а второй параметр — порядок. Этот порядок может принимать одно из трех значений: » first (первый)», «none (ни первый, ни последний)», и » last (последний)». Последний параметр представляет собой тип триггера, т.е. insert, update или delete. Это означает, что Вы не можете позволить себе иметь четыре или пять триггеров одного и того же типа, которые бы выполнялись в определенном порядке. Однако это вряд ли встречается в практике. По крайней мере, я не встречал еще так много триггеров одного типа на одной таблице.

Этот порядок не может быть установлен опциями alter trigger или create trigger. Если оператор alter trigger изменяет первый или последний триггер, то первоначально установленные на триггере атрибуты first или last удаляются, и значение заменяется на none. Значение порядка должно быть переустановлено с помощью хранимой процедуры sp_settriggerorder.

Разрешения

Владелец триггера и таблицы, на которой определен триггер, имеет разрешение на выполнение sp_settriggerorder. Члены ролей db_owner и db_ddladmin в текущей базе данных, так же как серверная роль sysadmin, могут выполнять эту хранимую процедуру.

Получение порядка

Следующая проблема заключается в установлении порядка, в котором выполняются триггеры, на более поздней стадии. Если я не ошибаюсь, нет никакого прямого способа получить эту информацию из enterprise manager sql server. Вместо этого Вы пишете простые запросы.

укажет, является ли trg_updatefirst первым (first) триггером на вставку?

Предостережения

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

Сервер sql 2005

В sql server 2005 имеется дополнительный параметр в sp_settriggerorder, который должен сообщить, является ли данный триггер триггером базы данных или триггером сервера. Это обусловлено тем, что в sql server 2005 Вы можете написать также ddl триггеры.

Заключение

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

Ссылка на основную публикацию
Adblock
detector