Procedura: creare ed eseguire un trigger CLR di SQL Server

Articolo
09/12/2008

Aggiornamento: novembre 2007

Creare un trigger SQL aggiungendo un elemento Trigger a un progetto server SQL. Al termine della distribuzione, i trigger creati in codice gestito verranno chiamati ed eseguiti come qualsiasi altro trigger T-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 T-SQL.

Nota:
Per impostazione predefinita, in Microsoft SQL Server la funzionalità di integrazione di Common Language Runtime (CLR) è disattivata e deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

Per impostazione predefinita, in Microsoft SQL Server la funzionalità di integrazione di Common Language Runtime (CLR) è disattivata e deve essere attivata per poter utilizzare gli elementi del progetto SQL Server. Per attivare la funzionalità di integrazione di CLR, utilizzare l'opzione clr enabled della stored procedure sp_configure. Per ulteriori informazioni, vedere Attivazione dell'integrazione CLR.

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 SQL Server esistente o crearne uno nuovo. Per ulteriori informazioni, vedere Procedura: creare un progetto SQL Server.
Scegliere Aggiungi nuovo elemento dal menu Progetto.
Selezionare Trigger nella Finestra di dialogo Aggiungi nuovo elemento.
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.

Nota:

Gli esempi di C++ devono essere compilati con l'opzione del compilatore /clr:safe.
Per Visual Basic e Visual C#, in Esplora soluzioni aprire la cartella Script di test e fare doppio clic sul file Test.sql.

Per Visual C++, in Esplora soluzioni fare doppio clic sul file debug.sql.
Aggiungere codice al file Test.sql (debug.sql in Visual C++) 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 del progetto SQL Server su SQL Server.
Visualizzare i risultati presenti nella Finestra di output e selezionare Mostra output di: Output database.

Nota:
Gli esempi di C++ devono essere compilati con l'opzione del compilatore /clr:safe.

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]+$");
    }
}

#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]+$");
    }
};

Aggiungere codice per eseguire e verificare il trigger nel file Test.sql (debug.sql in Visual C++) 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

Condividi tramite

Procedura: creare ed eseguire un trigger CLR di SQL Server

Creazione del trigger SQL Server

Per creare un trigger SQL Server

Esempio

Vedere anche

Attività

Concetti

Riferimenti

Altre risorse

Risorse aggiuntive