Comment : créer et exécuter un déclencheur SQL Server à l'aide de l'intégration du Common Language Runtime

Article
08/10/2011

Créez un déclencheur SQL en ajoutant un élément Déclencheur au projet de base de données du Common Language Runtime SQL Server (CLR SQL). Une fois le déploiement effectué, les déclencheurs créés dans le code managé sont appelés et exécutés comme n'importe quels autres déclencheurs Transact-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 Transact-SQL.

Notes

Il est possible que votre ordinateur affiche des noms ou des emplacements différents pour certains des éléments d'interface utilisateur de 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 de base de données CLR SQL existant ou créez-en un. Pour plus d'informations, consultez Comment : créer un projet pour des objets de base de données qui utilisent l'intégration du Common Language Runtime SQL Server.
Dans le menu Projet, cliquez sur Ajouter un nouvel élément.
Dans la boîte de dialogue Ajouter un nouvel élément, sélectionnez Déclencheur.
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.
Dans l'Explorateur de solutions, ouvrez le dossier TestScripts et double-cliquez sur le fichier Test.sql.

Notes

Vous pouvez spécifier d'autres scripts comme script de débogage par défaut. Pour plus d'informations, consultez Comment : modifier le script Test.sql pour exécuter des objets qui utilisent l'intégration du Common Language Runtime SQL Server.
Ajoutez le code au fichier Test.sql pour 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 de base de données CLR SQL sur un serveur SQL Server.

Important

SQL Server 2005 et SQL Server 2008 prennent uniquement en charge les projets SQL Server générés avec la version 2.0, 3.0 ou 3.5 de .NET Framework. Si vous essayezSQL Server de déployer un projet versSQL Server 2005 ouSQL Server 2008, une erreur s'affiche : 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 (où AssemblyName est le nom de l'assembly que vous déployez). Pour plus d'informations, consultez Comment : créer un projet pour des objets de base de données qui utilisent l'intégration du Common Language Runtime SQL Server.
Affichez les résultats affichés dans etSortie, fenêtre entrez la sortie de spectacle de : Sortie de base de données.

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

Ajoutez le code permettant d'exécuter et de tester votre déclencheur au fichier Test.sql situé dans le dossier Scripts de test 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

Partager via

Comment : créer et exécuter un déclencheur SQL Server à l'aide de l'intégration du Common Language Runtime

Création du déclencheur SQL Server

Pour créer un déclencheur SQL Server

Exemple

Voir aussi

Tâches

Référence

Concepts

Autres ressources

Ressources supplémentaires