Триггеры

Триггеры являются наиболее эффективным инструментом сохранения целостности баз данных, так как позволяют подробно проанализировать события, происходящие в системе.  Все триггеры можно разделить на два класса: триггеры DML – перехват  команд insert, update, delete и триггеры DDL – перехват  команд DDL. В свою очередь триггеры DML делятся на триггеры After – выполняющиеся после выполнения команды и триггеры Instead of – триггеры выполняются вместо соответствующих команд SQL.

Триггеры DML

Структура и создание

Формат команды создания триггера следующий

create trigger [ schema_name . ]trigger_name

on { table | view }

[ with <dml_trigger_option> [ ,…n ] ]

{ for | after | instead of }

{ [ insert ] [ , ] [ update ] [ , ] [ delete ] }

[ with append ]

[ not for replication ]

as

{ sql_statement  [ ; ] [ …n ] |

external name <method specifier [ ; ] > }

Здесь

■           [ schema_name . ]trigger_name – имя создаваемого триггера. Должно удовлетворять требованиям, предъявляемым к идентификаторам. Имя триггера не может начинаться с символа ‘#’, поскольку триггер не может быть временным объектом.

■           on { table | view } – опция указывает к какой таблице или представлению (имя таблицы или представления) триггер будет относиться.  Только триггеры типа instead of могут создаваться для представлений.

■                       <dml_trigger_option> — опции триггера.  Можно использовать два вида опций  encryption (шифровать)  и    execution as clause (задать контекст), но с ними мы уже знакомы.

■                       for | after | instead of – определяется тип триггера. Опции и for и after являются синонимами.

■           [ delete ] [ , ] [ insert ] [ , ] [ update ] —  указываются команды DML, на которые будет реагировать данный триггер.  Следует указать, по крайней мере, одну команду.

■           with append  — данная опция устарела и используется для совместимости.

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

■           sql_statement  [ ; ] [ …n ] – последовательность команд языка Transact SQL, которые будут выполняться, при запуске триггера.

■           external name <method specifier >– опция используется при создании триггера на основе технологии  .NET.

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

alter trigger schema_name.trigger_name

on ( table | view )

[ with <dml_trigger_option> [ ,…n ] ]

( for | after | instead of )

{ [ delete ] [ , ] [ insert ] [ , ] [ update ] }

[ not for replication ]

as { sql_statement [ ; ] [ . ..n ] |

external name <method specifier> [ ; ] }

Удаление триггера DDL осуществляется командой drop trigger schema_name.trigger_name [ ,…n ]. Т.е. одной командой можно удалить сразу несколько триггеров.

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

Триггеры instead of выполняются вместо операций DML и по одному для каждой операции. Эти триггеры могут создаваться и для представлений.

В триггерах after используются специальный инструментарий для определения того, что произошло с таблицей (чем вызван запуск триггера). Функция update (column) определяет, был или не был модифицирован данный столбец. Относиться к командам update и insert. Если столбец был модифицирован, то функция возвращает TRUE. Функция columns_updated() позволяет определить, какой столбец был изменении.  Функция возвращает двоичное число, каждый бит которого относиться к конкретному столбцу. Если бит равен 1 то это значит, что столбец был изменен командой update или insert.

Перед выполнением для триггера автоматически создаются две временные таблицы: inserted и deleted. Их содержимое зависит от того, какая операция была выполнена:

■           При выполнении команды insert таблица inserted будет содержать новые строки. Таблица deleted будет пуста.

■           При выполнении команды delete таблица deleted будет содержать удаляемые строки, таблица inserted будет пустой.

■           При выполнении команды update таблица deleted будет содержать старые значения строк, таблица inserted – новые.

В случае триггера instead of таблицы deleted и inserted будут содержать строки, которые соответственно должны быть удалены или должны быть вставлены. 

Во временные таблицы (deleted и inserted) нельзя вносить какие-либо изменения. Но из триггера можно менять содержимое любых других таблиц. Из триггера нельзя выполнять следующие команды языка Transact SQL: reconfigure, create database, alter database, drop database, restore database, restore log, load database, load log.

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

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

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

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

sp_settriggerorder[@triggername = ] ‘triggername’

, [@order = ] ‘value’

, [@stmttype = ] ‘statement_type’

