방법: CLR SQL Server 트리거 만들기 및 실행
업데이트: 2007년 11월
트리거 항목을 SQL Server 프로젝트에 추가하여 SQL 트리거를 만들 수 있습니다. 배포된 후에는 관리 코드에 만든 트리거를 다른 T-SQL 트리거와 마찬가지로 호출하고 실행할 수 있습니다. 관리되는 언어로 작성된 트리거에서는 SqlTriggerContext 클래스를 사용하여 T-SQL 트리거에서 사용할 수 있는 것과 같은 정보에 액세스할 수 있습니다.
.gif "참고") 참고: |
|---|
기본적으로 CLR(공용 언어 런타임) 통합 기능은 Microsoft SQL Server에서 해제되어 있으며 SQL Server 프로젝트 항목을 사용하려면 이 기능을 사용하도록 설정해야 합니다. CLR 통합 기능을 사용하도록 설정하려면 sp_configure 저장 프로시저의 clr enabled 옵션을 사용합니다. 자세한 내용은 CLR 통합 설정을 참조하십시오. |
| 참고: |
|---|
다음 지침처럼 컴퓨터에서 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(Visual C++의 경우 debug.sql) 파일에 트리거를 실행하는 코드를 추가합니다. 이 절차 다음에 나오는 두 번째 예제를 참조하십시오.
F5 키를 눌러 트리거를 빌드, 배포 및 디버깅합니다. 디버깅하지 않고 배포하는 방법에 대한 자세한 내용은 방법: SQL Server에 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]+$");
}
}
#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) 파일에 트리거를 실행하고 테스트하는 코드를 추가합니다. 예를 들어, 트리거를 배포했으면 트리거가 설정된 테이블에 새 행을 삽입하는 스크립트를 실행하여 트리거를 발생시킴으로써 트리거를 테스트할 수 있습니다. 다음 디버깅 코드에서는 다음과 같이 정의된 두 테이블이 있는 것으로 가정합니다.
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 저장 프로시저 만들기 및 실행
방법: CLR SQL Server 트리거 만들기 및 실행
방법: CLR SQL Server 집계 만들기 및 실행
방법: CLR SQL Server 사용자 정의 함수 만들기 및 실행
방법: CLR SQL Server 사용자 정의 형식 만들기 및 실행
개념
관리 코드를 사용하여 데이터베이스 개체를 만드는 경우의 이점
참조
SQL Server 프로젝트 및 데이터베이스 개체의 특성