Практическое руководство. Создание и запуск триггера SQL Server при помощи интеграции со средой CLR
Создайте триггер SQL, добавив элемент Триггер в проект базы данных SQL Server со средой CLR. После успешного развертывания триггеры, созданные в управляемом коде, вызываются и выполняются как любой другой триггер Transact-SQL. Триггеры, написанные на управляемом языке, могут использовать класс SqlTriggerContext, чтобы получить доступ к тем же сведениям, которые доступны для триггеров Transact-SQL.
Примечание
На вашем компьютере названия некоторых элементов интерфейса пользователя Visual Studio или их расположение могут отличаться от указанных в нижеследующих инструкциях. Это зависит от имеющегося выпуска Visual Studio и используемых параметров. Дополнительные сведения см. в разделе Параметры Visual Studio.
Создание триггеров SQL Server
Создание триггера SQL Server
Откройте существующий Проект базы данных SQL CLR или создайте новый. Дополнительные сведения см. в разделе Практическое руководство. Создание проекта для объектов базы данных, использующих интеграцию SQL Server со средой CLR.
В меню Проект выберите команду Добавить новый элемент.
В диалоговом окне Добавление нового элемента выберите Триггер.
Введите Имя для нового триггера.
Добавьте код для выполнения при срабатывании триггера. Ознакомьтесь с первым примером, следующим за этой процедурой.
В окне Обозреватель решений откройте папку TestScripts и дважды щелкните файл Test.sql.
Примечание
В качестве скрипта отладки по умолчанию можно указать другие скрипты.Дополнительные сведения см. в разделе Практическое руководство. Изменение скрипта Test.sql для запуска объектов, использующих интеграцию SQL Server со средой CLR.
Добавьте код в файл Test.sql для выполнения триггера. Ознакомьтесь со вторым примером, следующим за этой процедурой.
Нажмите кнопку F5 для сборки, развертывания и отладки триггера. Дополнительные сведения о развертывании без отладки см. в разделе Практическое руководство. Развертывание элементов проекта базы данных SQL в среде CLR на сервере SQL Server.
Важно!
SQL Server 2005 и SQL Server 2008 поддерживают только проекты SQL Server, созданные с использованием версий 2.0, 3.0 и 3.5 платформы .NET Framework.Если вы попробуете развернуть SQL Server проекта SQL Server 2005 или SQL Server 2008, появляется сообщение об ошибке: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database(где имя_сборки — это имя сборки, развертывания).Дополнительные сведения см. в разделе Практическое руководство. Создание проекта для объектов базы данных, использующих интеграцию SQL Server со средой CLR.
Просмотрите результаты, которые показаны в Окно выходных данных и выберите Показать вывод из: База данных вывода.
Пример
В этом примере показан сценарии, в котором пользователи могут выбирать любое имя, но вы хотите знать, кто из них вводил адрес электронной почты в качестве имени. Триггер обнаруживает эту информацию и записывает ее в таблицу аудита.
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server
Partial Public Class Triggers
<SqlTrigger(Name:="UserNameAudit", Target:="Users", Event:="FOR INSERT")>
Public Shared Sub UserNameAudit()
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
Dim userName As New SqlParameter("@username", SqlDbType.NVarChar)
If triggContext.TriggerAction = TriggerAction.Insert Then
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim sqlComm As New SqlCommand
Dim sqlP As SqlPipe = SqlContext.Pipe()
sqlComm.Connection = conn
sqlComm.CommandText = "SELECT UserName from INSERTED"
userName.Value = sqlComm.ExecuteScalar.ToString()
If IsEMailAddress(userName.ToString) Then
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(username)"
sqlP.Send(sqlComm.CommandText)
sqlP.ExecuteAndSend(sqlComm)
End If
End Using
End If
End Sub
Public Shared Function IsEMailAddress(ByVal s As String) As Boolean
Return Regex.IsMatch(s, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function
End Class
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
[SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT UserName from INSERTED";
userName.Value = sqlComm.ExecuteScalar().ToString();
if (IsEMailAddress(userName.ToString()))
{
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
}
public static bool IsEMailAddress(string s)
{
return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
}
}
Добавьте код для выполнения и проверьте триггер с помощью файла Test.sql в папке TestScripts в проекте. Например, если вы развернули триггер, можно проверить его, выполнив скрипт, вставляющий новую строку в таблицу, на которой установлен этот триггер. Следующий код отладки предполагает, что существуют две таблицы со следующими определениями:
CREATE TABLE Users
(
UserName NVARCHAR(200) NOT NULL,
Pass NVARCHAR(200) NOT NULL
)
CREATE TABLE UsersAudit
(
UserName NVARCHAR(200) NOT NULL
)
-- Insert one user name that is not an e-mail address and one that is
INSERT INTO Users(UserName, Pass) VALUES(N'someone', N'cnffjbeq')
INSERT INTO Users(UserName, Pass) VALUES(N'someone@example.com', N'cnffjbeq')
-- check the Users and UsersAudit tables to see the results of the trigger
select * from Users
select * from UsersAudit
См. также
Задачи
Пошаговое руководство. Создание хранимой процедуры в управляемом коде
Практическое руководство. Отладка хранимой процедуры SQL CLR
Ссылки
Атрибуты проектов баз данных SQL в среде CLR и объектов баз данных
Основные понятия
Введение в интеграцию SQL Server со средой CLR (ADO.NET)
Преимущества использования управляемого кода для создания объектов баз данных
Создание объектов SQL Server в управляемом коде