НТВ плюс 

Ms sql триггеры примеры. Триггеры. Особенности промышленных серверов

Конспект лекций по дисциплине «Базы данных»

Тема: Процедуры и триггеры

(на примере MS SQL Server)

составитель: Л. В. Щёголева

ПетрГУ, кафедра прикладной математики и кибернетики

Введение.........................................................................................................

Описание структуры базы данных............................................................

Понятие процедуры....................................................................................

Команды работы с процедурами...............................................................

Понятие триггера........................................................................................

Команды работы с триггерами..................................................................

Примеры реализации триггеров..............................................................

Пример 1...............................................................................................

Пример 2...............................................................................................

Пример 3...............................................................................................

Пример 4...............................................................................................

Пример 5...............................................................................................

ПетрГУ, кафедра прикладной математики и кибернетики

Введение

В настоящем пособии представлены примеры команд создания процедур и триггеров

с описанием их работы.

Все команды написаны в синтаксисе MS SQL Server.

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

разделе 1.

ПетрГУ, кафедра прикладной математики и кибернетики

1 Описание структуры базы данных

Таблица tblFaculty содержит информацию о факультетах университета.

Наименование

Описание

атрибута

Идентификатор факультета

Название факультета

ФИО декана

Номер кабинета деканата

Телефон деканата

Количество студентов факультета

Таблица tblStudent содержит информацию о студентах университета в одном учебном году.

Наименование атрибута

Описание

Идентификатор студента

ФИО студента

Стипендия

Таблица tblGroup содержит информацию о студенческих группах университета в одном учебном году.

Наименование атрибута

Описание

Идентификатор группы

Староста

Номер группы

Факультет

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

Наименование атрибута

Описание

Идентификатор предмета

Название предмета

Количество часов лекций

Количество часов практик

Факультет

Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры)

ПетрГУ, кафедра прикладной математики и кибернетики

Таблица tblRoom содержит информацию об аудиториях университета.

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

Наименование атрибута

Описание

Идентификатор

Аудитория

День недели

ФИО преподавателя

ПетрГУ, кафедра прикладной математики и кибернетики

2 Понятие процедуры

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

Преимущества использования процедур:

– централизованный контроль доступа к данным;

– прикладные программы могут вызывать процедуру, что сокращает время написания программ, при модификации процедуры, все вызывающие ее программы получат новый код, оптимизация кода;

– снижает трафик в сети в системах «клиент-сервер» за счет передачи только имени процедуры и ее параметров вместо обмена данными, а процедура выполняется на сервере;

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

– большая безопасность данных, пользователь может иметь право вызывать

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

ПетрГУ, кафедра прикладной математики и кибернетики

3 Команды работы с процедурами в MS SQL Server

Создание процедуры

CREATE PROCEDURE <имя процедуры>

[@<имя параметра> <тип данных> , ...]

BEGIN

<операторы>

Имена всех переменных в MS SQL Server должны начинаться с символа

Вызов процедуры

EXECUTE <имя процедуры> [{@<имя переменной> | <значение параметра>}, ...]

Удаление процедуры

DROP PROCEDURE <имя процедуры>

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

Create Procedure prStudentsOfFaculty @id int, @total_sum int output AS

Set @total_sum = 0

Set @total_sum = (Select count(*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = @id)) End

ПетрГУ, кафедра прикладной математики и кибернетики

4 Понятие триггера

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

Особенности реализации триггеров в MS SQL Server

В MS SQL Server:

– триггер может быть вызван либо после выполнения операции, либо вместо выполнения операции;

– триггер вызывается один раз для всех записей таблицы, над которыми должна быть выполнена операция;

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

o таблица Inserted – содержит измененные или добавленные записи таблицы;

o таблица Deleted – содержит записи до выполнения изменений или удаленные записи таблицы;

– в теле триггера, определенного для операции Insert, доступна только таблица

– в теле триггера, определенного для операции Delete, доступна только таблица

– в теле триггера, определенного для операции Update, доступны обе таблицы

Inserted и Deleted;

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

ПетрГУ, кафедра прикладной математики и кибернетики

5 Команды работы с триггерами

Создание

CREATE TRIGGER <имя триггера> ON <имя таблицы>

{ FOR | AFTER | INSTEAD OF }

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] AS

DECLARE @<имя переменной> <тип данных>, ...

BEGIN <операторы>

Удаление

DROP TRIGGER <имя триггера>

ПетрГУ, кафедра прикладной математики и кибернетики

6 Примеры реализации триггеров

Ограничение предметной области: стипендия студента не может быть увеличена более чем на 5% от предыдущей стипендии.

CREATE TRIGGER tgrStudentGrantUpdate

ON tblStudent AFTER UPDATE

DECLARE @Grant_old float, @Grant_new float, @Id int;

Select @Grant_old = Grant from Deleted

Select @Grant_new = Grant, @Id = StudentId from Inserted

IF (@Grant_new - @Grant_old > 0.05 * @Grant_old)

UPDATE tblStudent SET Grant = 1.05 * @Grant_old

WHERE StudentId = @Id

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

В триггере определены три локальные переменные: @Grant_old (вещественного типа) для хранения старой стипендии студента, @Grant_new (вещественного типа) для хранения новой стипендии студента, @Id (целого типа) для хранения идентификатора студента.

При вызове триггера СУБД создает две таблицы: Deleted, содержащую измененные записи до их изменения, и Inserted, содержащую измененные записи после их изменения.

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

Далее, в теле триггера проверяется условие о величине изменения стипендии. Если стипендия изменилась более чем на 5%, то триггер вносит поправку в данные – увеличивает стипендию только на 5% по сравнению со предыдущим значением стипендии студента. Это действие выполняется посредством вызова операции Update в таблице tblStudent для соответствующего студента.

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

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

create table ( identity (1, 1) not null , null , null , null , (1) null) on go

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

create trigger trg_updatefirst on dbo. for insert as declare @id int, @val as float select @id = id from inserted select @val = floor(rand() * 10) update set = @val where id = @id insert into triggerlog (triggername) values ("trg_updatefirst")

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

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

create trigger trg_updatelast on dbo. for insert as declare @id int select @id = id from inserted update set = + where id = @id insert into triggerlog (triggername) values ("trg_updatelast")

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

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

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

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

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

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

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

sp_settriggerorder [@triggername =] "triggername" , [@order =] "значение" , [@stmttype =] "statement_type"

Первый параметр — это имя триггера, а второй параметр — порядок. Этот порядок может принимать одно из трех значений: " 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. Вместо этого Вы пишете простые запросы.

select objectproperty(object_id(" trg_updatefirst "), "execisfirstinserttrigger") execisfirstinserttrigger")

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

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

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

Сервер sql 2005

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

Заключение

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

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

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

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

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

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

Пример (Oracle):

/* Триггер на уровне таблицы */ CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger AFTER UPDATE ON district BEGIN INSERT INTO info VALUES ("table "district" has changed" ) ; END ;

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

/* Триггер на уровне строки */ CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger AFTER UPDATE ON district FOR EACH ROW BEGIN INSERT INTO info VALUES ("one string in table "district" has changed" ) ; END ;


Wikimedia Foundation . 2010 .

  • Домашнее хозяйство
  • Спектроскопия

Смотреть что такое "Триггер (базы данных)" в других словарях:

    Представление (базы данных) - У этого термина существуют и другие значения, см. Представление. Представление (англ. view, более созвучное не стандартное название «вид», в сленге программистов часто используется в качестве заимствования из английского «вьюха», «вьюшка»)… … Википедия

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

    Реляционные базы данных - Реляционная база данных база данных, основанная на реляционной модели данных. Слово «реляционный» происходит от англ. relation (отношение). Для работы с реляционными БД применяют реляционные СУБД. Использование реляционных баз данных было… … Википедия

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

    Курсор (базы данных) - У этого термина существуют и другие значения, см. Курсор (значения). Курсор ссылка на контекстную область памяти[источник не указан 126 дней]. В некоторых реализациях информационно логического языка SQL (Oracle,… … Википедия

    Триггер (значения) - Триггер (англ. trigger в значении существительного «собачка, защёлка, спусковой крючок в общем смысле, приводящий нечто в действие элемент»; в значении глагола «приводить в действие»): в русском языке первоначально термин из области… … Википедия

    Рефакторинг баз данных - (англ. database refactoring) это простое изменение в схеме базы данных, которое способствует улучшению ее проекта при сохранении функциональной и информационной семантики. Иными словами, следствием рефакторинга базы данных не может быть… … Википедия

    База данных - Запрос «БД» перенаправляется сюда; см. также другие значения. База данных представленная в объективной форме совокупность самостоятельных материалов (статей, расчётов, нормативных актов, судебных решений и иных подобных материалов),… … Википедия

    Проектирование баз данных - процесс создания схемы базы данных и определения необходимых ограничений целостности. Содержание 1 Основные задачи проектирования баз данных … Википедия

    Модель данных - В классической теории баз данных, модель данных есть формальная теория представления и обработки данных в системе управления базами данных (СУБД), которая включает, по меньшей мере, три аспекта: 1) аспект структуры: методы описания типов и… … Википедия

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

Назначение триггеров

Предотвращение изменения (например, предотвратить счета от изменений после того как они разосланы).
. Журналирование изменения (например, хранить копии старых данных).
. Аудит изменений (например, вести лог пользователей и ролей, участвующих в изменениях).
. Фиксация изменений (например, обеспечить, чтобы все изменения датировались по часам сервера, а не клиента).
. Реализация бизнес-правил.
. Репликация данных (например, хранить записи всех изменений, которые будут отправлены в другой базе данных более поздней версии).
. Повышение производительности (например, обновление сальдо после каждой детали сделки, для ускорения запросов).

Объявление триггеров

CREATE TRIGGER {BEFORE|AFTER } {DELETE|INSERT|UPDATE [OF ]} ON REFERENCING {OLD {[ROW ]|TABLE [AS ] } NEW {ROW|TABLE } [AS ] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC ]

[END ]

Ключевые слова

. BEFORE|AFTER – время запуска триггера – до | после операции обновления.
. DELETE|INSERT|UPDATE = событие срабатывания триггера.
. FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
. FOR EACH STATEMENT – для всей команды (действует по умолчанию).
. REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

Ограничения триггеров

Тело триггера не может содержать операторов:
. Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
. Обработки транзакций (COMMIT, ROLLBACK)
. Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
. Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
. Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
. Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
. Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @ .
. В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
. В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
. Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
. Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

Недостатки триггеров

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

Изменение и удаление триггеров

Для удаление триггера используется оператор DROP TRIGGER
. Для изменения триггера используется оператор ALTER TRIGGER …
. Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE } {BEFORE|AFTER } {INSERT|DELETE|UPDATE } [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION ] {FOR|AFTER|INSTEAD OF } {INSERT|UPDATE|DELETE }
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
. В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

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

Триггеры DDL срабатывают в ответ на ряд событий языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции. Триггеры входа могут срабатывать в ответ на событие LOGON, возникающее при установке пользовательских сеансов. Триггеры могут быть созданы непосредственно из Transact-SQL инструкций или методов сборок, созданных в Microsoft .NET Framework общеязыковая среда выполнения (CLR) и передачи в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой инструкции.

SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name

SQL Server Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table (DML Trigger on memory -optimized tables) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table } [ WITH [ ,...n ] ] { FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] } ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]

Trigger on a CREATE , ALTER , DROP , GRANT , DENY , REVOKE or UPDATE statement (DDL Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

Trigger on a LOGON event (Logon Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ ,...n ] ] { FOR | AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] [ ; ] > } ::= [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP , GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON { DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] [ ; ] } ::= [ EXECUTE AS Clause ]

Условно изменяет триггер только в том случае, если он уже существует.

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

имя_триггера
Имя триггера. Объект имя_триггера должно соответствовать правилам для , за исключением того, что имя_триггера не может начинаться с символов # или ##.

table | view
Таблица или представление, в которых выполняется триггер DML, иногда указывается как таблица триггера или представление триггера. Указание уточненного имени таблицы или представления не является обязательным. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах.

DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если указано, триггер срабатывает всякий раз, когда event_type или event_group происходит в текущей базе данных.

Применяет область действия триггера DDL или триггера входа к текущему серверу. Если указано, триггер срабатывает всякий раз, когда event_type или event_group возникновении в любом месте на текущем сервере.

Затемняет текст инструкции CREATE TRIGGER. Использование параметра WITH ENCRYPTION не позволяет публиковать триггер как часть репликации SQL Server. Параметр WITH ENCRYPTION не может быть указан для триггеров CLR.

EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, ссылаемые триггером.

Дополнительные сведения см. в разделе .

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

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

SCHEMABINDING
Гарантирует, что таблицы, на которые ссылается триггер не может быть удалена или изменена.

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

FOR | AFTER
Тип AFTER указывает, что триггер DML срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Все каскадные действия и проверки ограничений, на которые имеется ссылка, должны быть успешно завершены, прежде чем триггер сработает.

Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию.

Триггеры AFTER не могут быть определены на представлениях.

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

На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEAD OF.

Использование триггеров INSTEAD OF не допускается в поддерживающих обновление представлениях, которые используют параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к поддерживающему обновление представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON DELETE. Аналогично параметр UPDATE не разрешен в таблицах, у которых есть ссылочная связь с указанием каскадного действия ON UPDATE.

Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND не может быть использован для триггеров INSTEAD OF или при явном указании триггера AFTER. Аргумент WITH APPEND может использоваться только при указании параметра FOR без INSTEAD OF или AFTER из соображений поддержки обратной совместимости. Аргумент WITH APPEND не может быть указан, если указан параметр EXTERNAL NAME (в случае триггера CLR).

event_type
Имя языкового события Transact-SQL, которое после выполнения вызывает срабатывание триггера DDL. Допустимые события для триггеров DDL, перечислены в .

event_group
Имя стандартной группы событий языка Transact-SQL. Триггер DDL срабатывает после выполнения какого-либо Transact-SQL событий языка, к которому принадлежит event_group . Список групп событий для триггеров DDL, перечислены в .

После завершения работы, инструкции CREATE TRIGGER event_group также функционирует в качестве макроса, добавляя события соответствующих типов в представление каталога sys.trigger_events.

NOT FOR REPLICATION

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

sql_statement
Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события - DML, DDL или событие входа - вызывают срабатывание триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

Триггеры могут содержать любое количество инструкций языка Transact-SQL любого типа, за некоторыми исключениями. Дополнительные сведения см. в подразделе «Примечания». Триггеры разработаны для контроля или изменения данных на основании инструкций модификации или определения данных; они не возвращают пользователю никаких данных. Transact-SQL Часто содержат инструкции в триггере языка управления потоком .

Триггеры DML используют логические (концептуальные) таблицы deleted и inserted. По своей структуре они подобны таблице, на которой определен триггер, то есть таблице, к которой применяется действие пользователя. В таблицах deleted и inserted содержатся старые или новые значения строк, которые могут быть изменены действиями пользователя. Например, для запроса всех значений таблицы deleted можно использовать инструкцию:

SELECT * FROM deleted;

Дополнительные сведения см. в разделе .

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

SQL Serverпозволяет обновить текст , ntext , или изображение триггер столбцов с помощью INSTEAD OF для таблиц или представлений.

Для триггеров в таблицах, оптимизированных для памяти единственным sql_statement разрешено на верхнем уровне-АТОМАРНОГО блока. Допускается внутри блока ATOMIC T-SQL, ограничивается допускается внутри собственного процедуры T-SQL.

< method_specifier >

Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым SQL Server идентификатором и существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки () или двойные кавычки (" "). Класс не может быть вложенным.

Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. В SQL Server декларативное ограничение ссылочной целостности обеспечивается инструкциями ALTER TABLE и CREATE TABLE. Однако декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после срабатывания триггера INSTEAD OF и до выполнения триггера AFTER. В случае нарушения ограничения выполняется откат действий триггера INSTEAD OF, и триггер AFTER не срабатывает.

Первые и последние триггеры AFTER, которые будут выполнены в таблице, могут быть определены с использованием процедуры sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если в таблице есть другие триггеры AFTER, они будут выполняться случайным образом.

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

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

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

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

Например, если триггер определен как INSTEAD OF UPDATE для представления и выполняет инструкцию UPDATE для этого же представления, инструкция UPDATE, выполняемая триггером, не вызывает нового срабатывания триггера. Инструкция UPDATE, выполняемая в триггере, обрабатывает представление так, как если бы у представления не имелось триггера INSTEAD OF. Столбцы, измененные с помощью инструкции UPDATE, должны принадлежать одной базовой таблице. Каждая модификация базовой таблицы вызывает применение последовательности ограничений и взвод триггеров AFTER, определенных для данной таблицы.

Проверка действий инструкций UPDATE или INSERT на указанные столбцы

Триггер языка Transact-SQL можно сконструировать для выполнения конкретных действий, основанных на изменении определенных столбцов с помощью инструкций UPDATE или INSERT. Используйте или в теле триггера для этой цели. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. С помощью конструкции COLUMNS_UPDATED проверяются действия инструкций UPDATE или INSERT, проводимых на нескольких столбцах, и возвращается битовый шаблон, показывающий, какие столбцы были вставлены или обновлены.

Ограничения триггеров

Инструкция CREATE TRIGGER должна быть первой инструкцией в пакете и может применяться только к одной таблице.

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

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

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

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

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

Во время срабатывания триггера результаты возвращаются вызывающему приложению так же, как и в случае с хранимыми процедурами. Чтобы предотвратить вызванное срабатыванием триггера возвращение результатов приложению, не следует включать инструкции SELECT, возвращающие результат, или инструкции, которые выполняют в триггере присвоение переменных. Триггер, содержащий либо инструкции SELECT, которые возвращают результаты пользователю, либо инструкции, выполняющие присвоение переменных, требует особого обращения; эти возвращаемые результаты должны быть перезаписаны во все приложения, в которых разрешены изменения таблицы триггера. Если в триггере происходит присвоение переменной, следует использовать инструкцию SET NOCOUNT в начале триггера, чтобы предотвратить возвращение каких-либо результирующих наборов.

Хотя инструкция TRUNCATE TABLE по своей сути является инструкцией DELETE, она не активирует триггер, поскольку операция не записывает удаление отдельных строк. Однако беспокоиться о случайном обходе триггера DELETE таким образом нужно только пользователям с разрешениями на выполнение инструкции TRUNCATE TABLE.

Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.

Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:

Триггеры DDL, как и стандартные триггеры, выполняют хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают в ответ на выполнение инструкций UPDATE, INSERT или DELETE по отношению к таблице или представлению. Вместо этого триггеры срабатывают в первую очередь в ответ на инструкции языка определения данных (DDL). Это инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

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

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

В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Поэтому для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в разделе .

Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие вызывается при установке пользовательского сеанса с экземпляром SQL Server. Триггеры входа срабатывают после завершения этапа проверки подлинности при входе, но перед тем, как пользовательский сеанс реально устанавливается. Следовательно, все сообщения, которые возникают внутри триггера и обычно достигают пользователя, такие как сообщения об ошибках и сообщения от инструкции PRINT, перенаправляются в журнал ошибок SQL Server . Дополнительные сведения см. в разделе .

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

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

Отключение триггера входа

Триггер входа может эффективно запрещать подключения к службам Компонент Database Engine для всех пользователей, в том числе членов предопределенной роли сервера sysadmin . Если триггер входа запрещает соединения, члены предопределенной роли сервера sysadmin могут подключаться с помощью выделенного административного соединения или путем вызова Компонент Database Engine в режиме минимальной конфигурации (-f). Дополнительные сведения см. в разделе .

Возвращаемые результаты

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

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

Несколько триггеров

SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL и LOGON. Например, если инструкция CREATE TRIGGER FOR UPDATE выполняется в таблице, уже имеющей триггер UPDATE, дополнительно создается триггер обновления. В более ранних версиях SQL Server был разрешен только один триггер в каждой таблице для каждого события изменения данных INSERT, UPDATE или DELETE.

Рекурсивные триггеры

SQL Server разрешает рекурсивный вызов триггеров, если с помощью инструкции ALTER DATABASE включена настройка RECURSIVE_TRIGGERS.

В рекурсивных триггерах могут возникать следующие типы рекурсии:

    Косвенная рекурсия

    При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Это вызывает срабатывание триггера T2 и обновление таблицы T1.

    Прямая рекурсия

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

В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет каждый из триггеров TR1 и TR2 один раз. В дополнение к этому срабатывание триггера TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.

Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.

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

Вложенные триггеры

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

Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. В конфигурации по умолчанию вложенные триггеры разрешены. Если вложенные триггеры отключены, рекурсивные триггеры тоже будут отключены, вне зависимости от настройки RECURSIVE_TRIGGERS, установленной с помощью инструкции ALTER DATABASE.

Первый триггер AFTER, вложенный в INSTEAD OF, триггер срабатывает, даже если вложенные триггеры параметр конфигурации сервера задано значение 0. Однако при таком значении параметра последующие триггеры AFTER не срабатывают. Рекомендуется проверить приложения на наличие вложенных триггеров определить, соответствуют ли приложения бизнес-правилам это поведение при вложенные триггеры параметр конфигурации сервера имеет значение 0 и внесите соответствующие изменения.

Отложенная интерпретация имен

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

Для создания триггера DML требуется разрешение ALTER на таблицу или представление, в которых создается триггер.

Для создания триггера DDL с областью действия в пределах сервера (ON ALL SERVER) или триггера входа требуется разрешение CONTROL SERVER на сервер. Для создания триггера DDL с областью видимости в пределах базы данных (ON DATABASE) требуется разрешение ALTER ANY DATABASE DDL TRIGGER на текущую базу данных.

A. Использование триггера DML с предупреждающим сообщением

Следующий триггер DML отправляет клиенту сообщение, когда кто-то пытается добавить или изменить данные в таблице Customer в базе данных AdventureWorks2012.

CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT , UPDATE AS RAISERROR ("Notify Customer Relations" , 16 , 10 ); GO

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

В следующем примере указанному пользователю (MaryM) по электронной почте отправляется сообщение при изменении таблицы Customer .

CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT , UPDATE , DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = "AdventureWorks2012 Administrator" , @recipients = "[email protected]" , @body = "Don""t forget to print a report for the sales force." , @subject = "Reminder" ; GO

В. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor

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

В следующем примере создается триггер DML в базе данных AdventureWorks&2012;. Этот триггер выполняет проверку кредитоспособности поставщика является хорошим (не 5) при попытке вставить новый заказ на покупку в PurchaseOrderHeader таблицы. Для получения сведений о кредитоспособности поставщика требуется ссылка на таблицу Vendor . В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не выполняется.

This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table -- when the credit rating of the specified vendor is set to 5 (below average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p .PurchaseOrderID = i .PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p .VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ("A vendor""s credit rating is too low to accept new purchase orders." , 16 , 1 ); ROLLBACK TRANSACTION ; RETURN END ; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status , EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES (2 ,3 ,261 ,1652 ,4 ,GETDATE () ,GETDATE () ,44594.55 ,3567.564 ,1114.8638 ); GO

Г. Использование триггера DDL уровня базы данных

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

CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS RAISERROR ("You must disable Trigger "safety" to drop synonyms!" ,10 , 1 ) ROLLBACK GO DROP TRIGGER safety ON DATABASE ; GO

Д. Использование триггера DDL уровня сервера

В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Дополнительные примеры использования функции EVENTDATA в триггерах DDL см. .

CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created." SELECT EVENTDATA ().value ("(/EVENT_INSTANCE/TSQLCommand/CommandText)" ,"nvarchar(max)" ) GO DROP TRIGGER ddl_trig_database ON ALL SERVER ; GO

Е. Использование триггера входа

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

USE master ; GO CREATE LOGIN login_test WITH PASSWORD = "3KHJ6dhx(0xVYsdf" MUST_CHANGE, CHECK_EXPIRATION = ON ; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS "login_test" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "login_test" AND (SELECT COUNT (*) FROM sys .dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "login_test" ) >