方法: 共通言語ランタイム統合機能を使用して SQL Server のトリガーを作成および実行する

[アーティクル]
08/09/2011

SQL Server 共通言語ランタイム (SQL CLR) データベースプロジェクトに [トリガー] を追加して、SQL トリガーを作成します。配置が成功すると、マネージコード内に作成されたトリガーが呼び出され、他の Transact-SQL トリガーと同様に実行されます。マネージ言語で作成されたトリガーでは、SqlTriggerContext クラスを使用して、Transact-SQL トリガーで使用可能な情報にアクセスできます。

注意

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

SQL Server トリガーの作成

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

既存の SQL CLR データベースプロジェクトを開くか、または新しいプロジェクトを作成します。詳細については、「方法: SQL Server 共通言語ランタイム統合機能を使用するデータオブジェクトのプロジェクトを作成する」を参照してください。
[プロジェクト] メニューの [新しい項目の追加] をクリックします。
[新しい項目の追加] ダイアログボックスで、[トリガー] をクリックします。
新しいトリガーに [ファイル名] を指定します。
トリガーの実行時に実行するコードを追加します。下記の最初の例を参照してください。
ソリューションエクスプローラーで、[TestScripts] フォルダーを開き、Test.sql ファイルをダブルクリックします。

注意

既定のデバッグスクリプトに他のスクリプトを指定することもできます。詳細については、「方法: Test.sql スクリプトを編集して SQL Server 共通言語ランタイム統合機能を使用するオブジェクトを実行する」を参照してください。
トリガーを実行するコードを Test.sql ファイルに追加します。この手順に続く 2 番目の例を参照してください。
F5 キーを押して、トリガーをビルド、配置、およびデバッグします。デバッグせずに配置する方法については、「方法: SQL CLR データベースプロジェクト項目を SQL Server に配置する」を参照してください。

重要

2.0、3.0、または 3.5 バージョンの .NET Framework でビルドした SQL Server プロジェクトをサポートするのは、SQL Server 2005 および SQL Server 2008 のみです。展開すると、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(ここでAssemblyNameは、展開するアセンブリの名前です)。詳細については、「方法: SQL Server 共通言語ランタイム統合機能を使用するデータオブジェクトのプロジェクトを作成する」を参照してください。
表示されます、結果を表示、[出力] ウィンドウ選択出力元の表示：データベース出力。

使用例

この例は、ユーザーがユーザー名を任意に選択できるようにする一方で、電子メールアドレスをユーザー名として入力したユーザーを特定する必要がある場合を示しています。このトリガーは、この情報を検出し、監査テーブルに記録します。

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

トリガーを実行してテストするためのコードを、プロジェクト内の TestScripts フォルダーにある Test.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

参照

方法: 共通言語ランタイム統合機能を使用して SQL Server のトリガーを作成および実行する

SQL Server トリガーの作成

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

使用例

参照

処理手順

参照

概念

その他の技術情報

その他のリソース