Freigeben über

CLR-Trigger

Aufgrund der SQL Server-Integration in die Common Language Runtime (CLR) von .NET Framework können Sie eine beliebige .NET Framework-Sprache verwenden, um CLR-Trigger zu erstellen. In diesem Abschnitt werden Informationen behandelt, die spezifisch für Trigger sind, die mit der CLR-Integration implementiert wurden. Eine vollständige Erläuterung von Triggern finden Sie unter DDL-Trigger.

Was sind Trigger?

Ein Trigger ist ein spezieller Typ gespeicherter Prozedur, der automatisch ausgeführt wird, wenn ein Sprachereignis ausgeführt wird. SQL Server enthält zwei allgemeine Arten von Triggern: Datenmanipulationssprache (Data Manipulation Language, DML) und DDL-Trigger (Data Definition Language). DML-Trigger können verwendet werden, wenn INSERTUPDATE, oder DELETE Anweisungen Daten in einer angegebenen Tabelle oder Ansicht ändern. DDL löst gespeicherte Prozeduren als Reaktion auf eine Vielzahl von DDL-Anweisungen aus, die in erster Linie mit CREATE, und ALTERDROP. DDL-Trigger können für administrative Aufgaben verwendet werden, z. B. Überwachung und Regulierung von Datenbankvorgängen.

Eindeutige Funktionen von CLR-Triggern

Trigger, die in Transact-SQL geschrieben wurden, verfügen über die Möglichkeit, zu bestimmen, welche Spalten aus der Auslösenansicht oder Tabelle mithilfe der UPDATE(column) Funktionen COLUMNS_UPDATED() aktualisiert wurden.

Trigger, die in einer CLR-Sprache geschrieben wurden, unterscheiden sich von anderen CLR-Integrationsobjekten auf verschiedene Weise. CLR-Trigger können:

  • Verweisen auf Daten in den INSERTED und DELETED Tabellen

  • Ermitteln, welche Spalten aufgrund eines UPDATE Vorgangs geändert wurden

  • Greifen Sie auf Informationen zu Datenbankobjekten zu, die von der Ausführung von DDL-Anweisungen betroffen sind.

Diese Funktionen werden inhärent in der Abfragesprache oder von der SqlTriggerContext Klasse bereitgestellt. Informationen zu den Vorteilen der CLR-Integration und der Auswahl zwischen verwaltetem Code und Transact-SQL finden Sie in der Übersicht über die CLR-Integration.

Verwenden der SqlTriggerContext-Klasse

Die SqlTriggerContext Klasse kann nicht öffentlich erstellt werden und kann nur durch Den Zugriff auf die SqlContext.TriggerContext Eigenschaft innerhalb des Textkörpers eines CLR-Triggers abgerufen werden. Die SqlTriggerContext Klasse kann über die aktive SqlContext Klasse abgerufen werden, indem sie die SqlContext.TriggerContext Eigenschaft aufruft:

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

Die SqlTriggerContext Klasse stellt Kontextinformationen zum Trigger bereit. Diese Kontextinformationen enthalten den Typ der Aktion, die dazu führte, dass der Trigger ausgelöst wurde, welche Spalten in einem UPDATE-Vorgang geändert wurden, und im Falle eines DDL-Triggers eine XML-Struktur EventData , die den auslösenden Vorgang beschreibt. Weitere Informationen finden Sie unter EVENTDATA (Transact-SQL).

Bestimmen der Triggeraktion

Nachdem Sie ein SqlTriggerContextEreignis erhalten haben, können Sie ihn verwenden, um den Aktionstyp zu bestimmen, der den Auslöser ausgelöst hat. Diese Informationen sind über die TriggerAction Eigenschaft der SqlTriggerContext Klasse verfügbar.

Bei DML-Triggern kann die TriggerAction Eigenschaft einen der folgenden Werte aufweisen:

  • TriggerAction.Update (0x1)

  • TriggerAction.Insert (0x2)

  • TriggerAction.Delete(0x3)

  • Bei DDL-Triggern ist die Liste der möglichen TriggerAction-Werte erheblich länger. Weitere Informationen finden Sie unter "TriggerAction Enumeration" im .NET Framework SDK.

Verwenden der eingefügten und gelöschten Tabellen

