方法 : CLR の SQL Server トリガを作成および実行する

[アーティクル]
09/17/2008

更新 : 2007 年 11 月

SQL トリガを作成するには、SQL サーバープロジェクトに [トリガ] 項目を追加します。配置が成功すると、マネージコード内に作成されたトリガが呼び出され、他の T-SQL トリガと同様に実行されます。マネージ言語で作成されたトリガでは、SqlTriggerContext クラスを使用して、T-SQL トリガで使用可能な情報にアクセスできます。

メモ :
既定では、Microsoft SQL Server では共通言語ランタイム (CLR: Common Language Runtime) 統合機能が無効になっており、SQL Server プロジェクト項目を使用するにはこの機能を有効にする必要があります。CLR 統合を有効にするには、sp_configure ストアドプロシージャの clr enabled オプションを使用します。詳細については、「CLR 統合の有効化」を参照してください。

既定では、Microsoft SQL Server では共通言語ランタイム (CLR: Common Language Runtime) 統合機能が無効になっており、SQL Server プロジェクト項目を使用するにはこの機能を有効にする必要があります。CLR 統合を有効にするには、sp_configure ストアドプロシージャの clr enabled オプションを使用します。詳細については、「CLR 統合の有効化」を参照してください。

メモ :
お使いのマシンで、Visual Studio ユーザーインターフェイスの一部の要素の名前や場所が、次の手順とは異なる場合があります。これらの要素は、使用している Visual Studio のエディションや独自の設定によって決まります。詳細については、「Visual Studio の設定」を参照してください。

SQL Server トリガの作成

SQL Server トリガを作成するには

既存の SQL Server プロジェクトを開くか、または新しい SQL Server プロジェクトを作成します。詳細については、「方法 : SQL Server プロジェクトを作成する」を参照してください。
[プロジェクト] メニューの [新しい項目の追加] をクリックします。
[新しい項目の追加] ダイアログボックスの [トリガ] を選択します。
新しいトリガに [ファイル名] を指定します。
トリガの実行時に実行するコードを追加します。下記の最初の例を参照してください。

メモ :

C++ の例は、/clr:safe コンパイラオプションを使ってコンパイルする必要があります。
Visual Basic および Visual C# の場合は、ソリューションエクスプローラで TestScripts フォルダを開き、Test.sql ファイルをダブルクリックします。

Visual C++ の場合は、ソリューションエクスプローラで debug.sql ファイルをダブルクリックします。
トリガを実行するコードを Test.sql (Visual C++ の場合は debug.sql) ファイルに追加します。この手順に続く 2 番目の例を参照してください。
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]+$");
    }
};

トリガを実行してテストするためのコードを、プロジェクト内の TestScripts フォルダにある Test.sql (Visual C++ の場合は debug.sql) ファイルに追加します。たとえば、トリガを配置した場合にそのトリガをテストするには、トリガを設定したテーブルに新しい行を挿入してそのトリガを起動するスクリプトを実行します。次のデバッグコードでは、次のように定義された 2 つのテーブルが存在することを前提としています。

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 トリガを作成するには

使用例

参照

処理手順

概念

参照

その他の技術情報

その他のリソース