Como: Criar e executar um disparador de SQL Server usando a integração do Common Language Runtime
Criar um disparador SQL, adicionando um disparador item a um projeto de banco de dados SQL Server Common Language Runtime (CLR SQL). Após a implantação bem-sucedida, disparadores são criados em código gerenciado são chamados e executados como qualquer outro Transact-SQL disparador. Disparadores são escritos em uma linguagem gerenciada podem usar a classe SqlTriggerContext para obter acesso às mesmas informações que está disponíveis para Transact-SQL disparadores.
.gif "Observação") Observação |
|---|
Seu computador pode mostrar nomes ou locais diferentes para alguns dos elementos da interface do usuário do Visual Studio nas instruções a seguir. A edição do Visual Studio que você possui e as configurações que você usa determinam esses elementos. Para obter mais informações, consulte Configurações do Visual Studio. |
Criando o disparador SQL Server
Para criar um disparador SQL Server
Abra um existente Projeto de banco de dados do CLR SQL, ou criar um novo. Para obter mais informações, consulte Como: Criar um projeto para os objetos de banco de dados que usam a integração de tempo de execução de linguagem comum SQL Server.
No menu Project, selecione Add New Item.
No Add New Item caixa de diálogo, selecione disparador.
Digite um Name para o novo disparador.
Adicione código para executar quando o disparador for executado. Consulte o primeiro exemplo que segue este procedimento.
No Solution Explorer, abra a pasta TestScripts e clique duas vezes no arquivo Test.sql.
ObservaçãoVocê pode especificar outros scripts como o seu script de depuração padrão. Para obter mais informações, consulte Como: Edite o Script de Test. SQL para executar objetos usar a integração de tempo de execução de linguagem comum de SQL Server.
Adicione código ao aquivo Test.sql para executar o disparador. Consulte o segundo exemplo que segue este procedimento.
Pressione F5 para criar, implantar, e depurar o disparador. Para obter informações sobre como implantar sem depuração, consulte Como: Implantar itens de projeto de banco de dados do CLR de SQL um SQL Server.
.gif "Observação importante")
ImportanteSQL Server 2005e SQL Server 2008 oferece suporte somente a os projetos deSQL Server que foram criados com o 2.0, 3.0 ou versão 3.5 do.NET Framework. Se você tentar implantar um SQL Server projeto SQL Server 2005 ou SQL Server 2008, um erro será exibida: 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(onde AssemblyName é o nome do assembly que você estiver implantando). Para obter mais informações, consulte Como: Criar um projeto para os objetos de banco de dados que usam a integração de tempo de execução de linguagem comum SQL Server.
Exibir os resultados mostrados na Janela de saída e selecione Show output from: Saída de banco de dados.
Exemplo
Este exemplo demonstra o cenário onde os usuários escolhem qualquer nome de usuário que desejarem, mas você deseja saber quais usuários inseriram um endereço de email como nome de usuário. Este disparador detecta essas informações e as registra em uma tabela de auditoria.
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]+$");
}
}
Adicione código para executar e testar o disparador no arquivo Test.sql na pasta TestScripts no seu projeto. Por exemplo, se você implantou um disparador, você pode testá-lo ao executar um script que insere uma nova linha na tabela na qual o disparador está definido, causando o disparo do disparador. O seguinte código depuração supõe que existem duas tabelas com as seguintes definições:
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
Consulte também
Tarefas
Como: Criar e executar um agregado de SQL Server usando a integração do Common Language Runtime
Demonstra Passo a passo: Criar um procedimento armazenado no código gerenciado
Como: Depurar um CLR SQL procedimento armazenado
Referência
Atributos para objetos de banco de dados e de projetos de banco de dados do CLR de SQL
Conceitos
Introduction to SQL Server CLR Integration (ADO.NET)
Vantagens de usar código gerenciado para criar objetos de bancos de dados
Criar objetos de SQL Server no código gerenciado