.NET Framework CLR(공용 언어 런타임)과 SQL Server 통합으로 인해 모든 .NET Framework 언어를 사용하여 CLR 트리거를 만들 수 있습니다. 이 섹션에서는 CLR 통합으로 구현된 트리거와 관련된 정보를 다룹니다. 트리거에 대한 자세한 내용은 DDL 트리거를 참조하세요.
트리거란?
트리거는 언어 이벤트가 실행될 때 자동으로 실행되는 특수한 유형의 저장 프로시저입니다. SQL Server에는 두 가지 일반적인 유형의 트리거인 DML(데이터 조작 언어) 및 DDL(데이터 정의 언어) 트리거가 포함됩니다. DML 트리거는 지정된 테이블 또는 뷰에서 데이터를 수정하거나 DELETE 문을 수정할 때 INSERTUPDATE사용할 수 있습니다. DDL은 주로 < a0/>로 시작하는
CLR 트리거의 고유한 기능
Transact-SQL 작성된 트리거는 실행 뷰 또는 테이블에서 함수를 사용하여 UPDATE(column)COLUMNS_UPDATED() 업데이트된 열을 결정하는 기능을 갖습니다.
CLR 언어로 작성된 트리거는 여러 가지 중요한 방법으로 다른 CLR 통합 개체와 다릅니다. CLR 트리거는 다음을 수행할 수 있습니다.
및
DELETED테이블의INSERTED참조 데이터작업의 결과로
UPDATE수정된 열 확인DDL 문 실행의 영향을 받는 데이터베이스 개체에 대한 정보에 액세스합니다.
이러한 기능은 쿼리 언어 또는 SqlTriggerContext 클래스에서 기본적으로 제공됩니다. CLR 통합의 장점과 관리 코드와 Transact-SQL 중에서 선택하는 이점에 대한 자세한 내용은 CLR 통합 개요를 참조하세요.
SqlTriggerContext 클래스 사용
클래스는 SqlTriggerContext 공개적으로 생성할 수 없으며 CLR 트리거의 본문 내에서 속성에 액세스해야 SqlContext.TriggerContext 만 가져올 수 있습니다.
SqlTriggerContext 속성을 호출하여 활성 SqlContext 에서 클래스를 SqlContext.TriggerContext 가져올 수 있습니다.
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
클래스는 SqlTriggerContext 트리거에 대한 컨텍스트 정보를 제공합니다. 이 컨텍스트 정보에는 트리거를 발생시킨 작업 유형, UPDATE 작업에서 수정된 열 및 DDL 트리거의 경우 트리거 작업을 설명하는 XML EventData 구조가 포함됩니다. 자세한 내용은 EVENTDATA(Transact-SQL)를 참조하세요.
트리거 작업 확인
가져온 후에는 트리거를 SqlTriggerContext발생시킨 작업 유형을 확인하는 데 사용할 수 있습니다. 이 정보는 클래스의 TriggerActionSqlTriggerContext 속성을 통해 사용할 수 있습니다.
DML 트리거의 경우 속성은 TriggerAction 다음 값 중 하나일 수 있습니다.
TriggerAction.Update(0x1)
TriggerAction.Insert(0x2)
TriggerAction.Delete(0x3)
DDL 트리거의 경우 가능한 TriggerAction 값 목록이 상당히 길어집니다. 자세한 내용은 .NET Framework SDK의 "TriggerAction 열거형"을 참조하세요.
삽입 및 삭제된 테이블 사용
DML 트리거 문에는 삽입된 테이블과 삭제 된 테이블이라는 두 개의 특수 테이블이 사용됩니다. SQL Server는 이러한 테이블을 자동으로 생성하고 관리합니다. 이러한 임시 테이블을 사용하여 특정 데이터 수정의 효과를 테스트하고 DML 트리거 작업에 대한 조건을 설정할 수 있습니다. 그러나 테이블의 데이터를 직접 변경할 수는 없습니다.
CLR 트리거는 CLR In Process 공급자 를 통해 삽입 및삭제된 테이블에 액세스할 수 있습니다. 이 작업은 SqlContext 개체에서 개체를 가져와 SqlCommand 서 수행합니다. 다음은 그 예입니다.
C# (프로그래밍 언어)
SqlConnection connection = new SqlConnection ("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted";
Visual Basic (비주얼 베이직 언어)
Dim connection As New SqlConnection("context connection=true")
Dim command As SqlCommand
connection.Open()
command = connection.CreateCommand()
command.CommandText = "SELECT * FROM " + "inserted"
업데이트된 열 확인
개체의 속성을 사용하여 ColumnCount UPDATE 작업에 의해 수정된 열 수를 SqlTriggerContext 확인할 수 있습니다. 열 서수를 입력 매개 변수로 사용하는 메서드를 사용하여 IsUpdatedColumn 열이 업데이트되었는지 여부를 확인할 수 있습니다. 값은 True 열이 업데이트되었음을 나타냅니다.
예를 들어 이 항목의 뒷부분에 있는 EmailAudit 트리거의 이 코드 조각에는 업데이트된 모든 열이 나열됩니다.
C# (프로그래밍 언어)
reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send("Updated column "
+ reader.GetName(columnNumber) + "? "
+ triggContext.IsUpdatedColumn(columnNumber).ToString());
}
reader.Close();
Visual Basic (비주얼 베이직 언어)
reader = command.ExecuteReader()
reader.Read()
Dim columnNumber As Integer
For columnNumber=0 To triggContext.ColumnCount-1
pipe.Send("Updated column " & reader.GetName(columnNumber) & _
"? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )
Next
reader.Close()
CLR DDL 트리거에 대한 EventData 액세스
일반 트리거와 같은 DDL 트리거는 이벤트에 대한 응답으로 저장 프로시저를 실행합니다. 그러나 DML 트리거와 달리 테이블 또는 뷰의 UPDATE, INSERT 또는 DELETE 문에 대한 응답으로 실행되지 않습니다. 대신, 주로 CREATE, ALTER 및 DROP으로 시작하는 문인 다양한 DDL 문에 대한 응답으로 실행됩니다. DDL 트리거는 데이터베이스 작업 및 스키마 변경에 대한 감사 및 모니터링과 같은 관리 작업에 사용할 수 있습니다.
DDL 트리거를 발생시키는 이벤트에 대한 정보는 클래스의 EventDataSqlTriggerContext 속성에서 사용할 수 있습니다. 이 속성에는 값이 xml 포함됩니다. xml 스키마에는 다음에 대한 정보가 포함됩니다.
이벤트의 시간
트리거가 실행되는 동안 연결의 SPID(시스템 프로세스 ID)입니다.
트리거를 실행한 이벤트의 유형
그런 다음 이벤트 유형에 따라 스키마에는 이벤트가 발생한 데이터베이스, 이벤트가 발생한 개체 및 이벤트의 Transact-SQL 명령과 같은 추가 정보가 포함됩니다.
다음 예제에서 다음 DDL 트리거는 원시 EventData 속성을 반환합니다.
비고
개체를 SqlPipe 통해 결과 및 메시지를 보내는 것은 설명 목적으로만 여기에 표시되며 CLR 트리거를 프로그래밍할 때 프로덕션 코드에는 일반적으로 권장되지 않습니다. 반환된 추가 데이터는 예기치 않은 것일 수 있으며 애플리케이션 오류가 발생할 수 있습니다.
C# (프로그래밍 언어)
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using System.Text.RegularExpressions;
public class CLRTriggers
{
public static void DropTableTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch(triggContext.TriggerAction)
{
case TriggerAction.DropTable:
SqlContext.Pipe.Send("Table dropped! Here's the EventData:");
SqlContext.Pipe.Send(triggContext.EventData.Value);
break;
default:
SqlContext.Pipe.Send("Something happened! Here's the EventData:");
SqlContext.Pipe.Send(triggContext.EventData.Value);
break;
}
}
}
Visual Basic (비주얼 베이직 언어)
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class CLRTriggers
Public Shared Sub DropTableTrigger()
Dim triggContext As SqlTriggerContext
triggContext = SqlContext.TriggerContext
Select Case triggContext.TriggerAction
Case TriggerAction.DropTable
SqlContext.Pipe.Send("Table dropped! Here's the EventData:")
SqlContext.Pipe.Send(triggContext.EventData.Value)
Case Else
SqlContext.Pipe.Send("Something else happened! Here's the EventData:")
SqlContext.Pipe.Send(triggContext.EventData.Value)
End Select
End Sub
End Class
다음 샘플 출력은 EventData 이벤트에 의해 DDL 트리거가 발생한 후의 속성 값입니다 CREATE TABLE .
<EVENT_INSTANCE><PostTime>2004-04-16T21:17:16.160</PostTime><SPID>58</SPID><EventType>CREATE_TABLE</EventType><ServerName>MACHINENAME</ServerName><LoginName>MYDOMAIN\myname</LoginName><UserName>MYDOMAIN\myname</UserName><DatabaseName>AdventureWorks</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>UserName</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create table dbo.UserName ( UserName varchar(50), RealName varchar(50) ) </CommandText></TSQLCommand></EVENT_INSTANCE>
클래스를 통해 SqlTriggerContext 액세스할 수 있는 정보 외에도 쿼리는 프로세스에서 실행된 명령의 텍스트 내에서 계속 참조 COLUMNS_UPDATED 및 삽입/삭제할 수 있습니다.
샘플 CLR 트리거
이 예제에서는 사용자가 원하는 ID를 선택하도록 허용하지만 전자 메일 주소를 ID로 입력한 사용자를 알고자 하는 시나리오를 고려합니다. 다음 트리거는 해당 정보를 검색하고 감사 테이블에 기록합니다.
비고
개체를 SqlPipe 통해 결과 및 메시지를 보내는 것은 설명 목적으로만 여기에 표시되며 일반적으로 프로덕션 코드에는 권장되지 않습니다. 반환된 추가 데이터가 예기치 않은 것일 수 있으며 애플리케이션 오류가 발생할 수 있습니다.
using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using System.Text.RegularExpressions;
public class CLRTriggers
{
[SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]
public static void EmailAudit()
{
string userName;
string realName;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
reader = command.ExecuteReader();
reader.Read();
userName = (string)reader[0];
realName = (string)reader[1];
reader.Close();
if (IsValidEMailAddress(userName))
{
command = new SqlCommand(
@"INSERT [dbo].[UserNameAudit] VALUES ('"
+ userName + @"', '" + realName + @"');",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send("You inserted: " + userName);
}
}
break;
case TriggerAction.Update:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
reader = command.ExecuteReader();
reader.Read();
userName = (string)reader[0];
realName = (string)reader[1];
pipe.Send(@"You updated: '" + userName + @"' - '"
+ realName + @"'");
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send("Updated column "
+ reader.GetName(columnNumber) + "? "
+ triggContext.IsUpdatedColumn(columnNumber).ToString());
}
reader.Close();
}
break;
case TriggerAction.Delete:
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM DELETED;",
connection);
reader = command.ExecuteReader();
if (reader.HasRows)
{
pipe.Send(@"You deleted the following rows:");
while (reader.Read())
{
pipe.Send(@"'" + reader.GetString(0)
+ @"', '" + reader.GetString(1) + @"'");
}
reader.Close();
//alternately, to just send a tabular resultset back:
//pipe.ExecuteAndSend(command);
}
else
{
pipe.Send("No rows affected.");
}
}
break;
}
}
public static bool IsValidEMailAddress(string email)
{
return Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$");
}
}
Visual Basic (비주얼 베이직 언어)
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
'The Partial modifier is only required on one class definition per project.
Partial Public Class CLRTriggers
<SqlTrigger(Name:="EmailAudit", Target:="[dbo].[Users]", Event:="FOR INSERT, UPDATE, DELETE")> _
Public Shared Sub EmailAudit()
Dim userName As String
Dim realName As String
Dim command As SqlCommand
Dim triggContext As SqlTriggerContext
Dim pipe As SqlPipe
Dim reader As SqlDataReader
triggContext = SqlContext.TriggerContext
pipe = SqlContext.Pipe
Select Case triggContext.TriggerAction
Case TriggerAction.Insert
Using connection As New SqlConnection("context connection=true")
connection.Open()
command = new SqlCommand("SELECT * FROM INSERTED;", connection)
reader = command.ExecuteReader()
reader.Read()
userName = CType(reader(0), String)
realName = CType(reader(1), String)
reader.Close()
If IsValidEmailAddress(userName) Then
command = New SqlCommand("INSERT [dbo].[UserNameAudit] VALUES ('" & _
userName & "', '" & realName & "');", connection)
pipe.Send(command.CommandText)
command.ExecuteNonQuery()
pipe.Send("You inserted: " & userName)
End If
End Using
Case TriggerAction.Update
Using connection As New SqlConnection("context connection=true")
connection.Open()
command = new SqlCommand("SELECT * FROM INSERTED;", connection)
reader = command.ExecuteReader()
reader.Read()
userName = CType(reader(0), String)
realName = CType(reader(1), String)
pipe.Send("You updated: " & userName & " - " & realName)
Dim columnNumber As Integer
For columnNumber=0 To triggContext.ColumnCount-1
pipe.Send("Updated column " & reader.GetName(columnNumber) & _
"? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )
Next
reader.Close()
End Using
Case TriggerAction.Delete
Using connection As New SqlConnection("context connection=true")
connection.Open()
command = new SqlCommand("SELECT * FROM DELETED;", connection)
reader = command.ExecuteReader()
If reader.HasRows Then
pipe.Send("You deleted the following rows:")
While reader.Read()
pipe.Send( reader.GetString(0) & ", " & reader.GetString(1) )
End While
reader.Close()
' Alternately, just send a tabular resultset back:
' pipe.ExecuteAndSend(command)
Else
pipe.Send("No rows affected.")
End If
End Using
End Select
End Sub
Public Shared Function IsValidEMailAddress(emailAddress As String) As Boolean
return Regex.IsMatch(emailAddress, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function
End Class
다음 정의를 사용하여 두 개의 테이블이 있다고 가정합니다.
CREATE TABLE Users
(
UserName nvarchar(200) NOT NULL,
RealName nvarchar(200) NOT NULL
);
GO CREATE TABLE UserNameAudit
(
UserName nvarchar(200) NOT NULL,
RealName nvarchar(200) NOT NULL
)
SQL Server에서 트리거를 만드는 Transact-SQL 문은 다음과 이며 어셈블리 SQLCLRTest 가 현재 SQL Server 데이터베이스에 이미 등록되어 있다고 가정합니다.
CREATE TRIGGER EmailAudit
ON Users
FOR INSERT, UPDATE, DELETE
AS
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit
잘못된 트랜잭션 유효성 검사 및 취소
트리거를 사용하여 잘못된 INSERT, UPDATE 또는 DELETE 트랜잭션의 유효성을 검사하고 취소하거나 데이터베이스 스키마의 변경을 방지하는 것이 일반적입니다. 이 작업은 유효성 검사 논리를 트리거에 통합한 다음 작업이 유효성 검사 조건을 충족하지 않는 경우 현재 트랜잭션을 롤백하여 수행할 수 있습니다.
트리거 Transaction.Rollback 내에서 호출될 때 명령 텍스트가 "TRANSACTION ROLLBACK"인 메서드 또는 SqlCommand는 모호한 오류 메시지와 함께 예외를 throw하며 try/catch 블록에 래핑되어야 합니다. 표시되는 오류 메시지는 다음과 유사합니다.
Msg 6549, Level 16, State 1, Procedure trig_InsertValidator, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_InsertValidator':
System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting... User transaction, if any, will be rolled back.
이 예외가 예상되며 코드 실행을 계속하려면 try/catch 블록이 필요합니다. 트리거 코드 실행이 완료되면 다른 예외가 발생합니다.
Msg 3991, Level 16, State 1, Procedure trig_InsertValidator, Line 1
The context transaction which was active before entering user defined routine, trigger or aggregate "trig_InsertValidator" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
The statement has been terminated.
이 예외도 예상되며, 실행을 계속할 수 있도록 트리거를 실행하는 작업을 수행하는 Transact-SQL 문 주변의 try/catch 블록이 필요합니다. 두 가지 예외가 발생했음에도 불구하고 트랜잭션이 롤백되고 변경 내용이 테이블에 커밋되지 않습니다. CLR 트리거와 Transact-SQL 트리거 간의 주요 차이점은 트랜잭션이 롤백된 후에도 Transact-SQL 트리거가 계속해서 더 많은 작업을 수행할 수 있다는 것입니다.
예시
다음 트리거는 테이블에서 INSERT 문의 간단한 유효성 검사를 수행합니다. 삽입된 정수 값이 1과 같으면 트랜잭션이 롤백되고 값이 테이블에 삽입되지 않습니다. 다른 모든 정수 값이 테이블에 삽입됩니다. 메서드 주변의 try/catch 블록을 확인합니다 Transaction.Rollback . Transact-SQL 스크립트는 테스트 테이블, 어셈블리 및 관리되는 저장 프로시저를 만듭니다. 두 INSERT 문은 try/catch 블록으로 래핑되므로 트리거 실행이 완료될 때 throw되는 예외가 catch됩니다.
C# (프로그래밍 언어)
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
// [Microsoft.SqlServer.Server.SqlTrigger (Name="trig_InsertValidator", Target="Table1", Event="FOR INSERT")]
public static void trig_InsertValidator()
{
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
SqlCommand command;
SqlDataReader reader;
int value;
// Open the connection.
connection.Open();
// Get the inserted value.
command = new SqlCommand(@"SELECT * FROM INSERTED", connection);
reader = command.ExecuteReader();
reader.Read();
value = (int)reader[0];
reader.Close();
// Rollback the transaction if a value of 1 was inserted.
if (1 == value)
{
try
{
// Get the current transaction and roll it back.
Transaction trans = Transaction.Current;
trans.Rollback();
}
catch (SqlException ex)
{
// Catch the expected exception.
}
}
else
{
// Perform other actions here.
}
// Close the connection.
connection.Close();
}
}
}
Visual Basic (비주얼 베이직 언어)
Imports System
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Transactions
Partial Public Class Triggers
' Enter existing table or view for the target and uncomment the attribute line
' <Microsoft.SqlServer.Server.SqlTrigger(Name:="trig_InsertValidator", Target:="Table1", Event:="FOR INSERT")> _
Public Shared Sub trig_InsertValidator ()
Using connection As New SqlConnection("context connection=true")
Dim command As SqlCommand
Dim reader As SqlDataReader
Dim value As Integer
' Open the connection.
connection.Open()
' Get the inserted value.
command = New SqlCommand("SELECT * FROM INSERTED", connection)
reader = command.ExecuteReader()
reader.Read()
value = CType(reader(0), Integer)
reader.Close()
' Rollback the transaction if a value of 1 was inserted.
If value = 1 Then
Try
' Get the current transaction and roll it back.
Dim trans As Transaction
trans = Transaction.Current
trans.Rollback()
Catch ex As SqlException
' Catch the exception.
End Try
Else
' Perform other actions here.
End If
' Close the connection.
connection.Close()
End Using
End Sub
End Class
Transact-SQL
-- Create the test table, assembly, and trigger.
CREATE TABLE Table1(c1 int);
go
CREATE ASSEMBLY ValidationTriggers from 'E:\programming\ ValidationTriggers.dll';
go
CREATE TRIGGER trig_InsertValidator
ON Table1
FOR INSERT
AS EXTERNAL NAME ValidationTriggers.Triggers.trig_InsertValidator;
go
-- Use a Try/Catch block to catch the expected exception
BEGIN TRY
INSERT INTO Table1 VALUES(42)
INSERT INTO Table1 VALUES(1)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage
END CATCH;
-- Clean up.
DROP TRIGGER trig_InsertValidator;
DROP ASSEMBLY ValidationTriggers;
DROP TABLE Table1;
또한 참조하십시오
CREATE TRIGGER(Transact-SQL)
DML 트리거
DDL 트리거
TRY...CATCH(Transact-SQL)
CLR(공용 언어 런타임) 통합 사용하여 데이터베이스 개체 빌드
EVENTDATA(Transact-SQL)