Procedura: creare ed eseguire un trigger SQL Server tramite l'integrazione di Common Language Runtime
Per creare un trigger SQL, è necessario aggiungere un elemento Trigger a un progetto di database Common Language Runtime di SQL Server (CLR SQL). Al termine della distribuzione, i trigger creati in codice gestito verranno chiamati ed eseguiti come qualsiasi altro trigger Transact-SQL. Con i trigger scritti in un linguaggio gestito è possibile utilizzare la classe SqlTriggerContext per ottenere l'accesso alle stesse informazioni disponibili per i trigger Transact-SQL.
Nota
Nel computer in uso è possibile che vengano visualizzati nomi o percorsi diversi per alcuni elementi dell'interfaccia utente di Visual Studio nelle istruzioni seguenti. La versione di Visual Studio in uso e le impostazioni configurate determinano questi elementi. Per ulteriori informazioni vedere Impostazioni di Visual Studio.
Creazione del trigger SQL Server
Per creare un trigger SQL Server
Aprire un Progetto di database CLR SQL esistente o crearne uno nuovo. Per ulteriori informazioni, vedere Procedura: creare un progetto per gli oggetti di database che utilizzano l'integrazione di Common Language Runtime di SQL Server.
Scegliere Aggiungi nuovo elemento dal menu Progetto.
Nella finestra di dialogo Aggiungi nuovo elemento selezionare Trigger.
Nella casella Nome digitare un nome per il nuovo trigger.
Aggiungere codice da eseguire quando viene eseguito il trigger. Vedere il primo esempio che segue questa procedura.
In Esplora soluzioni aprire la cartella Script di test e fare doppio clic sul file Test.sql.
Nota
È possibile specificare altri script come script di debug predefinito. Per ulteriori informazioni, vedere Procedura: modificare lo script Test.sql per eseguire oggetti che utilizzano l'integrazione di Common Language Runtime di SQL Server.
Aggiungere codice al file Test.sql per eseguire il trigger. Vedere il secondo esempio che segue questa procedura.
Premere F5 per compilare, distribuire ed eseguire il debug del trigger. Per informazioni sulla distribuzione senza debug, vedere Procedura: distribuire elementi di progetti di database CLR SQL Server su SQL Server.
.gif "Nota importante")
ImportanteSQL Server 2005 e SQL Server 2008 supportano solo i progetti SQL Server compilati con la versione 2.0, 3.0 o 3.5 di .NET Framework. Se si tenta di implementare un progetto SQL Server in SQL Server 2005 o SQL Server 2008, viene visualizzato un errore: 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 (dove AssemblyName è il nome dell'assembly che si sta implementando). Per ulteriori informazioni, vedere Procedura: creare un progetto per gli oggetti di database che utilizzano l'integrazione di Common Language Runtime di SQL Server.
Visualizzare i risultati presenti nella Finestra di output e selezionare Mostra output da: Output del database.
Esempio
In questo esempio viene illustrato lo scenario in cui gli utenti scelgono il nome utente che preferiscono, ma si desidera sapere quali utenti hanno immesso un indirizzo di posta elettronica come nome utente. Il trigger rileva tali informazioni e le registra in una tabella di controllo.
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]+$");
}
}
Aggiungere codice per eseguire e verificare il trigger al file Test.sql contenuto nella cartella Script di test del progetto. Se, ad esempio, è stato distribuito un trigger, è possibile verificarlo eseguendo uno script che inserisce una nuova riga nella tabella sulla quale il trigger è impostato, causandone l'attivazione. Nel codice di debug riportato di seguito si presuppone l'esistenza di due tabelle con le seguenti definizioni:
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
Vedere anche
Attività
Procedura dettagliata: creazione di una stored procedure nel codice gestito
Procedura: eseguire il debug di una stored procedure CLR SQL
Riferimenti
Attributi per oggetti di database e progetti di database CLR SQL
Concetti
Introduzione all'integrazione con CLR di SQL Server (ADO.NET)
Vantaggi dell'utilizzo di codice gestito per creare oggetti di database
Creazione di oggetti SQL Server nel codice gestito