Здесь

■           [@triggername = ] ‘triggername’ – определяет имя триггера, которое может содержаться и в переменной.

■           [@order = ] ‘value’ – порядок следования триггера. Который может быть:

o        first – выполняется первый.

o        last – выполняется второй.

o        none – порядок не определен.

■           [@stmttype = ] ‘statement_type’ – тип инструкции.

 

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

Наконец триггер не возвращать никаких наборов строк. Эта возможность считается устаревшей и будет удалена в будущих версиях SQL Server.

Примеры триггеров

В Листинге 3.83 представлен простой триггер after, который запрещает обновлять значение столбца t1 для таблицы table1. При этом в вызывающий модуль с помощью функции raiserror будет возвращено сообщение о причине отказа. Поскольку триггер расположен в одной транзакции с командой, которая вызвала данный триггер, то команда rollback transaction возвращает состояние таблицы в исходное положение.

Листинг 3. 83

create trigger no_update on table1

after update

as

if update(t1)

begin

        raiserror (‘Обновлять нельзя’, 15,1)

        rollback transaction

end

Рассмотрим еще один пример (см. Листинг 3.84).

Листинг 3.84

create trigger dt_del on dbo.students

instead of delete

as

begin

—удалить из таблицы marks

        delete from dbo.marks

        where id_student in (select id from deleted)

        if(@@error>0)

        begin

                rollback transaction

                raiserror(‘Ошибка удаления из таблицы marks’,16,3)

                return

        end

—удалить из таблицы students

        delete from dbo.students

        where id in (select id from deleted)

        if(@@error>0)

        begin

                rollback transaction

                raiserror(‘Ошибка удаления из таблицы students’,16,3)

                return

        end

return

end

Триггер из Листинга 3. 84 срабатывает на попытку удаления из таблицы dbo.students. Триггер в начале удаляет строки из связанной таблицы dbo.marks, а затем уже из таблицы dbo.students (каскадное удаление). При этом в триггере обрабатываются и возможные ошибки с откатом тразнакции и возвращаением в приложение сообщения об ошибке.

Триггеры DDL

Триггеры DDL запускаются в ответ на команды DDL.

Вот формат команды создания триггера DDL

create trigger_name

on { all server | database }

[ with <ddl_trigger_option> [ ,…n ] ]

{ for | after } { event_type | event_group } [ ,…n ]

as { sql_statement  [ ; ] [ …n ]

| external name < method specifier >  [ ; ] }

Здесь

■           on { all server | database } – данная опция показывает, будет ли триггер действовать в пределах текущей базы данных или в пределах всего сервера SQL.

■           <ddl_trigger_option> — данная опция аналогична такой же опции для триггеров DML.

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

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

Поскольку остальные опции нам уже знакомы, перейдем сразу к примеру (см. Листинг 3.85).

Листинг 3.85

create trigger tr1

on database

for DDL_TABLE_EVENTS –триггер на операции с таблицами

as

begin

        rollback transaction

        raiserror(‘Операции над таблицами запрещены’,16,3)

        return

end

При работе с DDL триггерами удобно использовать функцию eventdata().  Данная функция, запущенная внутри триггера возвращает полную информацию о происшедшем событии. Особенностью функции является то, что информация, которую она возвращает, имеет структуру xml-документа.

SQL.RU | Триггеры и порядок их выполнения

До версии SQL Server 7.0, мы могли создать только три триггера на одной таблице, один INSERT, один UPDATE и один DELETE. Ситуация изменилась с выходом SQL Server 7.0. Теперь мы можем на одну таблицу задать большое количество (Ограничено числом объектов в базе данных) триггеров каждого типа.

Порядок запуска триггеров при этом никак не определен и не гарантирован. В SQL Server 2000 появилась документированная процедура sp_settriggerorder, которая позволяет изменять порядок запуска триггеров. Она позволяет указать какие триггеры AFTER срабатывают первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке.

Для демонстрации запустим скрипт:

--Тестовая таблица
if object_id( 'dbo. TestTable', 'U' ) is not null
  drop table dbo.TestTable
go
create table dbo.TestTable ( id int identity, val varchar(64) )
go

--Триггер 1
if object_id ( 'dbo.TestTrigger01', 'TR' ) is not null
  drop trigger dbo.TestTrigger01
go
create trigger dbo.TestTrigger01 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 2
if object_id ( 'dbo.TestTrigger02', 'TR' ) is not null
  drop trigger dbo.TestTrigger02
go
create trigger dbo.TestTrigger02 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 3
if object_id ( 'dbo.TestTrigger03', 'TR' ) is not null
  drop trigger dbo.TestTrigger03
go
create trigger dbo.TestTrigger03 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo. TestTable
values ( object_name( @@procid ) )
go

--Триггер 4
if object_id ( 'dbo.TestTrigger04', 'TR' ) is not null
  drop trigger dbo.TestTrigger04
go
create trigger dbo.TestTrigger04 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go

--Триггер 5
if object_id ( 'dbo.TestTrigger05', 'TR' ) is not null
  drop trigger dbo.TestTrigger05
go
create trigger dbo.TestTrigger05 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go
Мы создали тестовую таблицу с 5-ю триггерами AFTER на событие INSERT. Каждый из триггеров добавляет запись в эту же таблицу. Теперь проведем 1ый тест, выполнив следующий скрипт: 


--Тест 1: Порядок запуска триггеров не задан
insert into dbo.TestTable
select 'Test1'
go
select * from dbo. TestTable
order by id
go

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


--Тест 2: Порядок запуска триггеров не задан, триггер №2 пересоздадим, чтобы он стал самым поздним по времени создания
--Триггер 2
if object_id ( 'dbo.TestTrigger02', 'TR' ) is not null
  drop trigger dbo.TestTrigger02
go
create trigger dbo.TestTrigger02 on dbo.TestTable
for insert
as
if @@nestlevel > 1 return
insert into dbo.TestTable
values ( object_name( @@procid ) )
go
insert into dbo.TestTable
select 'Test2'
go
select * from dbo.TestTable
order by id
go

Теория подтвердилась, но это всего лишь совпадение, и такое поведение не гарантировано и, как указано в БОЛ, без явного указания порядка, триггеры выполняются случайным образом. Но через процедуру sp_settriggerorder мы можем задать только триггеры AFTER, срабатывающие первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке. Т.е. если у нас 3 триггера на одно событие, то мы сможем гарантировать порядок срабатывания, через эту процедуру. Но если триггеров больше, как в нашем случае, то порядок будет гарантирован только для первого и последнего триггера. Для иллюстрации работы процедуры sp_settriggerorder, укажем в качестве первого триггера Триггер 2, а последним Триггер 3: 


--Тест 3: Зададим порядок запуска триггеров 2 и 3
exec sp_settriggerorder 'dbo.TestTrigger02', 'first', 'insert'
go
exec sp_settriggerorder 'dbo.TestTrigger03', 'last', 'insert'
go
insert into dbo.TestTable
select 'Test3'
go
select * from dbo.TestTable
order by id
go

При попытке задать для другого триггера порядок выполнения, например первым (first), мы получим ошибку:
Msg 15130, Level 16, State 1, Procedure sp_settriggerorder, Line 163
There already exists a ‘first’ trigger for ‘insert’.

Просмотреть, задан ли порядок запуска для триггера можно с помощью функции OBJECTPROPERTY 


select name
     , objectproperty( object_id, 'ExecIsFirstInsertTrigger') FirstInsertTrigger 
     , objectproperty( object_id, 'ExecIsLastInsertTrigger')  LastInsertTrigger 
from sys.triggers
where name like 'TestTrigger0%'

На что ещё стоит обратить внимание: Если один и тот же триггер необходимо назначить в качестве первого или последнего для нескольких типов инструкций, процедуру sp_settriggerorder необходимо выполнить для каждого типа инструкций. Кроме того, прежде чем триггер может быть назначен в качестве первого (First) или последнего (Last) выполняющегося триггера для того или иного типа инструкций, этот триггер должен быть сначала определен для данного типа.

Ну и последний тест, сбросим порядок запуска триггеров 2 и 3: 


--Тест 4: Сбросим порядок запуска триггеров 2 и 3
exec sp_settriggerorder 'dbo. TestTrigger02', 'none', 'insert'
go
exec sp_settriggerorder 'dbo.TestTrigger03', 'none', 'insert'
go
insert into dbo.TestTable
select 'Test4'
go
select * from dbo.TestTable
order by id
go

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

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

Редактировать

Твиттер LinkedIn Фейсбук Электронная почта

  • Статья

Применяется к: SQL Server База данных SQL Azure Управляемый экземпляр Azure SQL

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

Преимущества триггера DML

Триггеры DML аналогичны ограничениям в том, что они могут обеспечивать целостность объектов или доменов. Как правило, целостность объекта всегда должна обеспечиваться на самом низком уровне с помощью индексов, которые являются частью ограничений PRIMARY KEY и UNIQUE или создаются независимо от ограничений. Целостность домена должна обеспечиваться с помощью ограничений CHECK, а ссылочная целостность (RI) должна обеспечиваться с помощью ограничений FOREIGN KEY. Триггеры DML наиболее полезны, когда функции, поддерживаемые ограничениями, не могут удовлетворить функциональные потребности приложения.

В следующем списке триггеры DML сравниваются с ограничениями и определяются, когда триггеры DML имеют преимущества перед ограничениями.

  • Триггеры DML могут каскадировать изменения через связанные таблицы в базе данных; однако эти изменения можно выполнить более эффективно, используя каскадные ограничения ссылочной целостности. Ограничения FOREIGN KEY могут проверять значение столбца только при точном совпадении со значением в другом столбце, если только предложение REFERENCES не определяет каскадное ссылочное действие.

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

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

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

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

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

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

  • Если в таблице триггеров существуют ограничения, они проверяются после выполнения триггера INSTEAD OF, но до выполнения триггера AFTER. Если ограничения нарушаются, действия триггера INSTEAD OF отменяются, а триггер AFTER не выполняется.

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

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

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

В этой таблице сравниваются функции триггеров AFTER и INSTEAD OF.

Функция Триггер ПОСЛЕ ВМЕСТО триггера
Применимость Столы Таблицы и представления
Количество на таблицу или представление Несколько на одно действие триггера (ОБНОВЛЕНИЕ, УДАЛЕНИЕ и ВСТАВКА) По одному на действие триггера (ОБНОВЛЕНИЕ, УДАЛЕНИЕ и ВСТАВКА)
Каскадные ссылки Ограничений нет Триггеры INSTEAD OF UPDATE и DELETE не разрешены для таблиц, являющихся целью каскадных ограничений ссылочной целостности.
Исполнение После:

Обработка ограничений

Декларативные ссылочные действия

вставлены и удалены создание таблиц

Триггерное действие

 Раньше: обработка зависимостей

Вместо: Инициирующего действия

После: вставлено и удалено создание таблиц

Порядок исполнения Можно указать первое и последнее выполнение Неприменимо
varchar(max) , nvarchar(max) и varbinary(max) ссылки на столбцы в вставлены и удалены таблицы Разрешено Разрешено
текст , ntext и изображение ссылки на столбцы в вставлены и удалены таблицы Не разрешено Разрешено

Триггеры CLR
Триггер CLR может быть триггером AFTER или INSTEAD OF. Триггер CLR также может быть триггером DDL. Вместо выполнения хранимой процедуры Transact-SQL триггер CLR выполняет один или несколько методов, написанных в управляемом коде, которые являются членами сборки, созданной в .NET Framework и загруженной в SQL Server.

Задача Тема
Описывает создание триггера DML. Создание триггеров DML
Описывает, как создать триггер CLR. Создание триггеров CLR
Описывает, как создать триггер DML для обработки изменений как однострочных, так и многострочных данных. Создание триггеров DML для обработки нескольких строк данных
Описывает вложение триггеров. Создание вложенных триггеров
Описывает порядок срабатывания триггеров AFTER. Укажите первый и последний триггеры
Описывает использование специальных таблиц вставки и удаления в коде триггера. Использовать вставленные и удаленные таблицы
Описывает, как изменить или переименовать триггер DML. Изменить или переименовать триггеры DML
Описывает, как просмотреть информацию о триггерах DML. Получить информацию о триггерах DML
Описывает, как удалить или отключить триггеры DML. Удалить или отключить триггеры DML
Описывает, как управлять безопасностью триггера. Управление безопасностью триггера

См. также

CREATE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
Trigger Functions (Transact-SQL) -SQL)

Обратная связь

Просмотреть все отзывы о странице

Пример триггера SQL Server

Автор: Daniel Farina   | Обновлено: 17 марта 2022 г.   | Комментарии (11)   | Связанный: Еще > Триггеры


Проблема

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

Решение

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

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

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

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

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

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

Классы триггеров SQL Server

В SQL Server существует два класса триггеров:

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

Кроме того, триггеры DML бывают разных типов:

  • FOR или AFTER [INSERT, UPDATE, DELETE]: Эти типы триггеров выполняются после окончания оператора запуска (вставка, обновление или удаление).
  • ВМЕСТО [ВСТАВИТЬ, ОБНОВИТЬ, УДАЛИТЬ]: В отличие от типа FOR (ПОСЛЕ), Триггеры INSTEAD OF выполняются вместо оператора срабатывания. Другими словами, этот тип триггера заменяет оператор запуска. Это очень удобно в случаях где вам нужно иметь ссылочную целостность кросс-базы данных.

В чем важность триггеров SQL Server?

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

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

Как узнать, какие строки были обновлены, вставлены или удалены с помощью SQL Server DML-триггер?

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

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

Синтаксис триггера SQL Server DML

В следующем разделе кода вы увидите основной синтаксис CREATE TRIGGER.

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

Кроме того, в следующей таблице описаны все аргументы команды CREATE TRIGGER. синтаксис.

Аргумент Описание
С <Опции> В этом аргументе можно указать дополнительные параметры создания триггера. Я расскажу об этом дальше.
ДЛЯ | ПОСЛЕ | ВМЕСТО Указывает, когда должен срабатывать триггер при возникновении определенного события, например событие вставки, обновления или удаления.
[ВСТАВИТЬ], [ОБНОВИТЬ], [УДАЛИТЬ] Событие DML (или список событий), которое вызовет срабатывание триггера.
С опцией Описание Замечания
ШИФРОВАНИЕ Шифрует код триггера. Не работает с таблицами, оптимизированными для памяти
ВЫПОЛНИТЬ КАК Изменяет контекст безопасности, в котором будет выполняться триггер Требуется для триггеров в таблицах, оптимизированных для памяти.
СОБСТВЕННАЯ_КОМПИЛЯЦИЯ Компилирует код триггера в двоичный файл, чтобы он работал в собственном коде. Требуется для триггеров в таблицах, оптимизированных для памяти.
СХЕМА ПРИВЯЗКИ Гарантирует невозможность удаления таблиц, на которые ссылается триггер. или изменен. Требуется для триггеров в таблицах, оптимизированных для памяти.

Сценарии использования триггеров SQL Server

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

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

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

Образец триггера SQL Server DML

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

Сначала мы должны создать таблицу «Сотрудники».

 СОЗДАТЬ ТАБЛИЦУ Сотрудники
    (
      Целое число EmployeeID NOT NULL IDENTITY(1, 1) ,
      имя_сотрудника VARCHAR(50) ,
      Адрес сотрудника VARCHAR(50) ,
      МесяцЗарплата NUMERIC(10, 2)
      ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАННЫЙ (EmployeeID)
    )
ВПЕРЕД

Затем мы должны создать таблицу EmployeesAudit для хранения записей аудита. Этот таблица имеет ту же структуру, что и таблица «Сотрудники», но включает столбец AuditId. в качестве первичного ключа, ModifiedDate, чтобы сохранить дату модификации, ModifiedBy, чтобы мы может знать, кто изменил таблицу «Сотрудники» и, наконец, операцию, где мы укажем операция DML, сгенерировавшая контрольную запись с одной из трех букв (I для вставить, U для обновления и D для удаления).

 СОЗДАТЬ ТАБЛИЦУ Сотрудников Аудит
    (
      AuditID INTEGER NOT NULL IDENTITY(1, 1) ,
      Идентификатор сотрудника ЦЕЛОЕ ЧИСЛО ,
      имя_сотрудника VARCHAR(50) ,
      Адрес сотрудника VARCHAR(50) ,
      MonthSalary NUMERIC(10, 2) ,
      Изменено VARCHAR(128),
      ModifiedDate ДАТАВРЕМЯ ,
      Операция СИМВОЛ(1)
      КЛАСТЕРИРОВАННЫЙ ПЕРВИЧНЫЙ КЛЮЧ ( AuditID )
    )
ВПЕРЕД

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

 ВСТАВИТЬ В dbo.Employees
        ( Имя сотрудника ,
          Адрес сотрудника ,
          МесяцЗарплата
        )
ВЫБЕРИТЕ «Марк Смит», «Ocean Dr 1234», 10000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ «Джо Райт», «Вечнозеленый 1234», 10000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ «Джон Доу», «Международный доктор 1234», 10000
СОЮЗ ВСЕХ
ВЫБЕРИТЕ «Питер Родригес», «74 Street 1234», 10000
ВПЕРЕД

Теперь, когда мы установили тестовую среду, пришло время создать наш триггер. Брать посмотрите на код ниже.

В основном код состоит из получения пользователя, который изменяет сотрудников. таблице, глядя на sys.dm_exec_sessions Динамическое управление Просмотр сеанса с текущим SPID. После этого триггер вставляет одну запись в таблице EmployeesAudit для каждой записи, вставленной, обновленной или удаленной в Таблица сотрудников, а также текущее время и операция DML, которая запустила курок.

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

Первый из этих запросов — это обновление.

 НАЧАТЬ ТРАНЗАКЦИЮ

ВЫБИРАТЬ  *
ОТ dbo.Employees
ГДЕ Сотрудник ID = 1
 
ОБНОВЛЕНИЕ Сотрудники
УСТАНОВИТЬ имя_сотрудника = 'zzz'
ГДЕ Сотрудник ID = 1
 
ВЫБИРАТЬ  *
ОТ dbo.Employees
ГДЕ Сотрудник ID = 1
 
ВЫБИРАТЬ  *
ОТ dbo.EmployeesAudit

ОТМЕНА ТРАНЗАКЦИИ

На следующем снимке экрана вы увидите обновленную запись в таблице «Сотрудники». и новая запись в EmployeesAudit, которая отслеживает операцию DML в течение таблицу «Сотрудники».

Второй запрос представляет собой вставку двух строк в таблицу «Сотрудники».

 НАЧАТЬ ТРАНЗАКЦИЮ

ВСТАВИТЬ В dbo.Employees
        ( Имя сотрудника ,
          Адрес сотрудника ,
          МесяцЗарплата
        )
        ВЫБЕРИТЕ «зз»,
                'дсда',
                10000
        СОЮЗ ВСЕХ
        ВЫБЕРИТЕ «Маркус Рубиус»,
                'дсда',
                6000

ВЫБИРАТЬ  *
ОТ dbo.Employees

ВЫБИРАТЬ  *
ОТ dbo.EmployeesAudit

ОТМЕНА ТРАНЗАКЦИИ

На следующем снимке экрана вы увидите две вставленные строки в разделе «Сотрудники». и соответствующую запись аудита в таблице EmployeesAudit.

Наконец, третий запрос — это оператор удаления таблицы «Сотрудники».

 НАЧАТЬ ТРАНЗАКЦИЮ

ВЫБИРАТЬ  *
ОТ dbo.Employees
ГДЕ Сотрудник ID = 1

УДАЛИТЬ ИЗ dbo.Employees
ГДЕ Сотрудник ID = 1
 
ВЫБИРАТЬ  *
ОТ dbo.EmployeesAudit

ВЫБИРАТЬ  *
ОТ dbo.Employees
ГДЕ Сотрудник ID = 1

ОТМЕНА ТРАНЗАКЦИИ

На следующем снимке экрана вы увидите, что строка удалена из таблицы «Сотрудники». и соответствующую запись аудита в таблице EmployeesAudit.

Следующие шаги
  • Если вам было трудно понять, как я зафиксировал, какую операцию DML было выполнено в таблице «Сотрудники», взгляните на этот совет: Общие сведения о вставленных и удаленных таблицах SQL Server для триггеров DML.
  • В качестве примера триггера INSTEAD OF вы можете посмотреть этот совет: Использование триггеров INSTEAD OF в SQL Server для операций DML.
  • Дополнительную информацию о DMV sys.dm_exec_sessions можно найти здесь: Понимание и использование sys.

    No related posts.