Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 1/2007.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Nutzen Sie Trigger, um Aktionen mit Datensatzänderungen zu verknüpfen.

Techniken

SQL Server, T-SQL, Trigger

Voraussetzungen

Microsoft SQL Server 2000 (MSDE) oder 2005 (Express)

Beispieldatei

Trigger.sql

Shortlink

431

Trigger im SQL Server

Ruprecht Dröge, Ratingen

Auch wenn Access und die Jet-Engine bereits zahlreiche Möglichkeiten einer „richtigen“ Datenbank enthalten, fehlen immer noch wichtige Funktionen: So finden Sie etwa beim Umstieg auf SQL Server-Backends viel Bekanntes, aber auch Unbekanntes – zum Beispiel Trigger. Im vorliegenden Beitrag stellen wir den Nutzen von und den Umgang mit Triggern vor.

Welche Aufgaben haben Trigger im SQL Server eigentlich? Laut Übersetzungshilfe beschreibt das Substantiv „Trigger“ den Auslöser, den Anstoß oder den Abzugsbügel und das Verb entsprechend ansteuern, auslösen, einleiten.

Ein Trigger löst also bei bestimmten Aktionen etwas aus. Da wir uns im SQL Server befinden, kann also nur ein SQL-Befehl etwas auslösen und das kann hier natürlich auch nur wieder ein SQL-Statement sein. Damit realisieren Trigger etwa die Funktionalität, die wir vom Client und dabei etwa aus Access als VBA-Ereignisprozedur kennen. Mit Ereignisprozeduren können Sie beispielsweise beim Einfügen eines Datensatzes eine Aktion auslösen und diesen Vorgang unter bestimmten Bedingungen abbrechen (s. Listing 1).

Listing 1: Eine Ereignisprozedur in VBA

Private Sub Form_BeforeInsert(Cancel As Integer)

     If Name = "Doofmann" Then

     MsgBox "Solche Namen wollen wir hier nicht!"

     Cancel = True

     End If

End Sub

Die unterschiedlichen Client-Programmierumgebungen unterstützen unterschiedliche Ereignisse. Was in VBA unter Access geht, ist vielleicht unter ADO.NET nicht verfügbar, und andersherum, unter ADO und C++ wiederum finden Sie wieder ganz andere Möglichkeiten.

Wenn aber die unterschiedlichen Clients solche Funktionen kennen, warum brauchen wir sie dann noch einmal im SQL Server? In einer richtigen Client-Server-Umgebung ist es eben nicht sichergestellt, dass alle Veränderungen an den Daten einer Datenbank über denselben Client abgewickelt werden. Wenn Sie eine Access-Anwendung auf einer SQL Server 2005-Datenbank programmieren und vor dem Löschen eines Datensatzes diesen unbedingt in eine „Ist-gelöscht-worden“-Tabelle schreiben möchten, dann ist die Ereignisprozedur in Access nicht der richtige Weg, da jeder Berechtigte direkt auf die SQL Server-Datenbank zugreifen und ein DELETE FROM tblMeineDaten WHERE ID=9 absetzen kann. Der Datensatz wäre ohne Dokumentation verschwunden (die Syntax „DELETE * FROM…“, also mit „*“ ist im Übrigen Access-Dialekt und nicht ANSI SQL).

Die Funktionalität wird also vom Client auf den Server verschoben. Ähnliches kennen Sie schon von der referentiellen Integrität. Auch das Löschen von Master-Datensätzen bei existierenden Detail-Datensätzen kann man mit einer client-seitigen Ereignisprozedur überwachen und gegebenenfalls verhindern, doch Access kann dies auch automatisch unterlassen.

Im SQL Server haben Sie noch viel mehr Möglichkeiten: Angenommen, Sie möchten die erwähnte Funktionalität umsetzen und jeden Datensatz vor dem Löschen in eine Dokumentations- oder Historisierungstabelle schreiben, dann sind Trigger genau das Richtige für Sie.

Zum Nachvollziehen der Beispiele bauen Sie am besten direkt eine kleine Testdatenbank auf. Dazu können Sie das Skript aus Listing 2 verwenden, das Sie am einfachsten über das SQL Server Management Studio des SQL Servers 2005 eingeben und ausgeführen (weitere Informationen siehe [1]). Die Syntax wird aber auch vom SQL Server 2000 unterstützt, die ersten Beispiele können Sie daher auch auf diesem nachvollziehen und das Skript beispielsweise im Query Analyser des SQL Servers 2000 ausführen. Wenn Sie die Beispiele wieder löschen wollen, löschen Sie einfach die gesamte Test-Datenbank mit dem Befehl DROP DATABASE TestTrigger. Falls Sie die Fehlermeldung erhalten, dass die Datenbank nicht gelöscht werden kann, weil sie in Benutzung ist (wahrscheinlich von Ihnen selbst durch die Tests), hilft der folgende kleine Trick beim Schließen aller bestehenden Verbindungen:

USE master

go

ALTER DATABASE TriggerTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

DROP DATABASE TriggerTest

