Использование таблиц inserted и deleteduse the inserted and deleted tables

6.2. Синтаксис DROP TRIGGER

DROP TRIGGER 

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

Обратите внимание: До MySQL
5.0.10, имя таблицы требовалось вместо имени схемы
(). При обновлении с MySQL 5.0 до MySQL 5.0.10 или выше, Вы должны
удалить все триггеры перед обновлением и вновь создать их впоследствии, иначе
вызов не работает после обновления

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

Отключение и удаление триггеров DDLDisabling and Dropping DDL Triggers

Если триггер DDL больше не нужен, он может быть отключен или удален.When a DDL trigger is no longer needed, you can disable it or delete it.

Отключение триггера DDL не приводит к его удалению,Disabling a DDL trigger does not drop it. Триггер все еще существует как объект в текущей базе данных.The trigger still exists as an object in the current database. Однако этот триггер не будет срабатывать при выполнении инструкций Transact-SQLTransact-SQL , на которые он был запрограммирован.However, the trigger will not fire when any Transact-SQLTransact-SQL statements on which it was programmed are run. Отключенные триггеры DDL можно повторно включать.DDL triggers that are disabled can be reenabled. После включения триггер DDL вновь начинает срабатывать так, как это было указано при его создании.Enabling a DDL trigger causes it to fire in the same way the trigger did when it was originally created. При создании триггеров DDL они включаются по умолчанию.When DDL triggers are created, they are enabled by default.

При удалении триггера DDL он удаляется из текущей базы данных.When a DDL trigger is deleted, it is dropped from the current database. Удаление триггера DDL никоим образом не влияет на объекты или данные, на которые распространялась область действия триггера.Any objects or data upon which the DDL trigger is scoped are not affected.

Отключение триггера DDLTo disable a DDL trigger

Включение триггера DDLTo enable a DDL trigger

Удаление триггера DDLTo delete a DDL trigger

КомментарииRemarks

Если не задано никаких аргументов, функция TRIGGER_NESTLEVEL возвращает общее число триггеров в стеке вызова.When no parameters are specified, TRIGGER_NESTLEVEL returns the total number of triggers on the call stack. Этот список также включает и саму функцию TRIGGER_NESTLEVEL.This includes itself. Пропуск аргументов может наблюдаться в случае, когда триггер выполняет команды, приводящие к запуску другого триггера, или создает последовательность запускаемых триггеров.Omission of parameters can occur when a trigger executes commands causing another trigger to be fired or creates a succession of firing triggers.

Для возвращения общего числа триггеров в стеке вызовов для конкретного типа триггеров и категории событий следует указать аргумент object_id = 0.To return the total number of triggers on the call stack for a particular trigger type and event category, specify object_id = 0.

Функция TRIGGER_NESTLEVEL возвращает 0 в случае выполнения вне триггера, если значение хотя бы одного из аргументов не равно NULL.TRIGGER_NESTLEVEL returns 0 if it is executed outside a trigger and any parameters are not NULL.

Если каким-либо аргументам явно задано значение NULL, то оно возвращается независимо от того, внутри или вне триггера использовалась функция TRIGGER_NESTLEVEL.When any parameters are explicitly specified as NULL, a value of NULL is returned regardless of whether TRIGGER_NESTLEVEL was used within or external to a trigger.

Определение триггеров

Последнее обновление: 09.11.2017

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

Формальное определение триггера:

CREATE TRIGGER имя_триггера
ON {имя_таблицы | имя_представления}
{AFTER | INSTEAD OF} 
AS выражения_sql

Для создания триггера применяется выражение CREATE TRIGGER, после которого идет имя триггера.
Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

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

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

• AFTER: выполняется после выполнения действия. Определяется только для таблиц.

• INSTEAD OF: выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT, UPDATE или
DELETE.

Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции
указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

CREATE DATABASE productdb;
GO
 
USE productdb;
CREATE TABLE Products
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price MONEY NOT NULL
);

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

USE productdb;
GO
CREATE TRIGGER Products_INSERT_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
UPDATE Products
SET Price = Price + Price * 0.38
WHERE Id = (SELECT Id FROM inserted)

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

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара,
а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар?
Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных
товарах.

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

Для удаления триггера необходимо применить команду DROP TRIGGER:

DROP TRIGGER Products_INSERT_UPDATE

Отключение триггера

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

DISABLE TRIGGER Products_INSERT_UPDATE ON Products

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER:

ENABLE TRIGGER Products_INSERT_UPDATE ON Products

НазадВперед

Связанные задачиRelated Tasks

