Разработка триггеров с помощью SQLCLR

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

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

Также как и триггеры, написанные на обычном Transact SQL, триггеры SQLCLR имеют доступ к системным таблицам «inserted» и «deleted».

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

Создадим триггер, который будет проверять данные, которые вносятся в базу данных при обновлении. Если предполагается обновление более одной записи или поле Email будет пустым, триггер выполнит откат транзакции и обновление не произойдёт.

Добавление триггера осуществляется по аналогии с другими объектами SQLCLR. В разделе «SQL CLR C#» окна добавления нового объекта просто выбирается соответствующий пункт и задаётся название будущего триггера.

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

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

Укажем для триггера следующий атрибут:

Вначале создадим подключение к базе данных и напишем запрос на проверку количества записей в таблице «inserted».

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

Блок «try-catch» необходим для корректного завершения работы триггера, так как процедура RAISERROR вызовет исключение.

Полный код триггера приведён ниже:

Протестируем работу триггера.

После загрузки сборки импортируем его с помощью следующей команды:

Попытаемся выполнить запрос, который при успешном выполнении обновит все записи в таблице Data.

В результате запрос не был выполнен. Все изменения были отменены.

ЗапретОбновления более одной записи

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

Обновление только одной записи

Теперь доработаем триггер и запретим ввод пустых значений в поле «Email» при обновлении.

Для этого добавим в оператор «if» который проверяет количество обновляемых записей блок «else». Такой подход позволяет исключить дополнительную проверку количества записей так как при проверке поля «Email» наличие в таблице «inserted» единственной записи будет гарантировано.

Протестируем обновлённую версию триггера.

Следующий запрос не выполнился.

Microsoft SQL Server выдал сообщение об ошибке.

Запрет пустого значения поля

Зато запрос, в котором поле «Email» не пустое выполнился без проблем.

[code lang=»sql»]UPDATE [dbo].[Data] SET [Email]=’testTrigger@example.com’ WHERE id=4[/code]

Ввод только непустого значения

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

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

В результате получаем такой «унифицированный» метод:

При этом сам триггер стал гораздо компактнее и проще.

Старые версии Microsoft SQL Server

Пример, который мы рассмотрели выше, приведён для Microsoft SQL Server.

Если Вы используете более раннюю версию, например 2008, то вместо атрибута «Microsoft.SqlServer.Server.SqlTrigger» необходимо использовать проверку типа операции с таблицей при помощи контекста триггера непосредственно в его коде. Проверку следует реализовать до выполнения любых других операций.

Для примера рассмотренного выше такая проверка выполняется следующим образом:

Триггеры Transact SQL или SQLCLR?

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

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

Во всех остальных случаях следует отдавать предпочтение Transact-SQL.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *