Comment : créer et exécuter un déclencheur SQL Server CLR

Article
08/21/2008

Mise à jour : novembre 2007

Créez un déclencheur SQL en ajoutant un élément Déclencheur à un projet SQL Server. Une fois le déploiement réussi, les déclencheurs créés dans le code managé sont appelés et exécutés comme tout autre déclencheur T-SQL. Les déclencheurs écrits dans un langage managé peuvent utiliser la classe SqlTriggerContext pour avoir accès aux mêmes informations que les déclencheurs T-SQL.

Remarque :
La fonctionnalité d'intégration du Common Language Runtime (CLR) est désactivée par défaut dans Microsoft SQL Server et doit être activée pour utiliser des éléments de projet SQL Server. Pour activer l'intégration du CLR, utilisez l'option clr enabled de la procédure stockée sp_configure. Pour plus d'informations, consultez Activation de l'intégration CLR.

Remarque :
Il est possible que votre ordinateur affiche des noms ou des emplacements différents pour certains des éléments d'interface utilisateur Visual Studio dans les instructions suivantes. L'édition de Visual Studio dont vous disposez et les paramètres que vous utilisez déterminent ces éléments. Pour plus d'informations, consultez Paramètres Visual Studio.

Création du déclencheur SQL Server

Pour créer un déclencheur SQL Server

Ouvrez un Projet SQL Server existant ou créez-en un. Pour plus d'informations, consultez Comment : créer un projet SQL Server.
Dans le menu Projet, sélectionnez Ajouter un nouvel élément.
Sélectionnez Déclencheur dans la Ajouter un nouvel élément, boîte de dialogue.
Tapez un Nom pour le nouveau déclencheur.
Ajoutez le code à exécuter en même temps que le déclencheur. Consultez le premier exemple qui suit cette procédure.

Remarque :

Les exemples C++ doivent être compilés avec l'option du compilateur /clr:safe.
Pour Visual Basic et Visual C#, dans l'Explorateur de solutions, ouvrez le dossier TestScripts et double-cliquez sur le fichier Test.sql.

Pour Visual C++, dans l'Explorateur de solutions, double-cliquez sur le fichier debug.sql.
Ajoutez au fichier Test.sql (debug.sql en Visual C++) le code permettant d'exécuter le déclencheur. Consultez le deuxième exemple qui suit cette procédure.
Appuyez sur F5 pour générer, déployer et déboguer le déclencheur. Pour plus d'informations sur le déploiement sans débogage, consultez Comment : déployer des éléments de projet SQL Server sur un serveur SQL Server.
Consultez les résultats affichés dans la Sortie, fenêtre et sélectionnez Afficher la sortie à partir de : Sortie de base de données.

Remarque :
Les exemples C++ doivent être compilés avec l'option du compilateur /clr:safe.

Exemple

Cet exemple illustre un scénario selon lequel les utilisateurs choisissent le nom d'utilisateur qu'ils souhaitent, mais vous voulez savoir quels utilisateurs ont entré une adresse de messagerie comme nom d'utilisateur. Ce déclencheur détecte cette information et la consigne dans une table d'audits.

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

Ajoutez le code permettant d'exécuter et de tester votre déclencheur au fichier Test.sql (debug.sql en Visual C++) situé dans le dossier TestScripts de votre projet. Par exemple, si vous avez déployé un déclencheur, vous pouvez le tester en exécutant un script qui insère une nouvelle ligne dans la table sur laquelle il est défini, entraînant ainsi l'exécution du déclencheur. Le code de débogage suivant suppose que deux tables existent avec les définitions suivantes :

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

Voir aussi

Comment : créer et exécuter un déclencheur SQL Server CLR

Création du déclencheur SQL Server

Pour créer un déclencheur SQL Server

Exemple

Voir aussi

Tâches

Concepts

Référence

Autres ressources

Ressources supplémentaires