Как запустить триггер sql

Использование триггеров в СУБД MySQL

Триггер (англ. trigger) — это хранимая откомпилированная SQL-процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события внутри базы данных (вставки, удаления, обновления записей). Поддержка триггеров в MySQL началась с версии 5.0.2

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

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

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

Общий вид синтаксиса для создания триггера:

CREATE [DEFINER = < user | CURRENT_USER >] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

Где
trigger_name — название триггера;
trigger_time — время срабатывания триггера. BEFORE — перед событием. AFTER — после события;
trigger_event — событие:

insert — событие возбуждается операторами insert, data load, replace;
update — событие возбуждается оператором update;
delete — событие возбуждается операторами delete, replace. Операторы DROPTABLE и TRUNCATE не активируют выполнение триггера;
tbl_name — название таблицы;
trigger_body — выражение, которое выполняется при активации триггера.

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

Пример: создадим две таблицы test и log, напишем триггер, который после добавления каждой записи в 1-ю таблицу будет вести лог этого события:

-- таблица, за которой мы будем следить CREATE TABLE `test` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `content` TEXT NOT NULL ); -- лог CREATE TABLE `log` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `msg` VARCHAR( 255 ) NOT NULL, `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `row_id` INT( 11 ) NOT NULL ); -- триггер DELIMITER | CREATE TRIGGER `update_test` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO log Set msg = 'insert', row_id = NEW.id; END;

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

На столбцы таблицы, к которой привязан триггер можно ссылаться с помощью псевдонимов OLD и NEW. OLD.col_name указывает на столбец с именем col_name до изменения или удаления данных. NEW.col_name относится к колонке новой строке после вставки или существующей — сразу после её обновления.

Для удаления триггера необходимо выполнить запрос:

DROP TRIGGER `update_test`;

Для просмотра триггеров в базе данных используется оператор:

SHOW TRIGGERS;

Триггеры имеют несколько важных особенностей использования:

триггеры в MySQL 5 могут создаваться только пользователем с привилегией SUPER;
при использовании запроса, затрагивающего N — записей, триггер будет запускаться N — раз;
после удаления таблицы, СУБД MySQL автоматически удаляет привязанные к ней триггеры.

Список использованный источников

Официальная документация MySQL (http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html)
Habrahabr.ru — Триггеры в MySQL (http://habrahabr.ru/blogs/mysql/37693/)
Википедия (http://ru.wikipedia.org/wiki/Триггер_(базы_данных)

При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на handyhost.ru в качестве источника.

МИР Visa MasterCard СБП QIWI Wallet Безналичный платеж

Все способы

Услуги
Хостинг сайтов
Домены
Конструктор сайтов
Linux VPS / Windows VPS
Выделенные серверы
SSL сертификаты

Клиентам
Контакты
О компании
Акции
Оборудование
Партнерская программа

Поддержка
Способы оплаты
Регламент
Документы
Справка

ВКЛЮЧЕНИЕ ТРИГГЕРА

При наличии отключенного триггера вам может понадобиться его включить. Вы можете сделать это с помощью команды ALTER TRIGGER.

Синтаксис

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

Параметры и аргументы
имя_триггера наименование триггера, который вы хотите включить.

Примечание

Смотрите также, как включить все триггеры таблицы.
Смотрите также, как отключить триггер таблицы или отключить все триггеры таблицы.

Пример

Рассмотрим пример, который показывает, как включить триггер в Oracle.

ALTER TRIGGER orders_before_insert ENABLE;

Этот пример использует команду ALTER TRIGGER, для включения триггера orders_before_insert.

CREATE TRIGGER

Команда CREATE TRIGGER создает новый триггер. CREATE OR REPLACE TRIGGER либо создаст новый триггер, либо заменит существующий. Этот триггер будет связан с заданной таблицей, представлением или сторонней таблицей и будет выполнять указанную функцию имя_функции при выполнении определенных типов операций с этой таблицей.

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

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

Триггер, помеченный как FOR EACH ROW, вызывается один раз для каждой строки, которую изменяет операция. Например, операция DELETE , которая затрагивает 10 строк, вызовет срабатывание всех триггеров ON DELETE для целевого отношения 10 раз подряд: по одному разу для каждой удаляемой строки. И наоборот, триггер с указанием FOR EACH STATEMENT срабатывает только один раз для любой заданной операции, независимо от того, сколько строк она изменяет (в частности, операция, которая не изменяет ни одной строки, все равно приведет к выполнению всех применимых триггеров FOR EACH STATEMENT).

Триггеры, предназначенные для запуска вместо события, т. е. INSTEAD OF, должны быть помечены как FOR EACH ROW и могут быть определены только для представлений. Триггеры BEFORE и AFTER для представления должны быть помечены как FOR EACH STATEMENT.

Кроме того, триггеры могут быть определены для операции TRUNCATE , но только с указанием FOR EACH STATEMENT.

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

Когда	Событие	На уровне строк	На уровне оператора
BEFORE	INSERT/UPDATE/DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
BEFORE	TRUNCATE	—	Таблицы
AFTER	INSERT/UPDATE/DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
AFTER	TRUNCATE	—	Таблицы
INSTEAD OF	INSERT/UPDATE/DELETE	Представления	—
INSTEAD OF	TRUNCATE	—	—

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

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

Если указан параметр CONSTRAINT, команда создает триггер ограничения. Он такой же, как и обычный триггер, за исключением того, что время его срабатывания можно регулировать с помощью команды SET CONSTRAINTS . Триггеры ограничений должны создаваться с указанием AFTER ROW для обычных таблиц (не для сторонних). Они могут срабатывать либо в конце оператора, вызывающего целевое событие, либо в конце содержащей оператор транзакции; в последнем случае они считаются отложенными. Также срабатывание ожидающего отложенного триггера можно вызывать принудительно с помощью команды SET CONSTRAINTS . Ожидается, что триггеры ограничений должны генерировать исключение, когда нарушаются реализуемые ими ограничения.

Если указан параметр REFERENCING, для триггера собираются переходные отношения, которые представляют собой наборы строк, включающие все строки, которые были добавлены, удалены или изменены текущим оператором SQL. Эта функциональность позволяет триггеру иметь глобальное представление о том, что сделал оператор, а не только об одной строке за раз. Это указание допускается только для триггера AFTER, который не является триггером ограничения; кроме того, если такой триггер является триггером UPDATE , у него должен отсутствовать список имен_столбцов. Указание OLD TABLE можно задать только один раз и только для триггера, который срабатывает для операций UPDATE или DELETE ; это указание создает переходное отношение, которое содержит образы до изменения всех строк, измененных или удаленных оператором. Аналогично указание NEW TABLE можно задать только один раз и только для триггера, который срабатывает для операций UPDATE или INSERT ; это указание создает переходное отношение, которое содержит образы после изменения всех строк, измененных или добавленных оператором.

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

Дополнительную информацию о триггерах см. в главе Триггеры.

Параметры

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

BEFORE
AFTER
INSTEAD OF

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

событие

Принимает одно из значений: INSERT, UPDATE, DELETE или TRUNCATE; этот параметр указывает на событие, которое приведет к срабатыванию триггера. Можно указать несколько событий с помощью слова OR, за исключением случаев, когда запрашиваются переходные отношения.

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

UPDATE OF имя_столбца1 [, имя_столбца2 . ]

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

Для событий INSTEAD OF UPDATE не допускается использование списка столбцов. Список столбцов также нельзя указывать при запросе переходных отношений.

имя_таблицы

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

имя_ссылочной_таблицы

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

DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED

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

REFERENCING

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

OLD TABLE
NEW TABLE

Это предложение указывает, относится ли следующее имя отношения к отношению перехода образа до изменения или к отношению перехода образа после изменения.

имя_переходного_отношения

Имя (без указания схемы), которое будет использоваться в триггере для этого переходного отношения.

FOR EACH ROW
FOR EACH STATEMENT

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

условие

Логическое выражение, определяющее, будет ли фактически выполняться функция триггера. Если указано WHEN, функция будет вызываться только в том случае, если условие возвращает true. В триггерах FOR EACH ROW условие WHEN может ссылаться на столбцы старых и/или новых значений строк в виде записи OLD.имя_столбца или NEW.имя_столбца соответственно. Конечно, триггеры INSERT не могут ссылаться на OLD, а триггеры DELETE не могут ссылаться на NEW.

Триггеры INSTEAD OF не поддерживают условия WHEN.

В настоящее время выражения WHEN не могут содержать подзапросы.

Обратите внимание, что для триггеров ограничений вычисление условия WHEN не откладывается, а выполняется сразу после осуществления операции изменения строки. Если это условие не вычисляется в true, то триггер не помещается в очередь для отложенного выполнения.

имя_функции

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

В синтаксисе команды CREATE TRIGGER ключевые слова FUNCTION и PROCEDURE равнозначны, но указанная функция должна в любом случае быть функцией, а не процедурой. Ключевое слово PROCEDURE оставлено по историческим причинам и является устаревшим.

аргументы

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

Примечания

Чтобы создать триггер, необходимо иметь право TRIGGER для таблицы, а также право EXECUTE для функции триггера.

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

Создание триггера уровня строк для партиционированной таблицы приведет к созданию идентичного «клонированного» триггера для каждой из ее существующих партиций, и у каждой партиции, созданной или присоединенной впоследствии, тоже появится идентичный триггер. Если в дочерней партиции уже имеется триггер с конфликтующим именем, возникнет ошибка, если только не выполняется CREATE OR REPLACE TRIGGER , поскольку в таком случае этот триггер заменяется клонированным. Когда партиция отсоединяется от родительской таблицы, ее клонированные триггеры удаляются.

Триггер, специфичный для столбцов (определенный с помощью синтаксиса UPDATE OF имя_столбца ), будет срабатывать, когда любой из его столбцов перечисляется в качестве целевого в списке SET команды UPDATE . Значение столбца можно изменить, даже когда триггер не запускается, потому что изменения содержимого строки, внесенные с помощью триггеров BEFORE UPDATE, не рассматриваются. И наоборот, команда вроде UPDATE . SET x = x . запустит триггер по столбцу x, даже если значение столбца не изменилось.

В триггере BEFORE условие WHEN вычисляется непосредственно перед фактическим или возможным выполнением функции, поэтому использование WHEN существенно не отличается от проверки того же условия в начале триггерной функции. В частности, обратите внимание, что строка NEW, рассматриваемая условием, содержит текущее значение, которое, возможно, было изменено предыдущими триггерами. Кроме того, условию WHEN триггера BEFORE не разрешено проверять системные столбцы строки NEW (например ctid), потому что они еще не были установлены.

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

В некоторых случаях одна команда SQL может запустить несколько видов триггеров. Например, команда INSERT с предложением ON CONFLICT DO UPDATE может вызвать как операции добавления, так и операции изменения, поэтому она будет по мере необходимости запускать оба вида триггеров. Отношения перехода, предоставляемые триггерам, являются специфичными для их типа событий; таким образом, триггер INSERT будет видеть только добавленные строки, в то время как триггер UPDATE будет видеть только измененные строки.

Изменения или удаления строк, вызванные принудительными действиями внешнего ключа, такими как ON UPDATE CASCADE или ON DELETE SET NULL, рассматриваются как часть команды SQL, которая их вызвала (обратите внимание, что такие действия никогда не откладываются). В затрагиваемой таблице будут запущены соответствующие триггеры, так что это дает команде SQL еще один способ запускать триггеры, не вполне соответствующие их типу. В простых случаях триггеры, которые запрашивают отношения перехода, будут видеть все изменения, сделанные в их таблице одной исходной командой SQL, как одно отношение перехода. Однако существуют случаи, в которых присутствие триггера AFTER ROW, который запрашивает отношения перехода, приведет к тому, что действия принудительного применения внешнего ключа, инициированные одной командой SQL, будут разделены на несколько этапов, каждый со своим собственным отношением (или отношениями) перехода. В таких случаях любые имеющиеся триггеры уровня оператора будут вызваны один раз при создании отношения перехода, гарантируя, что триггеры будут видеть каждую затронутую строку в отношения перехода один и только один раз.

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

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

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

В настоящее время параметр OR REPLACE не поддерживается для триггеров ограничений.

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

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

Примеры

Выполнение функции check_account_update всякий раз перед изменением строк таблицы accounts:

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();

Изменение определения этого триггера, чтобы функция выполнялась, только если столбец balance указан как целевой в команде UPDATE :

CREATE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();

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

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE FUNCTION check_account_update();

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

CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION view_insert_row();

Выполнение функции check_transfer_balances_to_zero для каждого оператора, чтобы убедиться, что строки таблицы transfer в совокупности дают нулевой баланс:

CREATE TRIGGER transfer_insert AFTER INSERT ON transfer REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT EXECUTE FUNCTION check_transfer_balances_to_zero();

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

CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE FUNCTION check_matching_pairs();

В разделе Полный пример триггера содержится полный пример триггерной функции, написанной на языке C/RUST.

Совместимость

Команда CREATE TRIGGER в QHB реализует подмножество возможностей, описанных в стандарте SQL. В настоящее время отсутствуют следующие функциональные возможности:

В то время как имена переходных таблиц для триггеров AFTER задаются с помощью предложения REFERENCING стандартным образом, переменные строк, используемые в триггерах FOR EACH ROW в предложении REFERENCING указывать нельзя. Порядок обращения к таким строкам зависит от языка, на котором написана триггерная функция, но для каждого языка он вполне определенный. Некоторые языки по сути ведут себя так, как будто в команде присутствует предложение REFERENCING, содержащее указание OLD ROW AS OLD NEW ROW AS NEW.
Стандарт позволяет использовать переходные таблицы со специфичными для столбцов триггерами UPDATE, но тогда набор строк, которые должны быть видны в переходных таблицах, зависит от списка целевых столбцов триггера. В настоящее время в QHB это не реализовано.
QHB разрешает выполнять в качестве действия триггера только пользовательскую функцию. Стандарт же позволяет выполнять в качестве действия триггера ряд других команд SQL, таких как CREATE TABLE . Это ограничение нетрудно обойти, создав пользовательскую функцию, которая выполняет желаемые команды.

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

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

Возможность задать несколько действий для одного триггера с помощью слова OR является расширением стандарта SQL, реализованным в QHB.

Возможность вызова триггеров для операции TRUNCATE является расширением стандарта SQL, реализованным в QHB, как и возможность определять триггеры уровня операторов для представлений.

Вариант команды CREATE CONSTRAINT TRIGGER является расширением стандарта SQL, реализованным в QHB, как и параметр OR REPLACE.

Триггеры

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

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

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

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

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

USE SampleDb; UPDATE Project SET Budget = 200000 WHERE Number = 'p2';

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

Содержимое таблицы логов

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

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

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

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

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

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

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

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

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

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

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

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

Триггеры INSTEAD OF

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

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

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

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

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

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

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

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

Триггеры first и last

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

first — указывает, что триггер является первым триггером AFTER, выполняющимся для модифицирования действия;
last — указывает, что данный триггер является последним триггером AFTER, выполняющимся для инициирования действия;
none — указывает, что для триггера отсутствует какой-либо определенный порядок выполнения. (Это значение обычно используется для того, чтобы выполнить сброс ранее установленного порядка выполнения триггера как первого или последнего.)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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