In DML-Triggeranweisungen werden zwei spezielle Tabellen verwendet: die eingefügte Tabelle und die gelöschte Tabelle. SQL Server erstellt und verwaltet diese Tabellen automatisch. Sie können diese temporären Tabellen verwenden, um die Auswirkungen bestimmter Datenänderungen zu testen und Bedingungen für DML-Triggeraktionen festzulegen; Sie können die Daten in den Tabellen jedoch nicht direkt ändern.

CLR-Trigger können über den CLR-In-Process-Anbieter auf die eingefügten und gelöschten Tabellen zugreifen. Dies erfolgt durch Abrufen eines SqlCommand Objekts aus dem SqlContext-Objekt. Beispiel:

C#

SqlConnection connection = new SqlConnection ("context connection = true");  
connection.Open();  
SqlCommand command = connection.CreateCommand();  
command.CommandText = "SELECT * from " + "inserted";

Visual Basic

Dim connection As New SqlConnection("context connection=true")  
Dim command As SqlCommand  
connection.Open()  
command = connection.CreateCommand()  
command.CommandText = "SELECT * FROM " + "inserted"

Bestimmen aktualisierter Spalten

Sie können die Anzahl der Spalten bestimmen, die von einem UPDATE-Vorgang geändert wurden, indem Sie die ColumnCount Eigenschaft des SqlTriggerContext Objekts verwenden. Sie können die IsUpdatedColumn Methode verwenden, die die Spalten-Ordnungszahl als Eingabeparameter verwendet, um zu bestimmen, ob die Spalte aktualisiert wurde. Ein True Wert gibt an, dass die Spalte aktualisiert wurde.

Dieser Codeausschnitt (aus dem EmailAudit-Trigger weiter unten in diesem Thema) listet beispielsweise alle aktualisierten Spalten auf:

C#

reader = command.ExecuteReader();  
reader.Read();  
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)  
{  
   pipe.Send("Updated column "  
      + reader.GetName(columnNumber) + "? "  
   + triggContext.IsUpdatedColumn(columnNumber).ToString());  
 }  
  
 reader.Close();

Visual Basic

reader = command.ExecuteReader()  
reader.Read()  
Dim columnNumber As Integer  
  