Das Skript aus Listing 2 erledigt Folgendes: Der Befehl USE wechselt den aktuellen Datenbankkontext, weil es im SQL Server in einer Instanz mehrere Datenbanken geben kann (und meistens auch gibt). Nach dem Erstellen der Datenbank TestTrigger erstellt das Skript mit der CREATE TABLE-Anweisung zwei Tabellen. Die erste Tabelle MyData enthält die Ausgangsdaten, die zweite Tabelle MyDataHistory soll die Löschvorgänge mit Uhrzeit (DEFAULT entspricht dem Standardwert eines Feldes unter Access) und aktuellem Benutzer dokumentieren. Der Befehl GO beendet einen Batch, wobei zur Vereinfachung beinahe alle Befehle in einem einzigen Batch untergebracht sind. Die Anweisungen können dann einzeln markiert und nacheinander ausgeführt werden (wenn Sie F5 oder STRG+E drücken, wird sowohl im Query Analyser des SQL Server 2000 als auch im SQL Server Management Studio des SQL Server 2005 nur die Markierung ausgeführt).

Listing 2: Erstellen der benötigten Objekte für die Beispiele

-- SETUP der Beispieldatenbank ----------

USE master

go

CREATE DATABASE TriggerTest

GO

USE TriggerTest

GO

CREATE TABLE MyData

(

    ID int identity Primary Key,

    Name nvarchar(80)

)

GO

INSERT INTO MyData VALUES ("Müller")

INSERT INTO MyData VALUES ("Maier")

INSERT INTO MyData VALUES ("Schmidt")

INSERT INTO MyData VALUES ("Schulz")

INSERT INTO MyData VALUES ("Schäfer")

GO

SELECT * FROM myData

GO

CREATE TABLE MyDataHistory

(

    ID int,

    Name nvarchar(80),

    DeletedAt datetime DEFAULT GETDATE(),

    DeletedFrom nvarchar(80) DEFAULT SYSTEM_USER

)

Ein einfacher Trigger

Einen Trigger erstellt man wie andere Objekte in SQL mit dem CREATE-Befehl, hier mit CREATE TRIGGER. Trigger wie der geplante werden immer für eine bestimmte Tabelle erstellt. Diese Tabelle legt man mit dem Schlüsselwort ON fest (s. Listing 3).

Listing 3: Erstellen eines einfachen Triggers

CREATE TRIGGER DataHistory ON myData FOR DELETE

    AS

BEGIN

     INSERT INTO MyDataHistory (ID, Name) SELECT ID, Name FROM deleted

END

Beachten Sie das Schlüsselwort FOR DELETE. Genauso können Sie auch Trigger für das Einfügen und Ändern erstellen, dann verwenden Sie das Schlüsselwort FOR INSERT oder FOR UPDATE. Es gibt auch gleichnamige gemeinsame Trigger.

Löschen Sie nun mit den folgenden Anweisungen einmal ein paar Datensätze:

DELETE FROM MyData where ID=2

DELETE FROM MyData where ID=5

GO

Schauen Sie sich das Ergebnis in den beiden Tabellen an:

SELECT * FROM myData

SELECT * FROM myDataHistory

Interessant ist hier der Befehl, der im Trigger selbst ausgeführt wird: INSERT INTO… SELECT … FROM deleted. Um welche Tabelle handelt es sich denn bei deleted?

Dies ist keine tatsächlich im SQL Server vorhandene Tabelle. Die Abfrage SELECT * FROM deleted wird vom SQL Server dementsprechend mit der Fehlermeldung Invalid object name "deleted’ beantwortet.

Die Trigger-Tabellen

Der SQL Server stellt die Tabelle deleted allein im Kontext eines Triggers bereit und auch nur für die Dauer der Durchführung des Triggers. Es handelt sich tatsächlich um eine Tabelle, wenn auch keine im üblichen Sinne, und nicht etwa um einen einzelnen Datensatz, denn in einem SQL-Statement können durchaus mehrere Datensätze gelöscht werden. Für jedes Statement wird der Trigger aber nur einmal ausgelöst. Wenn Sie drei Datensätze wie im unten stehenden Listing löschen, wird der Trigger trotzdem nur einmal ausgelöst, die Tabelle deleted enthält dann eben drei Datensätze, wie wir einfach nachweisen können.

Bemerkenswert ist, dass der SQL Server beim Löschen von Datensätzen auch zwei Meldungen erzeugt (s. Abb. 1). (3 row(s) affected) bezieht sich bei der ersten Meldung auf das Einfügen in die History-Tabelle und bei der zweiten Meldung auf den Löschvorgang in MyData. Ob dies tatsächlich die richtige Reihenfolge ist, lässt sich leicht prüfen. Dazu ändern Sie den Trigger wie folgt, was dazu führt, dass der Datensatz mit dem Wert Beethoven für das Feld Name nicht in die History-Tabelle geschrieben wird.

ALTER TRIGGER [dbo].[DataHistory] ON [dbo].[MyData] FOR DELETE

AS

BEGIN

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

© 2003-2015 André Minhorst Alle Rechte vorbehalten.