Разработка триггеров в SQLCLR имеет очень много общего с разработкой хранимых процедур, которая была рассмотрена ранее.
Также как и хранимые процедуры, триггеры представляют собой открытые статические методы «обычного» класса C#, которые помечены специальным атрибутом.
|
1 |
[Microsoft.SqlServer.Server.SqlTrigger (Name="Имя триггера", Target="Таблица, для которой он предназначен", Event="Событие, при котором срабатывает триггер")] |
Также как и триггеры, написанные на обычном Transact SQL, триггеры SQLCLR имеют доступ к системным таблицам «inserted» и «deleted».
Для примера снова обратимся к той же самой базе данных, что была рассмотрена в статье о хранимых процедурах.
Создадим триггер, который будет проверять данные, которые вносятся в базу данных при обновлении. Если предполагается обновление более одной записи или поле Email будет пустым, триггер выполнит откат транзакции и обновление не произойдёт.
Добавление триггера осуществляется по аналогии с другими объектами SQLCLR. В разделе «SQL CLR C#» окна добавления нового объекта просто выбирается соответствующий пункт и задаётся название будущего триггера.
Для начала реализуем ту часть триггера, которая запрещает обновление более одной записи.
Укажем для триггера следующий атрибут:
|
1 |
[Microsoft.SqlServer.Server.SqlTrigger (Name="RollbackTrigger", Target="Data", Event="FOR UPDATE")] |
Вначале создадим подключение к базе данных и напишем запрос на проверку количества записей в таблице «inserted».
|
1 2 3 4 5 |
using (SqlConnection conn =new SqlConnection("context connection = true")) { SqlCommand command = conn.CreateCommand(); command.CommandText = "SELECT count(*) FROM inserted"; conn.Open(); |
Теперь выполним запрос и, если результат его выполнения больше единицы, выполним откат транзакции.
|
1 2 3 4 5 6 7 8 9 10 |
if ((int)command.ExecuteScalar()>1) { command.CommandText = "RAISERROR('Вы пытаетесь обновить слишком много строк. Откат транзакции.',16,1); ROLLBACK TRAN;"; try { SqlContext.Pipe.ExecuteAndSend(command); } catch { } } |
Блок «try-catch» необходим для корректного завершения работы триггера, так как процедура RAISERROR вызовет исключение.
Полный код триггера приведён ниже:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[Microsoft.SqlServer.Server.SqlTrigger (Name="RollbackTrigger", Target="Data", Event="FOR UPDATE")] public static void RollbackTrigger () { using (SqlConnection conn =new SqlConnection("context connection = true")) { SqlCommand command = conn.CreateCommand(); command.CommandText = "SELECT count(*) FROM inserted"; conn.Open(); if ((int)command.ExecuteScalar()>1) { command.CommandText = "RAISERROR('Вы пытаетесь обновить слишком много строк. Откат транзакции.',16,1); ROLLBACK TRAN;"; try { SqlContext.Pipe.ExecuteAndSend(command); } сatch { } } } } |
Протестируем работу триггера.
После загрузки сборки импортируем его с помощью следующей команды:
|
1 2 3 |
CREATE TRIGGER [RollbackTrigger] ON [dbo].[Data] FOR UPDATE AS EXTERNAL NAME [MyTriggers].[Triggers].[RollbackTrigger]; |
Попытаемся выполнить запрос, который при успешном выполнении обновит все записи в таблице Data.
|
1 |
UPDATE [dbo].[Data] SET [Email]='25@example.com' |
В результате запрос не был выполнен. Все изменения были отменены.
В тоже время запрос на обновление только одной записи выполнен успешно.
|
1 |
UPDATE [dbo].[Data] SET [Email]='25@example.com' WHERE id=4 |
Теперь доработаем триггер и запретим ввод пустых значений в поле «Email» при обновлении.
Для этого добавим в оператор «if» который проверяет количество обновляемых записей блок «else». Такой подход позволяет исключить дополнительную проверку количества записей так как при проверке поля «Email» наличие в таблице «inserted» единственной записи будет гарантировано.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
else { command.CommandText = "SELECT [Email] FROM inserted"; if (command.ExecuteScalar().ToString() == "") { command.CommandText = "RAISERROR('Email не может быть пустым. Откат транзакции.',16,1); ROLLBACK TRAN;"; try { SqlContext.Pipe.ExecuteAndSend(command); } catch { } }; } |
Протестируем обновлённую версию триггера.
Следующий запрос не выполнился.
|
1 |
UPDATE [dbo].[Data] SET [Email]='' WHERE id=4 |
Microsoft SQL Server выдал сообщение об ошибке.
Зато запрос, в котором поле «Email» не пустое выполнился без проблем.
[code lang=»sql»]UPDATE [dbo].[Data] SET [Email]=’testTrigger@example.com’ WHERE id=4[/code]
Не трудно заметить, что в обоих случаях для отправки сообщения об ошибке используется один и тот же алгоритм, который работает с разными данными.
Попробуем упростить триггер и вынести этот алгоритм в отдельный закрытый метод. Для обеспечения возможности работать с различными данными заменим жёстко закодированный запрос на выдачу исключения и откат транзакции параметрическим.
В результате получаем такой «унифицированный» метод:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
private static void RaiseError(SqlCommand command, string errorMessage) { command.CommandText = "RAISERROR(@Msg,16,1); ROLLBACK TRAN;"; command.Parameters.Add("@Msg", SqlDbType.NVarChar); command.Parameters["@Msg"].Value = errorMessage; try { SqlContext.Pipe.ExecuteAndSend(command); } catch { } } |
При этом сам триггер стал гораздо компактнее и проще.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[Microsoft.SqlServer.Server.SqlTrigger (Name="RollbackTrigger", Target="Data", Event="FOR UPDATE")] public static void RollbackTrigger () { using (SqlConnection conn =new SqlConnection("context connection = true")) { SqlCommand command = conn.CreateCommand(); command.CommandText = "SELECT count(*) FROM inserted"; conn.Open(); if ((int)command.ExecuteScalar()>1) { RaiseError(command,"Вы пытаетесь обновить слишком много строк. Откат транзакции."); } else { command.CommandText = "SELECT [Email] FROM inserted"; if (command.ExecuteScalar().ToString() == "") { RaiseError(command, "Email не может быть пустым. Откат транзакции."); }; } } } |
Старые версии Microsoft SQL Server
Пример, который мы рассмотрели выше, приведён для Microsoft SQL Server.
Если Вы используете более раннюю версию, например 2008, то вместо атрибута «Microsoft.SqlServer.Server.SqlTrigger» необходимо использовать проверку типа операции с таблицей при помощи контекста триггера непосредственно в его коде. Проверку следует реализовать до выполнения любых других операций.
Для примера рассмотренного выше такая проверка выполняется следующим образом:
|
1 2 3 4 |
if (SqlContext.TriggerContext.TriggerAction!=TriggerAction.Update) { return; } |
Триггеры Transact SQL или SQLCLR?
Как и любой другой объект SQLCLR триггеры, созданные с помощью этой технологии, более громоздки, трудоёмки в разработке и сложны в отношении интеграции с целевой базой данных и дальнейшего сопровождения.
Их использование оправдано, когда ту или иную задачу невозможно решить с помощью обычных триггеров Transact SQL либо из соображений быстродействия.
Во всех остальных случаях следует отдавать предпочтение Transact-SQL.
Добавить комментарий