Практическое руководство. Создание и запуск триггера CLR SQL Server

Статья
11/05/2008

Обновлен: Ноябрь 2007

Создайте триггер SQL путем добавления элемента Триггер в проект SQL сервера. После успешного развертывания триггеры, созданные в управляемом коде, вызываются и выполняются как любой другой триггер T-SQL. Триггеры, написанные на управляемом языке, могут использовать класс SqlTriggerContext, чтобы получить доступ к тем же сведениям, что доступны для триггеров T-SQL.

Примечание.
Средства интеграции Microsoft SQL Server среды CLR по умолчанию отключены и должны быть включены для использования элементов проекта SQL Server. Для включения интеграции со средой CLR используйте параметр clr enabled хранимой процедуры sp_configure. Дополнительные сведения см. в разделе Включение интеграции со средой CLR.

Средства интеграции Microsoft SQL Server среды CLR по умолчанию отключены и должны быть включены для использования элементов проекта SQL Server. Для включения интеграции со средой CLR используйте параметр clr enabled хранимой процедуры sp_configure. Дополнительные сведения см. в разделе Включение интеграции со средой CLR.

Примечание.
На вашем компьютере названия некоторых элементов интерфейса пользователя Visual Studio или их расположение могут отличаться от указанных в нижеследующих инструкциях. Это зависит от имеющегося выпуска Visual Studio и используемых параметров. Дополнительные сведения см. в разделе Параметры Visual Studio.

На вашем компьютере названия некоторых элементов интерфейса пользователя Visual Studio или их расположение могут отличаться от указанных в нижеследующих инструкциях. Это зависит от имеющегося выпуска Visual Studio и используемых параметров. Дополнительные сведения см. в разделе Параметры Visual Studio.

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

Создание триггера SQL Server

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

Примечание.

Примеры C++ должны быть скомпилированы с параметром компилятора /clr:safe.
Для Visual Basic и Visual C# в окне Обозреватель решений откройте папку TestScripts и дважды щелкните файл Test.sql.

Для Visual C++ в окне Обозреватель решений дважды щелкните файл Debug.sql.
Добавьте код в файл Test.sql (debug.sql в Visual C++) для выполнение триггера. Ознакомьтесь со вторым примером, следующим за этой процедурой.
Нажмите кнопку F5 для сборки, развертывания и отладки триггера. Сведения о развертывании без отладки содержатся в разделе Практическое руководство. Развертывание элементов проекта SQL Server на сервере SQL Server.
Просмотрите результаты, которые отображаются в окне Окно выходных данных, и выберите Показать выходные данные от: Выходные данные БД.

Примечание.
Примеры C++ должны быть скомпилированы с параметром компилятора /clr:safe.

Пример

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

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]+$");
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlClient;
using namespace System::Data::SqlTypes;
using namespace System::Text::RegularExpressions;
using namespace Microsoft::SqlServer::Server;

// In order to debug your Trigger, add the following to your debug.sql file:
//
// -- 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
//

public ref class AddNewTrigger
{
public:
    [SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]
    static void UserNameAudit()
    {
        SqlTriggerContext ^triggContext = SqlContext::TriggerContext;
        SqlParameter ^userName = gcnew SqlParameter("@username", System::Data::SqlDbType::NVarChar);

        if (triggContext->TriggerAction == TriggerAction::Insert)
        {
            SqlConnection ^conn = gcnew SqlConnection("context connection=true");
            conn->Open();
            SqlCommand ^sqlComm = gcnew 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);
            }

            conn->Close();
        }
    }

    static bool IsEMailAddress(String ^s)
    {
        return Regex::IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
    }
};

Добавьте код для выполнения и проверьте триггер с помощью файла Test.sql (debug.sql в Visual C++) в папке 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

См. также

Практическое руководство. Создание и запуск триггера CLR SQL Server

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

Создание триггера SQL Server

Пример

См. также

Задачи

Основные понятия

Ссылки

Другие ресурсы

Дополнительные ресурсы