방법: 공용 언어 런타임 통합을 사용하여 SQL Server 트리거 만들기 및 실행

아티클
08/10/2011

트리거 항목을 SQL Server 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 파일에 트리거를 실행하는 코드를 추가합니다. 이 절차 다음에 나오는 두 번째 예제를 참조하십시오.
F5 키를 눌러 트리거를 빌드, 배포 및 디버깅합니다. 디버깅하지 않고 배포하는 방법에 대한 자세한 내용은 방법: SQL Server에 SQL CLR 데이터베이스 프로젝트 항목 배포를 참조하십시오.

중요

SQL Server 2005 및 SQL Server 2008에서는 .NET Framework 2.0, 3.0 또는 3.5 버전으로 빌드된 SQL Server 프로젝트만 지원합니다. 배포 하려고 하는 경우는 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 파일에 트리거를 실행하고 테스트하는 코드를 추가합니다. 예를 들어, 트리거를 배포했으면 트리거가 설정된 테이블에 새 행을 삽입하는 스크립트를 실행하여 트리거를 발생시킴으로써 트리거를 테스트할 수 있습니다. 다음 디버깅 코드에서는 다음과 같이 정의된 두 테이블이 있는 것으로 가정합니다.

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 트리거를 만들려면

예제

참고 항목

작업

참조

개념

기타 리소스

추가 리소스