Unsere Datenschutzrichtlinie wird in Kürze aktualisiert. Bitte sehen Sie sich die Vorschau an.

SQL Server 2016: Triggers, Stored Procedures und Funktionen

DDL-Trigger

Testen Sie unsere 2021 Kurse

10 Tage kostenlos!

Jetzt testen Alle Abonnements anzeigen
DDL-Trigger reagieren auf Änderungen auf Datenbank- oder Server-Ebene.
08:10

Transkript

Beginnen wir mit dem DDL-Trigger, also den Data Definition Language-Triggern, die immer dann aufgerufen werden, wenn es an der Struktur einer Datenbank oder auf dem Server Änderungen gibt, die also mit CREATE, mit DROP oder ALTER in Verbindung stehen, zumindest sind das die meisten Auslöser. Das Wichtige ist, sie bekommen einen kompletten Kontext, von dem der für den Aufruf verantwortlich ist. Das heißt, Sie haben eine Funktion zur Verfügung, EVENTDATA, und diese liefert als XML den kompletten Kontext, das heißt, wer hat, wann was aufgerufen, welches war exakt das Statement, was zur Auslösung des Triggers entsprechend geführt hat. Und Sie können die Änderungen, Trigger sind immer mit Änderungen assoziiert, entsprechend verändern, indem Sie einen Fehler oder ROLLBACK ausführen an der Stelle. Wie das aussieht, zeige ich Ihnen jetzt. Das erste Beispiel, was ich Ihnen zeige, benötigt zunächst einmal keine eigene Datenbank. Ganz bewusst wird auch hier oben in die master-Datenbank gewechselt, falls das nicht sowieso schon der Fall ist. Der Trigger soll CREATE_DATABASE überwachen, das heißt, wenn neuen Datenbanken angelegt werden. Das heißt, ich lasse das Ganze mal zusammenlaufen De Trigger soll reagieren auf ALL SERVER FOR CREATE_DATABASE. Das bedeutet, ALL SERVER auf der gesamte Datenbank für CREATE_DATABASE. Das wiederum bedeutet, dass ich hier unter Server Objects Triggers den Trigger finden sollte, den ich gerade angelegt habe. Was macht dieser Trigger? Er gibt im Wesentlichen neben einer Nachricht die Informationen aus, die über die EVENTDATA-Funktion, geliefert werden. Das wiederum ist nicht anderes als XML, das heißt, ich kann hier mit einer X-Query entsprechend auf einzelnen Informationen zugreifen und wenn ich den jetzt zum Beispiel eine Datenbank anlege, ich mache das mal mit dem Block, dann sehen Sie, dass die Rückgabe zum einen genau das XML ist, gucken wir uns gleich an, zum anderen aber genau aus SERVER_INSTANCE/TSQL/Command_CommandText und das erste Element genau dieses CREATE DATABASE extrahiert hat und das ist genau das, was im Kern ausgeführt wurde um die Datenbank anzulegen. Wenn ich mir jetzt das XML angucke, sehe ich alle Informationen, die ich bekomme, also ich sehe welche EventType das ist, wann das geposstet wurde, die Prozess-ID, ServerName, LoginName, Datenbank. Ich sehe auch welches Command das war, die einzelne SetOptions, die gesetzt wurden, kann ich hier erkennen und den CommandText selber und das ist genau, was der X-Pass ausdrückt extra hier an der Stelle. Dann kann ich ein bisschen weiter runterschrollen. Ich kann zum Beispiel innerhalb dieser Datenbank, ich bin jetzt in der dotnetconsulting_Triggers-Datenbank, kann ich einen Trigger anlegen, der entsprechend für CRETE_TABLE zuständig ist. Ich wurde den erst löschen, existiert noch nicht, das bräuchte also nicht machen, aber CREATE_TRIGGER, dann der Name des Triggers, ON DATABASE, im Gegensatz zu dem, was wir gerade hatten, da hießt es ja ON ALL SERVER. Dann kann ich im Wesentlich sagen FOR in dem Fall CREATE_TABLE. Ich mache eigentlich genau das Gleiche, aber ich gebe einen Text aus, EVENTDATA, und auch das SQL-Command. Wenn ich jetzt den Trigger suche, muss ich entsprechend in die Datenbank gehen und kann dann unter Programmability, unter Database Triggers hier an der Stelle findig werden und wenn Sie mal schauen, das Symbol sieht ulkig aus, es ist ein Fehler von SQL Server Management Studio, eigentlich sollte es ein Blitz sein. Jetzt kann ich hingehen und zum Beispiel eine Tabelle anlegen. Sie sehen, selbst wenn ich entsprechend eine Tabelle erzeuge, bekomme ich genau dieses Statement, im Wesentlich die gleiche Information wie gerade, hier dann den CREATE_TABLE. Dann kann ich Server auf Serveebene deaktivieren. Und zwar entweder einzelne. Eine Möglichkeit hätte ich auch hier über das Kontextmenü. Das heißt, wenn ich jetzt hier aktualisiere, bietet er mir nur noch Enable an. Ich kann alle Trigger deaktivieren, DISABLE TRIGGER ALL ON ALL SERVER. Ich kann das Spielchen auf Datenbanebene machen. Das heißt, hier einen speziellen Trigger auf Datenbankebene deaktivieren oder alle Trigger auf Datenbankebene aktualisieren. Und ich kann das Ganze rückgängig machen, und ich kann sie wieder aktivieren, entweder einzeln oder alle und das auf Server- oder auf Datenbankebene. Ich kann Trigger natürlich auch ändern, indem ich einfach sage, ALTER TRIGGER, dann benenne ich den Namen des Triggers, dann sprechen, worauf er reagieren soll und sein Text an der Stelle. Und ich kann Trigger, last but not least, natürlich löschen, entweder auf ALL SERVER oder ON DATABASE. Damit habe ich die Möglichkeit diesem Spuk auch ein Ende zu bereiten. Damit sollte hier der Trigger weg sein. Und der Trigger hier sollte ebenfalls weg sein, was auch der Fall ist. Wenn ich das ein bisschen diferenzierter machen möchte, muss ich hier auf sys_server_triggers und in der Datenbank auf sys_triggers zugreifen um zu gucken, ob es hier einen Treffer gibt. Das sind nämlich die beiden Tabellen, die mir sagen, welche Trigger hier auf Datenbankebene und hier oben auf Serverebene existieren. Sonst wurden die DROP TRIGGER-Anweisungen hier unten auf einen Fehler laufen. Wer sich nun fragt, welche Trigger- Möglichkeiten ich überhaupt habe, dem zeige ich hier dieses zweite Skript. Wie gesagt, hier können Sie alle Trigger der aktuellen und alle Trigger des Servers auflisten, oder mit dieser Abfrage bekomme ich dieses Ergebnis. Sie sehen, DDL-Trigger können durchaus sehr umfangreich sein, das Ganze basiert auf einer Abfrage, auf sys.trigger_event_types, und zeigt mir also an, welche Trigger entsprechend zur Verfügung stehen. Und Sie sehen, das ist bewusst eine Hierarchie. Das heißt, ich kann zum Beispiel sagen, ich möchte auf DDL_EVENTS reagieren und das wurde alle DDL_EVENTS, die sich struktureller drunter befinden entsprechend einschließen. Oder ich kann zum Beispiel hingehen und kann sagen, ich möchte auf DDL_ASSEMBLY_EVENTS und das wurde ALTER CREATE und DROP einschließen. So kann ich relativ einfach durch den Katalog durchgehen. Ich kann erkennen, welche DDL_EVENTS existieren und welche ich nutzen kann, Sie sehen schon, es sind 264 insgesamt und ich werde nicht über jeden einzelnen reden aber zum Beispiel hier DDL_VIEW_EVENTS mit ALTER, CREATE, DROP. DDL_TRIGGER_EVENTS mit ALTER, CREATE, DROP und so weiter. Irgendwo müssen sich auch die für Tabellen befinden, hier, DDL_TABLE_EVENTS. Sie können sehr genau steuern, zu welchem DDL-Statement Ihrer Trigger feiern soll und können damit quasi wirklich sehr genau darauf reagieren, was wirklich auf dem Server oder in der Datenbank passiert. Die Abfrage stand auch nicht in Gänze von mir, sondern ist von der MSDN entliehen. Insofern hier einen kleinen Hinweis auf den Link, wo Sie entsprechend diese Information finden können.

SQL Server 2016: Triggers, Stored Procedures und Funktionen

Nutzen Sie in SQL Server Trigger, gespeicherte Prozeduren, Late Binding, Fehlerbehandlung sowie Scalar- und Tabellenwertfunktionen.

3 Std. 12 min (44 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:08.08.2016

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!