Comment : créer et exécuter un déclencheur SQL Server CLR
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.
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
Tâches
Comment : créer un projet SQL Server
Comment : créer et exécuter une procédure stockée SQL Server CLR
Comment : créer et exécuter un déclencheur SQL Server CLR
Comment : créer et exécuter un agrégat SQL Server CLR
Comment : créer et exécuter une fonction définie par l'utilisateur SQL Server CLR
Comment : créer et exécuter un type défini par l'utilisateur SQL Server CLR
Procédure pas à pas : création d'une procédure stockée dans le code managé
Comment : déboguer une procédure stockée SQL CLR
Concepts
Présentation de l'intégration de CLR dans SQL Server (ADO.NET)
Avantages de l'utilisation de code managé pour créer des objets de base de données
Modèles d'élément pour les projets SQL Server
Référence
Attributs pour les projets SQL Server et les objets de base de données