Create trigger ms sql: CREATE TRIGGER (Transact-SQL) — SQL Server

НОУ ИНТУИТ | Лекция | Триггеры: создание и применение

< Лекция 18 || Лекция 14: 123

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

Ключевые слова: триггер, исполнение, DML, базы данных, операции, SQL, достоверность, транзакция, откат, триггерное событие, ограничения целостности данных, формат команды, таблица, конфликт доступа, СУБД, server, сервер, шифрование, доступ, создание триггера, специальная таблица inserted, специальная таблица: deleted, %ROWCOUNT, обнаружение нарушений, удаление триггера, реализация ограничений на значение, статус ошибки

Определение триггера в стандарте языка SQL

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

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

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

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

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

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

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

• проверка корректности введенных данных и выполнение сложных
  ограничений целостности данных, которые трудно, если вообще
  возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
• выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном
  определенным образом;
• intuit.ru/2010/edi»>накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
• поддержка репликации.

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=
  CREATE TRIGGER имя_триггера
  BEFORE | AFTER <триггерное_событие>
  ON <имя_таблицы>
  [REFERENCING 
    <список_старых_или_новых_псевдонимов>]
  [FOR EACH { ROW | STATEMENT}]
  [WHEN(условие_триггера)]
  <тело_триггера>

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

intuit.ru/2010/edi»>Выполняемые триггером действия задаются для каждой строки ( FOR
EACH ROW ), охваченной данным событием, или только один раз для каждого события ( FOR EACH STATEMENT ).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка ( OLD / NEW )
либо старая или новая таблица ( OLD TABLE / NEW TABLE ). Ясно, что
старые значения не применимы для событий вставки, а новые – для событий удаления.

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

• сложность: при перемещении некоторых функций в базу данных усложняются задачи ее проектирования, реализации и администрирования;
• intuit.ru/2010/edi»>скрытая функциональность: перенос части функций в базу данных и
  сохранение их в виде одного или нескольких триггеров иногда приводит к сокрытию от пользователя некоторых функциональных возможностей. Хотя это в определенной степени упрощает его работу, но, к
  сожалению, может стать причиной незапланированных, потенциально нежелательных и вредных побочных эффектов, поскольку в этом
  случае пользователь не в состоянии контролировать все процессы,
  происходящие в базе данных;
• влияние на производительность: перед выполнением каждой команды по изменению состояния базы данных СУБД должна проверить триггерное условие с целью выяснения необходимости запуска триггера для этой команды. Выполнение подобных вычислений
  сказывается на общей производительности СУБД, а в моменты пиковой нагрузки ее снижение может стать особенно заметным. Очевидно, что при возрастании количества триггеров увеличиваются и
  накладные расходы, связанные с такими операциями.

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

Дальше >>

< Лекция 18 || Лекция 14: 123

Заметки Дмитрия Пилюгина о Microsoft SQL Server

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

Это может быть полезно если мы, например, хотим увидеть/залогировать конкретный запрос, который привел к изменению данных. Сразу скажу, что штатный механизм для обеспечения этого на сегодняшний день, в версиях 2000, 2005, 2008 — не предусмотрен. Но тем не менее, что-то похожее можно реализовать с некоторыми ограничениями.

В этом нам поможет команда DBCC INPUTBUFFER. В отличии от fn_get_sql и, появившейся в 2005, sys.dm_exec_sql_text, которые вернули бы текст самого триггера, DBCC INPUTBUFFER — вернет последнюю инструкцию, отправленную серверу, в которой будет весь текст пакета.

Для того чтобы иметь возможность записать данные, возвращаемые DBCC, воспользуемся маленькой хитростью, при помощи динамического sql, обернем выполнение этой команды в вызов хранимой процедуры и запишем результат процедуры во временную таблицу, пользуясь синтаксисом insert … exec.
В итоге, выглядит это все так:

/*
создаем тестовые таблицы, одну - которую будем логировать,
вторую - в которую будем писать лог
*/
if object_id('dbo.TriggerTable') is not null drop table dbo.TriggerTable
if object_id('dbo.LogTable') is not null drop table dbo.LogTable
create table dbo.TriggerTable( field int )
create table dbo.LogTable( field nvarchar(4000) )