ЗадачаTask	РазделTopic
Описывает, как создать триггеры входа.Describes how to create logon triggers. Триггеры входа могут создаваться из любой базы данных, но регистрируются на уровне сервера и принадлежат базе данных master .Logon triggers can be created from any database, but are registered at the server level and reside in the master database.	CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)
Описывает, как изменить триггеры входа.Describes how to modify logon triggers.	ALTER TRIGGER (Transact-SQL)ALTER TRIGGER (Transact-SQL)
Описывает, как удалить триггеры входа.Describes how to delete logon triggers.	DROP TRIGGER (Transact-SQL)DROP TRIGGER (Transact-SQL)
Описывает, как возвратить сведения о триггерах входа.Describes how to return information about logon triggers.	sys.server_triggers (Transact-SQL)sys.server_triggers (Transact-SQL)sys.server_trigger_events (Transact-SQL)sys.server_trigger_events (Transact-SQL)
Описывает, как перехватить данные события триггером входа.Describes how to capture logon trigger event data.

Создание триггеров DDLCreating DDL Triggers

Триггеры DDL создаются при помощи инструкции Transact-SQLTransact-SQL CREATE TRIGGER для триггеров DDL.DDL triggers are created by using the Transact-SQLTransact-SQL CREATE TRIGGER statement for DDL triggers.

Создание триггера DDLTo create a DDL trigger

CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)

Важно!

Возможность возвращать результирующие наборы из триггеров будет удалена в следующей версии SQL ServerSQL Server.The ability to return result sets from triggers will be removed in a future version of SQL ServerSQL Server. Триггеры, возвращающие результирующие наборы, могут привести к непредвиденному поведению приложений, не предназначенных для работы с ними.Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Не используйте в разрабатываемых приложениях триггеры, возвращающие результирующие наборы, и запланируйте изменение приложений, которые используют их в настоящее время.Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. Чтобы триггеры не возвращали результирующие наборы в SQL ServerSQL Server, задайте значение параметра disallow results from triggers , равное 1.To prevent triggers from returning result sets in SQL ServerSQL Server, set the disallow results from triggers Option to 1. В будущих версиях SQL ServerSQL Serverдля этого параметра по умолчанию будет задаваться значение 1.The default setting of this option will be 1 in a future version of SQL ServerSQL Server.

Создание триггера 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. По этой причине, если база данных содержит большое число часто используемых триггеров, следует ожидать значительного увеличения объема этой системной базы данных.

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

Если Вы захотите выполнить даже незначительную нормализацию структуры базы данных,  может получиться так, что нужно будет удалять источник основных данных, который имеет фрагменты, участвующие в общем потоке данных. Например, у вас может быть 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_id = cart_id;” Для MySQL задается, что перед тем, как удалить из trigger_carts, нужно взять  OLD.cart_id и также удалить из trigger_cart_items. Синтаксис OLD определяет переменную. Он будет обсуждаться в следующем разделе, где будут комбинироваться  OLD и NEW.

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

Два запроса:

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

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

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

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

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

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

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

CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples
FOR INSERT, UPDATE
AS
 Действие

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

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

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

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

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

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

Атрибутные функции

Oracle предоставляет набор функций (определенных в пакете ) для получения информации о причине запуска триггера DDL и других связанных с ним параметрах (например, имя удаляемой таблицы). Перечень этих атрибутных функций приведен в табл. 2, а примеры их использования — в следующих разделах.

Таблица 2. События триггеров DDL и атрибутные функции

Функция	Что возвращает
ORA_CLIENT_IP_ADDRESS	IP-адрес клиента
ORA_DATABASE_NAME	Имя базы данных
ORA_DES_ENCRYPTED_PASSWORD	Пароль текущего пользователя, зашифрованный с использованием алгоритма DES
ORA_DICT_OBJ_NAME	Имя объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера
ORA_DICT_OBJ_NAME_LIST	Количество обработанных командой объектов. В параметре  возвращается полный список этих объектов в виде коллекции типа
ORA_DICT_OBJ_OWNER	Имя владельца объекта базы данных, связанного с командой DDL, которая вызвала запуск триггера
ORA_DICT_OBJ_OWNER_LIST	Количество обработанных командой объектов. В параметре  возвращается полный список имен этих объектов в виде коллекции типа
ORA_DICT_OBJ_TYPE	Тип объекта базы данных, связанного с командой DDL, вызвавшей запуск триггера (например, или )
ORA_GRANTEE	Количество пользователей, получивших привилегии. В аргументе содержится полный список этих пользователей в виде коллекции типа
ORA_INSTANCE_NUM	Номер экземпляра базы данных
ORA_IS_ALTER_COLUMN	, если изменяется столбец, заданный параметром ; в противном случае
ORA_IS_CREATING_NESTED_TABLE	, если создается вложенная таблица; в противном случае
ORA_IS_DROP_COLUMN	, если удаляется столбец, заданный параметром_ в противном случае
ORA_LOGIN_USER	Имя пользователя, для которого запущен триггер
ORA_PARTITION_POS	Позиция команды для корректной вставки секции
ORA_PRIVILEGE_LIST	Количество предоставленных или отмененных привилегий. В аргументе содержится полный список привилегий в виде коллекции типа
ORA_REVOKEE	Количество пользователей, лишенных привилегий. В аргументе содержится полный список этих пользователей в виде коллекции типа
ORA_SQL_TXT	Количество строк в команде SQL, которая вызвала запуск триггера. Аргумент возвращает каждую строку команды в виде аргумента типа
ORA_SYSEVENT	Тип события, вызвавшего запуск триггера DDL (например, , или )
ORA_WITH_GRANT_OPTION	, если привилегии предоставлены конструкцией ; в противном случае

Об атрибутных функциях необходимо дополнительно сказать следующее:

 Тип данных 0RA_NAME_LIST_T определен в пакете DBMS_STANDARD так:

Иными словами, это вложенная таблица строк, каждая из которых может содержать до 64 символов.

•  События триггеров DDL и атрибутные функции также определены в пакете . Для каждой из функций этого пакета Oracle создает независимую функцию, добавляя к ее имени префикс 0RA_, для чего при создании базы данных выполняется сценарий  В некоторых версиях Oracle этот сценарий содержит ошибки, из-за которых независимые функции не видны или не выполняют­ся. Если вы сомневаетесь в правильности определения этих элементов, попросите ад­министратора базы данных проверить сценарий и внести необходимые исправления.
•  Представление словаря данных  не обновляется до срабатывания обо­их триггеров и . Иначе говоря, вы не сможете использовать эти функции для реализации системы контроля версий «до и после», построенной ис­ключительно в границах базы данных и основанной на триггерах.

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

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

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

Область действия триггера DDLDDL Trigger Scope

Триггеры DLL срабатывают в ответ на событие Transact-SQLTransact-SQL , обработанное текущей базой данных или текущим сервером.DDL triggers can fire in response to a Transact-SQLTransact-SQL event processed in the current database, or on the current server. Область триггера зависит от события.The scope of the trigger depends on the event. Например, триггер DDL, созданный для срабатывания на событие CREATE TABLE, может срабатывать каждый раз, когда в базе данных или в экземпляре сервера возникает событие CREATE_TABLE.For example, a DDL trigger created to fire in response to a CREATE_TABLE event can do so whenever a CREATE_TABLE event occurs in the database, or on the server instance. Триггер DDL, созданный для запуска в ответ на событие CREATE_LOGIN, может выполнять это только при возникновении события CREATE_LOGIN в экземпляре сервера.A DDL trigger created to fire in response to a CREATE_LOGIN event can do so only when a CREATE_LOGIN event occurs in the server instance.

В следующем примере триггер DDL будет срабатывать каждый раз, когда в базе данных будет выполняться инструкция или происходить событие .In the following example, DDL trigger will fire whenever a or event occurs in the database.

В следующем примере триггер DDL выводит сообщение, если в текущем экземпляре сервера происходит любое событие .In the following example, a DDL trigger prints a message if any event occurs on the current server instance. В этом примере используется функция для получения текста соответствующей инструкции Transact-SQLTransact-SQL .The example uses the function to retrieve the text of the corresponding Transact-SQLTransact-SQL statement. Дополнительные сведения об использовании EVENTDATA с триггерами DDL см. в статье Использование функции EVENTDATA.For more information about how to use EVENTDATA with DDL triggers, see Use the EVENTDATA Function.

Списки сопоставления инструкций Transact-SQLTransact-SQL соответствующим областям можно найти по ссылкам в подразделе «Выбор определенной инструкции DDL для запуска триггера DDL» далее в этом разделе.The lists that map the Transact-SQLTransact-SQL statements to the scopes that can be specified for them are available through the links provided in the section «Selecting a Particular DDL Statement to Fire a DDL Trigger,» later in this topic.

Триггеры DDL масштаба базы данных хранятся как объекты в базах данных, в которых они создаются.Database-scoped DDL triggers are stored as objects in the database in which they are created. Триггеры DDL можно создавать и в базе данных master , и они будут работать точно так же, как триггеры, созданные в пользовательских базах данных.DDL triggers can be created in the master database and behave just like those created in user-designed databases. Чтобы получить сведения о триггерах DDL, можно послать запрос к представлению каталога sys.triggers .You can obtain information about DDL triggers by querying the sys.triggers catalog view. Запрос к sys.triggers можно выполнить в контексте базы данных, где были созданы триггеры. Или можно задать имя базы данных в качестве идентификатора, например master.sys.triggers.You can query sys.triggers within the database context in which the triggers are created or by specifying the database name as an identifier, such as master.sys.triggers.

Триггеры DDL масштаба триггера хранятся как объекты в базе данных master .Server-scoped DDL triggers are stored as objects in the master database. Однако для получения сведений о триггерах DDL сервера можно направить запрос к представлению каталога sys.server_triggers в любом контексте базы данных.However, you can obtain information about server-scoped DDL triggers by querying the sys.server_triggers catalog view in any database context.

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

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

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

Например, мы хотим всегда устанавливать цену на товар с 30% надбавкой к стоимости. Это приводит к тому, что когда мы изменяем (UPDATE) стоимость, мы должны изменить (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: