Gewusst wie: Erstellen und Ausführen eines CLR SQL Server-Triggers

Artikel
09/02/2008

Aktualisiert: November 2007

Erstellen Sie einen SQL-Trigger, indem Sie ein Trigger-Element einem SQL Server-Projekt hinzufügen. Nach einer erfolgreichen Bereitstellung werden Trigger, die in verwaltetem Code erstellt wurden, genauso aufgerufen und ausgeführt wie jeder andere T-SQL-Trigger. Die in einer verwalteten Sprache geschriebenen Trigger können mithilfe der SqlTriggerContext-Klasse auf dieselben Informationen zugreifen wie T-SQL-Trigger.

Hinweis:
Das CLR-Integrationsfeature (Common Language Runtime) ist in Microsoft SQL Server standardmäßig deaktiviert und muss aktiviert werden, damit SQL Server-Projektelemente verwendet werden können. Die CLR-Integration kann mithilfe der Option CLR aktiviert der gespeicherten Prozedur sp_configure aktiviert werden. Weitere Informationen finden Sie unter Aktivieren der CLR-Integration.

Hinweis:
Ihr Computer zeigt möglicherweise für einige der Elemente der Visual Studio-Benutzeroberfläche in der folgenden Anleitung andere Namen oder Standorte an. Diese Elemente sind von der jeweiligen Visual Studio-Version und den verwendeten Einstellungen abhängig. Weitere Informationen finden Sie unter Visual Studio-Einstellungen.

Erstellen des SQL Server-Triggers

So erstellen Sie einen SQL Server-Trigger

Öffnen Sie ein vorhandenes SQL Server-Projekt, oder erstellen Sie ein neues. Weitere Informationen finden Sie unter Gewusst wie: Erstellen eines SQL Server-Projekts.
Wählen Sie im Menü Projekt die Option Neues Element hinzufügen aus.
Wählen Sie in Dialogfeld "Neues Element hinzufügen" die Option Trigger aus.
Geben Sie einen Namen für den neuen Trigger ein.
Fügen Sie den Code hinzu, der beim Ausführen des Triggers ausgeführt werden soll. Beachten Sie das erste Beispiel, das dieser Prozedur folgt.

Hinweis:

C++-Beispiele müssen mit der /clr:safe-Compileroption kompiliert werden.
Öffnen Sie für Visual Basic und Visual C# im Projektmappen-Explorer den Ordner TestScripts, und doppelklicken Sie auf die Datei Test.sql.

Doppelklicken Sie für Visual C++ im Projektmappen-Explorer auf die Datei debug.sql.
Fügen Sie der Datei Test.sql (bzw. debug.sql in Visual C++) Code hinzu, um den Trigger auszuführen. Beachten Sie das zweite Beispiel, das dieser Prozedur folgt.
Drücken Sie F5, um den Trigger zu erstellen, bereitzustellen und zu debuggen. Informationen zum Bereitstellen ohne Debugging finden Sie unter Gewusst wie: Bereitstellen von SQL Server-Projektelementen auf einem SQL-Server.
Prüfen Sie die im Ausgabefenster angezeigten Ergebnisse, und wählen Sie Ausgabe anzeigen von: Datenbankausgabe aus.

Hinweis:
C++-Beispiele müssen mit der /clr:safe-Compileroption kompiliert werden.

Beispiel

In diesem Beispiel wird das folgende Szenario veranschaulicht: Benutzer können einen beliebigen Benutzernamen auswählen. Sie möchten nun erfahren, welche Benutzer eine E-Mail-Adresse als Benutzernamen eingegeben haben. Der Trigger erkennt diese Informationen und protokolliert sie in einer Überwachungstabelle.

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

Fügen Sie der Datei Test.sql (bzw. debug.sql in Visual C++) im Ordner TestScripts Ihres Projekts Code hinzu, um den Trigger auszuführen und zu testen. Wenn ein Trigger bereitgestellt wurde, können Sie ihn z. B. durch Ausführen eines Skripts testen, mit dem eine neue Zeile in die Tabelle eingefügt wird, bei der der Trigger definitionsgemäß ausgelöst werden muss. Im folgenden Debugcode wird davon ausgegangen, dass zwei Tabellen mit den folgenden Definitionen vorhanden sind:

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

Siehe auch

Freigeben über

Gewusst wie: Erstellen und Ausführen eines CLR SQL Server-Triggers

Erstellen des SQL Server-Triggers

So erstellen Sie einen SQL Server-Trigger

Beispiel

Siehe auch

Aufgaben

Konzepte

Referenz

Weitere Ressourcen

Zusätzliche Ressourcen