/*создаем триггер на вставку*/
go
create trigger TriggerTable_insert on  dbo. TriggerTable after insert as
begin
	set nocount on;

	declare @temp table(EventType nvarchar (30), Parameters int, EventInfo nvarchar(4000) )
	insert into @temp
	exec sp_executesql N'DBCC inputbuffer (@@spid) WITH NO_INFOMSGS'

	insert into dbo.LogTable
	select EventInfo from @temp

end

/* добавляем значения */
go
insert into dbo.TriggerTable values (1)
go
insert into dbo.TriggerTable values (2)
go

/* проверяем что записалось в лог */
select * from dbo.LogTable
go

/* удаляем тестовые таблицы */
drop table dbo.LogTable
drop table dbo.TriggerTable
go

результат:

field
----------------------------------------
insert into dbo.TriggerTable values (1)
insert into dbo.TriggerTable values (2)
(2 row(s) affected)

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

/*
создаем тестовые таблицы, одну - которую будем логировать,
вторую - в которую будем писать лог
*/
if object_id('dbo.TriggerTable') is not null drop table dbo.TriggerTable
if object_id('dbo.LogTable') is not null drop table dbo.LogTable
if object_id('dbo.TriggerTable_add') is not null drop proc dbo.TriggerTable_add
create table dbo.TriggerTable( field int )
create table dbo.LogTable( field nvarchar(4000) )

/*создаем триггер на вставку*/
go
create trigger TriggerTable_insert on  dbo.TriggerTable after insert as
begin
	set nocount on;

	declare @temp table(EventType nvarchar (30), Parameters int, EventInfo nvarchar(4000) )
	insert into @temp
	exec sp_executesql N'DBCC inputbuffer (@@spid) WITH NO_INFOMSGS'

	insert into dbo. LogTable
	select EventInfo from @temp

end

/* добавляем значения */

/* Тут все в порядке, в лог попадает запрос на вставку*/
go
insert into dbo.TriggerTable values (1)
go
/* Вот тут в лог попадет все что до и после инсерта, т.е. все содержимое пакета*/
go
-- попадает все что есть в пакете
select 1
insert into dbo.TriggerTable values (2)
select 2
go
/* Тут в лог попадет только сам вызов процедуры*/
create proc dbo.TriggerTable_add @value int
as
insert into dbo.TriggerTable values (@value)
go
exec dbo.TriggerTable_add 3
go
/* проверяем что записалось в лог */
select * from dbo.LogTable
go

/* удаляем тестовые таблицы */
drop proc TriggerTable_add
drop table dbo.LogTable
drop table dbo.TriggerTable
go

результат:

field
----------------------------------------
insert into dbo.TriggerTable values (1)
-- попадает все что есть в пакете
select 1
insert into dbo. TriggerTable values (2)
select 2
exec dbo.TriggerTable_add 3

(3 row(s) affected)

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

триггеров в SQL Server

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

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

В SQL Server

есть три типа триггеров.

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

DML-триггеры

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

Используйте оператор CREATE TRIGGER для создания триггера в SQL Server.

 СОЗДАТЬ ТРИГГЕР [имя_схемы.]имя_триггера
ON { имя_таблицы | представление_имя }
{ ЗА | ПОСЛЕ | ВМЕСТО } {[ВСТАВИТЬ],[ОБНОВИТЬ],[УДАЛИТЬ]}
[НЕ ДЛЯ РЕПЛИКАЦИИ]
В КАЧЕСТВЕ
    {sql_statements}
 

В приведенном выше синтаксисе:

• имя_схемы (необязательно) — имя схемы, в которой будет создан новый триггер.
• trigger_name — это имя нового триггера.
• ВКЛ { имя_таблицы | имя_представления } Ключевое слово указывает имя таблицы или представления, для которого будет создан триггер.
• Предложение AFTER определяет событие INSERT, UPDATE или DELETE, которое запускает триггер. Предложение AFTER указывает, что триггер срабатывает только после того, как SQL Server успешно завершит выполнение действия, вызвавшего его срабатывание.
  Все остальные действия и ограничения должны быть успешно выполнены до срабатывания триггера.

• Предложение INSTEAD OF используется для пропуска инструкции INSERT, UPDATE или DELETE к таблице и вместо этого выполняет другие инструкции, определенные в триггере.
  Таким образом, фактическая инструкция INSERT, UPDATE или DELETE вообще не выполняется. Предложение INSTEAD OF нельзя использовать в триггерах DDL.

• Предложение [NOT FOR REPLICATION] указывает, что SQL Server не должен вызывать триггер, когда агент репликации изменяет таблицу.
• sql_statements указывает действие, которое должно выполняться при возникновении события.

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

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

 СОЗДАТЬ ТАБЛИЦУ EmpLog (
LogID int IDENTITY(1,1) NOT NULL,
EmpID int NOT NULL,
Операция nvarchar(10) НЕ NULL,
UpdatedDate Дата и время НЕ NULL
)
 

В приведенной выше таблице LogID — это серийный номер с автоматическим увеличением, UpdatedDate — это дата обновления таблицы Employee .
9Столбец 0005 Operation хранит тип операции, выполненной в таблице; либо «ВСТАВИТЬ», «ОБНОВИТЬ», либо «УДАЛИТЬ».

ДЛЯ триггеров

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

Следующий триггер FOR срабатывает при операции INSERT на Сотрудник таблица.

 СОЗДАТЬ ТРИГГЕР dbo.trgEmployeeInsert
ON dbo.Employee
ДЛЯ ВСТАВКИ
В КАЧЕСТВЕ
    ВСТАВИТЬ В dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID, 'INSERT', GETDATE() FROM INSERTED; --виртуальная таблица ВСТАВЛЕНА
 

Приведенное выше действие создаст триггер tgEmployeeInsert в папке -> Triggers, как показано ниже.

Триггеры в SQL Server

Выполните инструкции select в таблицах Employee и EmpLog , чтобы просмотреть существующие записи.

Ниже приведена таблица EmpLog .

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

 INSERT INTO Сотрудник (Имя
           ,Фамилия
           ,Эл. адрес
           ,Телефон
           ,Дата приема на работу
           ,Идентификатор менеджера
           ,Зарплата
           ,идентификатор отдела)
     ЗНАЧЕНИЯ('Маниша'
           , "Датт"
           ,'[электронная почта защищена]'
           ,6799878453
           ,'07. 11.2015'
           ,5
           ,50000
           ,20)
 

Приведенное выше действие вставит новую строку в таблицу Сотрудник , как показано ниже.

tgEmployeeInsert будет запущен и вставит строку в таблицу EmpLog , как показано ниже.

Вы можете видеть, что новая строка вставляется в таблицу EmpLog для каждого оператора INSERT для Сотрудник таблица.

Примечание. По какой-либо причине, если триггеры FOR завершатся неудачно, то и INSERT также завершится ошибкой, и никакие строки не будут вставлены.

Триггеры ПОСЛЕ

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

Например, следующий триггер будет запускаться после каждого оператора UPDATE в таблице Employee .

 СОЗДАТЬ ТРИГГЕР dbo. trgEmployeeUpdate
ON dbo.Employee
ПОСЛЕ ОБНОВЛЕНИЯ
В КАЧЕСТВЕ
    ВСТАВИТЬ В dbo.EmpLog(EmpID, Operation, UpdatedDate)
    SELECT EmployeeID, 'ОБНОВЛЕНИЕ', ПОЛУЧИТЬ () ИЗ УДАЛЕНЫХ;
 

Чтобы проверить этот триггер, выполните следующую инструкцию UPDATE.

 ОБНОВЛЕНИЕ Сотрудник
ЗП = 55000
ГДЕ СотрудникID = 2;
 

Теперь выберите строки из таблицы EmpLog . 9Триггер 0005 tgEmployeeUpdate должен был вставить новую строку в таблицу EmpLog , как показано ниже.

ВМЕСТО Триггеров

Триггер INSTEAD OF позволяет переопределить операции INSERT, UPDATE или DELETE в таблице или представлении.
Фактические операции DML вообще не выполняются.

Триггер INSTEAD OF DELETE выполняется вместо фактического события удаления в таблице или представлении. В приведенном ниже примере триггера «Вместо удаления» при выполнении команды удаления для таблицы «Сотрудник» в таблице «9» создается новая строка. 0005 Таблица EmpLog сохраняет операцию как «Удалить», но строка не удаляется.

 СОЗДАТЬ ТРИГГЕР dbo.trgInsteadOfDelete
ON dbo.Employee
ВМЕСТО УДАЛЕНИЯ
В КАЧЕСТВЕ
    ВСТАВИТЬ В dbo.EmpLog(EmpID, Operation, UpdatedDate)
    ВЫБЕРИТЕ ID сотрудника, 'УДАЛИТЬ', ПОЛУЧИТЬ ДАТУ () ИЗ УДАЛЕННОГО;
 

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

 УДАЛИТЬ ОТ Сотрудника
ГДЕ Сотрудник ID = 16;
 

Приведенный выше оператор запускает триггер trgInsteadOfDelete , который вставляет новую строку в таблицу EmpLog вместо удаления строки в таблице Employee .

Триггер INSTEAD OF DELETE работает таким же образом и для массового удаления.
Когда вы запускаете оператор SQL, удаляющий несколько строк, строки не будут удалены, но равное количество строк будет вставлено в EmpLog 9. 0006 таблица.

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

В SQL Server для одного и того же события в таблице можно создать несколько триггеров. Для этих триггеров нет определенного порядка выполнения.

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

 sp_settriggerorder [ @triggername = ] 'имя_триггера',
[ @order = ] 'значение',
[ @stmttype = ] 'тип_оператора',
[ @namespace = { 'БАЗА ДАННЫХ' | 'СЕРВЕР' | НУЛЕВОЙ } ]
 

Аргументы:

• Triggername — имя триггера, который нужно заказать.

• @order = Порядок триггера. Первый, последний или нет

• @stmttype = тип оператора. INSERT UPDATE, DELETE, LOGON или любое событие оператора TSQL, указанное в событиях DDL.

• @namespace указывает, был ли триггер DDL создан в базе данных или на сервере.

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

 sp_settriggerorder @triggername= 'dbo.trgEmployeeUpdate',
    @order='Первый',
    @stmttype = 'ОБНОВЛЕНИЕ';
 

Создание триггера DML с помощью SSMS

Шаг 1. Откройте SSMS и войдите на сервер базы данных. В обозревателе объектов разверните экземпляр базы данных и выберите базу данных, в которой вы хотите создать триггер.

Шаг 2: Разверните таблицу, в которой вы хотите создать триггер. Щелкните правой кнопкой мыши папку «Триггеры» и выберите «Новый триггер». Синтаксис CREATE TRIGGER для нового триггера откроется в редакторе запросов.

Шаг 3: В меню «Запрос» нажмите «Указать значения для параметров шаблона».

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

Шаг 4. В редакторе запросов введите операторы SQL для триггера в разделе с комментариями — вставьте здесь операторы для триггера.

Шаг 5: Вы можете проверить синтаксис, щелкнув Parse в меню Query.

Шаг 6: Нажмите «Выполнить», чтобы создать триггер.

Шаг 7: Обновите таблицу. Новый триггер будет создан в папке Triggers таблицы.

Таким образом, в SSMS можно создавать триггеры.

триггеров в SQL Server

В этой статье мы рассмотрим триггеры в SQL Server, различные типы событий триггеров, порядок триггеров и НЕ ДЛЯ РЕПЛИКАЦИИ в триггерах. Триггер — это объект базы данных, который запускается автоматически при возникновении события. Есть три разных типа событий.

• DML-события
• DDL-события
• Событие LOGON – Триггер входа в систему срабатывает, когда происходит событие LOGON, т. е. когда устанавливается сеанс пользователя.

Триггеры DML в SQL Server

Триггеры DML в SQL Server запускаются при возникновении события DML. то есть когда данные вставляются/обновляются/удаляются в таблице пользователем.

Создание триггеров для события DML

Давайте создадим несколько примеров таблиц и триггеров в SQL Server.

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

Ниже приведен пример синтаксиса для создания триггера DML для события обновления.

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

Вместо триггеров в SQL Server

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

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

Синтаксис T-SQL для создания триггера вместо триггера

• Если в таблице есть несколько триггеров вместе с триггером вместо триггера, вместо триггера срабатывает первый в порядке.
• ВМЕСТО триггеров можно создавать на представлениях
• мы можем определить только один триггер вместо триггера для инструкции INSERT, UPDATE или DELETE в таблице или представлении.

Включение и отключение триггеров DML для таблицы

Перейдите к папке триггеров на уровне таблицы, выберите триггер, щелкните правой кнопкой мыши триггер и щелкните Включить / Отключить , чтобы включить или отключить триггер с помощью SSMS .

Отключение определенного триггера SQL Server для таблицы с помощью T-SQL.

Включение определенного триггера для таблицы с помощью T-SQL.

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

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

Перетаскивание триггера на стол.

Чтобы сбросить триггер DML в таблицу с помощью студии управления SQL Server, перейдите к Запускает папку под столом. Выберите таблицу, которую хотите удалить, щелкните правой кнопкой мыши триггер и выберите Удалить . Нажмите Хорошо .

T-SQL, чтобы сбросить триггер на таблицу.

Удаление таблицы приведет к удалению всех триггеров SQL Server в таблице вместе с таблицей.

Триггеры DDL

Триггеры DDL в SQL Server запускаются при событиях DDL. т. е. против операторов создания, изменения и удаления и т. д. Эти триггеры создаются на уровне базы данных или на уровне сервера в зависимости от типа события DDL.

Эти триггеры полезны в следующих случаях.

• Предотвращение изменений в схеме базы данных
• Аудит изменений схемы базы данных
• Реагировать на изменение схемы базы данных

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

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

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

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

Чтобы просмотреть триггеры уровня базы данных, войдите на сервер с помощью студии управления SQL Server и перейдите к базе данных. Разверните базу данных и перейдите к Программируемость -> Триггеры базы данных.

Чтобы просмотреть триггеры на уровне сервера, войдите на сервер с помощью SSMS и перейдите в папку Server Objects , а затем в папку Triggers .

Включение и отключение триггеров DDL

Используйте приведенный ниже синтаксис T-SQL, чтобы отключить или включить триггер DDL на уровне базы данных.

Используйте приведенный ниже синтаксис T-SQL, чтобы удалить триггер DDL, созданный на уровне базы данных.

Триггеры входа в систему SQL Server

Эти триггеры в SQL Server срабатывают в ответ на событие LOGON. LOGON вызывает срабатывание после успешной аутентификации и до установления сеанса пользователя.

Триггеры LOGON создаются на уровне сервера и полезны в следующих случаях.

1. Для аудита активности входа
2. Чтобы контролировать активность входа в систему

Создание триггеров LOGON

Вы можете использовать EVENTDATA() и написать собственный код для отслеживания или управления соединениями. Здесь я создаю простые триггеры в SQL Server для события LOGON. Ниже приведен пример синтаксиса для создания триггера LOGON.

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

Включение и отключение триггеров LOGON

Используйте приведенный ниже синтаксис T-SQL, чтобы отключить или включить триггер LOGON.

Используйте приведенный ниже синтаксис T-SQL, чтобы удалить триггер LOGON.

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

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

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

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

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

Чтобы изменить параметр RECURSIVE_TRIGGERS с помощью SSMS, перейдите к базе данных, щелкните ее правой кнопкой мыши и выберите Properties. Нажмите Параметры и измените настройку на нужную.

Чтобы отключить RECURSIVE_TRIGGERS с помощью T-SQL, используйте следующую инструкцию и замените имя базы данных на имя вашей базы данных.

Чтобы установить RECURSIVE_TRIGGERS ON с помощью T-SQL, используйте инструкцию ниже и замените имя базы данных на имя вашей базы данных.

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

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

Ниже приведен пример триггера для косвенной рекурсии.

Теперь, когда мы обновляем значение в таблице Temp1, срабатывает триггер TR_Temp1, который обновляет таблицу Temp2. TR_Temp2 запускается и обновляет таблицу Temp1, что приводит к повторному запуску TR_Temp1.

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

Порядок запуска SQL Server

SQL Server допускает несколько триггеров в таблице для одного и того же события, и не существует определенного порядка выполнения этих триггеров.

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

Ниже приведен пример синтаксиса для установки порядка триггера на первый для инструкции INSERT.

Теперь при вставке данных в таблицу TriggerOrderTest происходит событие INSERT и первым срабатывает триггер TR_3.

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

Ниже приведен пример синтаксиса для установки порядка запуска DDL.

НЕ ДЛЯ РЕПЛИКАЦИИ

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

Например, если вы реплицируете как Locations, так и LocationHist. Теперь, когда вы обновляете запись о местоположении, срабатывает триггер, который вставляет запись в таблицу истории. Когда эти изменения синхронизируются с другим концом (подписчиками), нет необходимости в повторном срабатывании триггера. Итак, если мы пометим триггер как «НЕ ДЛЯ РЕПЛИКАЦИИ», триггер не сработает, когда агент репликации синхронизирует изменения, и сработает только для изменений данных, сделанных пользователем.

Ниже приведен пример синтаксиса для создания триггеров в SQL Server без репликации.

Если вы хотите, чтобы триггеры в SQL Server срабатывали при изменении данных синхронизации агента репликации на другом конце, просто создайте триггер, не указывая «НЕ ДЛЯ РЕПЛИКАЦИИ».