For columnNumber=0 To triggContext.ColumnCount-1  
  
   pipe.Send("Updated column " & reader.GetName(columnNumber) & _  
   "? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )  
  
Next  
  
reader.Close()

Zugreifen auf EventData für CLR-DDL-Trigger

DDL-Trigger, wie normale Trigger, lösen gespeicherte Prozeduren als Reaktion auf ein Ereignis aus. Im Gegensatz zu DML-Triggern werden sie jedoch nicht als Reaktion auf UPDATE-, INSERT- oder DELETE-Anweisungen in einer Tabelle oder Ansicht ausgelöst. Stattdessen werden sie als Reaktion auf eine Vielzahl von DDL-Anweisungen ausgelöst, die in erster Linie mit CREATE, ALTER und DROP beginnen. DDL-Trigger können für administrative Aufgaben verwendet werden, z. B. Überwachung und Überwachung von Datenbankvorgängen und Schemaänderungen.

Informationen zu einem Ereignis, das einen DDL-Trigger auslöst, ist in der EventData Eigenschaft der SqlTriggerContext Klasse verfügbar. Diese Eigenschaft enthält einen xml Wert. Das XML-Schema enthält Informationen zu:

  • Zeitpunkt des Ereignisses.

  • Die Systemprozess-ID (SPID) der Verbindung, in der der Trigger ausgeführt wurde.

  • Der Ereignistyp, der den Trigger ausgelöst hat.

Je nach Ereignistyp enthält das Schema dann zusätzliche Informationen, z. B. die Datenbank, in der das Ereignis aufgetreten ist, das Objekt, für das das Ereignis aufgetreten ist, und den Transact-SQL Befehl des Ereignisses.

Im folgenden Beispiel gibt der folgende DDL-Trigger die unformatierte EventData Eigenschaft zurück.

Hinweis

Das Senden von Ergebnissen und Nachrichten über das SqlPipe Objekt wird hier nur zu Veranschaulichungszwecken angezeigt und wird im Allgemeinen beim Programmieren von CLR-Triggern für Produktionscode abgeraten. Zusätzliche zurückgegebene Daten können unerwartet sein und zu Anwendungsfehlern führen.

C#

using System;  
using System.Data;  
using System.Data.Sql;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using System.Xml;  
using System.Text.RegularExpressions;  
  
public class CLRTriggers  
{  
   public static void DropTableTrigger()  
   {  
       SqlTriggerContext triggContext = SqlContext.TriggerContext;             
  
       switch(triggContext.TriggerAction)  
       {  
           case TriggerAction.DropTable:  
           SqlContext.Pipe.Send("Table dropped! Here's the EventData:");  
           SqlContext.Pipe.Send(triggContext.EventData.Value);  
           break;  
  
           default:  
           SqlContext.Pipe.Send("Something happened! Here's the EventData:");  
           SqlContext.Pipe.Send(triggContext.EventData.Value);  
           break;  
       }  
   }  
}

Visual Basic

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class CLRTriggers   
  
    Public Shared Sub DropTableTrigger()  
        Dim triggContext As SqlTriggerContext  
        triggContext = SqlContext.TriggerContext  
  
        Select Case triggContext.TriggerAction  
           Case TriggerAction.DropTable  
              SqlContext.Pipe.Send("Table dropped! Here's the EventData:")  
              SqlContext.Pipe.Send(triggContext.EventData.Value)  
  
           Case Else  
              SqlContext.Pipe.Send("Something else happened! Here's the EventData:")  
              SqlContext.Pipe.Send(triggContext.EventData.Value)  
  
        End Select  
    End Sub  
End Class

Die folgende Beispielausgabe ist der EventData Eigenschaftswert, nachdem ein DDL-Trigger durch ein CREATE TABLE Ereignis ausgelöst wurde:

<EVENT_INSTANCE><PostTime>2004-04-16T21:17:16.160</PostTime><SPID>58</SPID><EventType>CREATE_TABLE</EventType><ServerName>MACHINENAME</ServerName><LoginName>MYDOMAIN\myname</LoginName><UserName>MYDOMAIN\myname</UserName><DatabaseName>AdventureWorks</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>UserName</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create table dbo.UserName ( UserName varchar(50), RealName varchar(50) ) </CommandText></TSQLCommand></EVENT_INSTANCE>

Zusätzlich zu den Informationen, auf die über die SqlTriggerContext Klasse zugegriffen werden kann, können Abfragen weiterhin innerhalb des Texts eines befehls, der in einem Prozess ausgeführt wird, darauf verweisen COLUMNS_UPDATED und eingefügt/gelöscht werden.

BEISPIEL-CLR-Trigger

Betrachten Sie in diesem Beispiel das Szenario, in dem Sie dem Benutzer eine beliebige ID auswählen lassen, aber Sie möchten die Benutzer kennen, die eine E-Mail-Adresse speziell als ID eingegeben haben. Der folgende Auslöser würde diese Informationen erkennen und in einer Überwachungstabelle protokollieren.

Hinweis

Das Senden von Ergebnissen und Nachrichten über das SqlPipe Objekt wird hier nur zu Veranschaulichungszwecken angezeigt und wird im Allgemeinen für Produktionscode abgeraten. Zusätzliche zurückgegebene Daten können unerwartet sein und zu Anwendungsfehlern führen.

using System;  
using System.Data;  
using System.Data.Sql;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using System.Xml;  
using System.Text.RegularExpressions;  
  
public class CLRTriggers  
{  
   [SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]  
   public static void EmailAudit()  
   {  
      string userName;  
      string realName;  
      SqlCommand command;  
      SqlTriggerContext triggContext = SqlContext.TriggerContext;  
      SqlPipe pipe = SqlContext.Pipe;  
      SqlDataReader reader;  
  
      switch (triggContext.TriggerAction)  
      {  
         case TriggerAction.Insert:  
         // Retrieve the connection that the trigger is using  
         using (SqlConnection connection  
            = new SqlConnection(@"context connection=true"))  
         {  
            connection.Open();  
            command = new SqlCommand(@"SELECT * FROM INSERTED;",  
               connection);  
            reader = command.ExecuteReader();  
            reader.Read();  
            userName = (string)reader[0];  
            realName = (string)reader[1];  
            reader.Close();  
  
            if (IsValidEMailAddress(userName))  
            {  
               command = new SqlCommand(  
                  @"INSERT [dbo].[UserNameAudit] VALUES ('"  
                  + userName + @"', '" + realName + @"');",  
                  connection);  
               pipe.Send(command.CommandText);  
               command.ExecuteNonQuery();  
               pipe.Send("You inserted: " + userName);  
            }  
         }  
  
         break;  
  
         case TriggerAction.Update:  
         // Retrieve the connection that the trigger is using  
         using (SqlConnection connection  
            = new SqlConnection(@"context connection=true"))  
         {  
            connection.Open();  
            command = new SqlCommand(@"SELECT * FROM INSERTED;",  
               connection);  
            reader = command.ExecuteReader();  
            reader.Read();  
  
            userName = (string)reader[0];  
            realName = (string)reader[1];  
  
            pipe.Send(@"You updated: '" + userName + @"' - '"  
               + realName + @"'");  
  
            for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)  
            {  
               pipe.Send("Updated column "  
                  + reader.GetName(columnNumber) + "? "  
                  + triggContext.IsUpdatedColumn(columnNumber).ToString());  
            }  
  
            reader.Close();  
         }  
  
         break;  
  
         case TriggerAction.Delete:  
            using (SqlConnection connection  
               = new SqlConnection(@"context connection=true"))  
               {  
                  connection.Open();  
                  command = new SqlCommand(@"SELECT * FROM DELETED;",  
                     connection);  
                  reader = command.ExecuteReader();  
  
                  if (reader.HasRows)  
                  {  
                     pipe.Send(@"You deleted the following rows:");  
                     while (reader.Read())  
                     {  
                        pipe.Send(@"'" + reader.GetString(0)  
                        + @"', '" + reader.GetString(1) + @"'");  
                     }  
  
                     reader.Close();  
  
                     //alternately, to just send a tabular resultset back:  
                     //pipe.ExecuteAndSend(command);  
                  }  
                  else  
                  {  
                     pipe.Send("No rows affected.");  
                  }  
               }  
  
               break;  
            }  
        }  
  
     public static bool IsValidEMailAddress(string email)  
     {  
         return Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$");  
     }  
}

Visual Basic

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports System.Text.RegularExpressions  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class CLRTriggers   
  
    <SqlTrigger(Name:="EmailAudit", Target:="[dbo].[Users]", Event:="FOR INSERT, UPDATE, DELETE")> _  
    Public Shared Sub EmailAudit()  
        Dim userName As String  
        Dim realName As String  
        Dim command As SqlCommand  
        Dim triggContext As SqlTriggerContext  
        Dim pipe As SqlPipe  
        Dim reader As SqlDataReader    
  
        triggContext = SqlContext.TriggerContext      
        pipe = SqlContext.Pipe    
  
        Select Case triggContext.TriggerAction  
           Case TriggerAction.Insert  
              Using connection As New SqlConnection("context connection=true")  
                 connection.Open()  
                 command = new SqlCommand("SELECT * FROM INSERTED;", connection)  
  
                 reader = command.ExecuteReader()  
                 reader.Read()  
  
                 userName = CType(reader(0), String)  
                 realName = CType(reader(1), String)  
  
                 reader.Close()  
  
                 If IsValidEmailAddress(userName) Then  
                     command = New SqlCommand("INSERT [dbo].[UserNameAudit] VALUES ('" & _  
                       userName & "', '" & realName & "');", connection)  
  
                    pipe.Send(command.CommandText)  
                    command.ExecuteNonQuery()  
                    pipe.Send("You inserted: " & userName)  
  
                 End If  
              End Using  
  
           Case TriggerAction.Update  
              Using connection As New SqlConnection("context connection=true")  
                 connection.Open()  
                 command = new SqlCommand("SELECT * FROM INSERTED;", connection)  
  
                 reader = command.ExecuteReader()  
                 reader.Read()  
  
                 userName = CType(reader(0), String)  
                 realName = CType(reader(1), String)  
  
                 pipe.Send("You updated: " & userName & " - " & realName)  
  
                 Dim columnNumber As Integer  
  
                 For columnNumber=0 To triggContext.ColumnCount-1  
  
                    pipe.Send("Updated column " & reader.GetName(columnNumber) & _  
                      "? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )  
  
                 Next  
  
                 reader.Close()  
              End Using  
  
           Case TriggerAction.Delete  
              Using connection As New SqlConnection("context connection=true")  
                 connection.Open()  
                 command = new SqlCommand("SELECT * FROM DELETED;", connection)  
  
                 reader = command.ExecuteReader()  
  
                 If reader.HasRows Then  
                    pipe.Send("You deleted the following rows:")  
  
                    While reader.Read()  
  
                       pipe.Send( reader.GetString(0) & ", " & reader.GetString(1) )  
  
                    End While   
  
                    reader.Close()  
  
                    ' Alternately, just send a tabular resultset back:  
                    ' pipe.ExecuteAndSend(command)  
  
                 Else  
                   pipe.Send("No rows affected.")  
                 End If  
  
              End Using   
        End Select  
    End Sub  
  
    Public Shared Function IsValidEMailAddress(emailAddress As String) As Boolean  
  
       return Regex.IsMatch(emailAddress, "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")  
    End Function      
End Class

Es wird vorausgesetzt, dass zwei Tabellen mit den folgenden Definitionen vorhanden sind:

CREATE TABLE Users  
(  
    UserName nvarchar(200) NOT NULL,  
    RealName nvarchar(200) NOT NULL  
);  
GO CREATE TABLE UserNameAudit  
(  
    UserName nvarchar(200) NOT NULL,  
    RealName nvarchar(200) NOT NULL  
)

Die Transact-SQL-Anweisung, die den Trigger in SQL Server erstellt, lautet wie folgt, und es wird davon ausgegangen, dass die Assembly SQLCLRTest bereits in der aktuellen SQL Server-Datenbank registriert ist.

CREATE TRIGGER EmailAudit  
ON Users  
FOR INSERT, UPDATE, DELETE  
AS  
EXTERNAL NAME SQLCLRTest.CLRTriggers.EmailAudit

Überprüfen und Abbrechen ungültiger Transaktionen

Die Verwendung von Triggern zum Überprüfen und Abbrechen ungültiger INSERT-, UPDATE- oder DELETE-Transaktionen oder zum Verhindern von Änderungen am Datenbankschema ist üblich. Dies kann erreicht werden, indem Sie validierungslogik in Den Trigger integrieren und dann das rollbacken der aktuellen Transaktion, wenn die Aktion nicht den Überprüfungskriterien entspricht.

Wenn innerhalb eines Triggers aufgerufen wird, löst die Transaction.Rollback Methode oder ein SqlCommand mit dem Befehlstext "TRANSACTION ROLLBACK" eine Ausnahme mit einer mehrdeutigen Fehlermeldung aus und muss in einen Try/Catch-Block eingeschlossen werden. Die angezeigte Fehlermeldung ähnelt folgendem:

Msg 6549, Level 16, State 1, Procedure trig_InsertValidator, Line 0  
A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_InsertValidator':   
System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting... User transaction, if any, will be rolled back.

Diese Ausnahme wird erwartet und der try/catch-Block ist notwendig, damit die Codeausführung fortgesetzt wird. Wenn der Triggercode die Ausführung beendet, wird eine andere Ausnahme ausgelöst.

Msg 3991, Level 16, State 1, Procedure trig_InsertValidator, Line 1   
The context transaction which was active before entering user defined routine, trigger or aggregate "trig_InsertValidator" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.  
The statement has been terminated.

Diese Ausnahme wird ebenfalls erwartet, und ein Try/Catch-Block um die Transact-SQL-Anweisung, die die Aktion ausführt, die den Trigger auslöst, ist erforderlich, damit die Ausführung fortgesetzt werden kann. Trotz der beiden ausgelösten Ausnahmen wird die Transaktion zurückgesetzt, und die Änderungen werden nicht für die Tabelle übernommen. Ein wichtiger Unterschied zwischen CLR-Triggern und Transact-SQL Triggern besteht darin, dass Transact-SQL Trigger weiterhin mehr Arbeit ausführen können, nachdem die Transaktion zurückgesetzt wurde.

Beispiel

Der folgende Trigger führt eine einfache Überprüfung von INSERT-Anweisungen in einer Tabelle durch. Wenn der eingefügte ganzzahlige Wert gleich einem wert ist, wird die Transaktion zurückgesetzt, und der Wert wird nicht in die Tabelle eingefügt. Alle anderen ganzzahligen Werte werden in die Tabelle eingefügt. Beachten Sie den Try/Catch-Block um die Transaction.Rollback Methode. Das skript Transact-SQL erstellt eine Testtabelle, Assembly und verwaltete gespeicherte Prozedur. Beachten Sie, dass die beiden INSERT-Anweisungen in einen Try/Catch-Block eingeschlossen werden, damit die Ausnahme ausgelöst wird, wenn die Ausführung des Triggers abgeschlossen ist.

C#

using System;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;  
using System.Transactions;  
  
public partial class Triggers  
{  
    // Enter existing table or view for the target and uncomment the attribute line  
    // [Microsoft.SqlServer.Server.SqlTrigger (Name="trig_InsertValidator", Target="Table1", Event="FOR INSERT")]  
    public static void trig_InsertValidator()  
    {  
        using (SqlConnection connection = new SqlConnection(@"context connection=true"))  
        {  
            SqlCommand command;  
            SqlDataReader reader;  
            int value;  
  
            // Open the connection.  
            connection.Open();  
  
            // Get the inserted value.  
            command = new SqlCommand(@"SELECT * FROM INSERTED", connection);  
            reader = command.ExecuteReader();  
            reader.Read();  
            value = (int)reader[0];  
            reader.Close();  
  
            // Rollback the transaction if a value of 1 was inserted.  
            if (1 == value)  
            {  
                try  
                {  
                    // Get the current transaction and roll it back.  
                    Transaction trans = Transaction.Current;  
                    trans.Rollback();                      
                }  
                catch (SqlException ex)  
                {  
                    // Catch the expected exception.                      
                }  
            }  
            else  
            {  
                // Perform other actions here.  
            }  
  
            // Close the connection.  
            connection.Close();              
        }  
    }  
}

Visual Basic

Imports System  
Imports System.Data.SqlClient  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Transactions  
  
Partial Public Class Triggers  
' Enter existing table or view for the target and uncomment the attribute line  
' <Microsoft.SqlServer.Server.SqlTrigger(Name:="trig_InsertValidator", Target:="Table1", Event:="FOR INSERT")> _  
Public Shared Sub  trig_InsertValidator ()  
    Using connection As New SqlConnection("context connection=true")  
  
        Dim command As SqlCommand  
        Dim reader As SqlDataReader  
        Dim value As Integer  
  
        ' Open the connection.  
        connection.Open()  
  
        ' Get the inserted value.  
        command = New SqlCommand("SELECT * FROM INSERTED", connection)  
        reader = command.ExecuteReader()  
        reader.Read()  
        value = CType(reader(0), Integer)  
        reader.Close()  
  
        ' Rollback the transaction if a value of 1 was inserted.  
        If value = 1 Then  
  
            Try  
                ' Get the current transaction and roll it back.  
                Dim trans As Transaction  
                trans = Transaction.Current  
                trans.Rollback()  
  
            Catch ex As SqlException  
  
                ' Catch the exception.                      
            End Try  
        Else  
  
            ' Perform other actions here.  
        End If  
  
        ' Close the connection.  
        connection.Close()  
    End Using  
End Sub  
End Class

Transact-SQL

-- Create the test table, assembly, and trigger.  
CREATE TABLE Table1(c1 int);  
go  
  
CREATE ASSEMBLY ValidationTriggers from 'E:\programming\ ValidationTriggers.dll';  
go  
  
CREATE TRIGGER trig_InsertValidator  
ON Table1  
FOR INSERT  
AS EXTERNAL NAME ValidationTriggers.Triggers.trig_InsertValidator;  
go  
  
-- Use a Try/Catch block to catch the expected exception  
BEGIN TRY  
   INSERT INTO Table1 VALUES(42)  
   INSERT INTO Table1 VALUES(1)  
END TRY  
BEGIN CATCH  
  SELECT ERROR_NUMBER() AS ErrorNum, ERROR_MESSAGE() AS ErrorMessage  
END CATCH;  
  
-- Clean up.  
DROP TRIGGER trig_InsertValidator;  
DROP ASSEMBLY ValidationTriggers;  
DROP TABLE Table1;

Siehe auch

CREATE TRIGGER (Transact-SQL)
DML-Trigger
DDL-Trigger
TRY... CATCH (Transact-SQL)
Erstellen von Datenbankobjekten mit ClR-Integration (Common Language Runtime)
EVENTDATA (Transact-SQL)

  • Last updated on