Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. Databases

Einführung in MySQL-Trigger

by
Length:LongLanguages:

German (Deutsch) translation by Tatsiana Bochkareva (you can also view the original English article)

Wahrscheinlich wissen Sie, was ein Datenbank-Trigger ist, zumindest konzeptionell. Die Chancen stehen sogar noch größer, dass Sie wissen, dass MySQL Trigger unterstützt und diese seit geraumer Zeit unterstützt. Selbst mit diesem Wissen würde ich vermuten, dass viele von Ihnen Trigger mit MySQL nicht ausnutzen. Sie gehören zu den Dingen, die unbedingt in Ihrer Entwicklungs-Toolbox enthalten sein sollten, da sie die Art und Weise, wie Sie Ihre Daten betrachten, wirklich verändern können.


Einführung: Was ist ein Trigger?

"Da Anwendungen jedoch immer komplizierter werden, wird die Benutzerfreundlichkeit unserer internen Entwicklung umso größer, je weiter wir die Ebenen einer Anwendung abstrahieren können, um das zu handhaben, was sie sollten."

Für Uneingeweihte ist ein Trigger eine Regel, die Sie auf eine Tabelle setzen, die im Grunde sagt, wann immer Sie etwas in dieser Tabelle LÖSCHEN/DELETE, AKTUALISIEREN/UPDATE oder EINFÜGEN/INSERT, auch etwas anderes tun. Beispielsweise möchten wir möglicherweise eine Änderung protokollieren, aber anstatt zwei separate Abfragen zu schreiben, eine für die Änderung und eine für das Protokoll, können wir stattdessen einen Trigger schreiben, der besagt: "Wenn diese Zeile aktualisiert wird, erstellen Sie eine neue Zeile in einer anderen Tabelle, um mir mitzuteilen, dass das Update durchgeführt wurde". Dies erhöht die anfängliche Abfrage um einen kleinen Aufwand. Da jedoch nicht zwei Pakete in Ihre Datenbank gelangen, um zwei separate Aufgaben auszuführen, ergibt sich (theoretisch jedenfalls) ein allgemeiner Leistungsgewinn.

Trigger wurden in Version 5.0.2 in MySQL eingeführt. Die Syntax für einen Trigger ist beim ersten Erröten etwas fremd. MySQL verwendet den ANSI SQL:2003-Standard für Prozeduren und andere Funktionen. Wenn Sie mit einer Programmiersprache im Allgemeinen vertraut sind, ist es nicht so schwer zu verstehen. Die Spezifikation ist nicht frei verfügbar, daher werde ich mein Bestes tun, um einfache Strukturen zu verwenden und zu erklären, was innerhalb des Triggers geschieht. Sie haben es mit denselben Logikstrukturen zu tun, die jede Programmiersprache bietet.

Wie oben erwähnt, werden Trigger prozedural für UPDATE-, DELETE- und INSERT-Ereignisse ausgeführt. Was ich nicht erwähnt habe ist, dass sie entweder vor oder nach dem definierten Ereignis ausgeführt werden können. Daher könnten Sie einen Auslöser haben, der vor einem DELETE oder nach einem DELETE usw. ausgelöst wird. Dies bedeutet, dass Sie einen Auslöser haben könnten, der vor einem INSERT ausgelöst wird, und einen separaten, der nach einem INSERT ausgelöst wird, was sehr mächtig sein kann.

Ich werde drei Verwendungszwecke betrachten, die Sie Ihrer Toolbox hinzufügen könnten. Es gibt verschiedene Verwendungszwecke, auf die ich nicht näher eingehen werde, da ich der Meinung bin, dass es bessere Methoden gibt, um die gleichen Ergebnisse zu erzielen, oder sie verdienen ein eigenes Tutorial. Jede dieser Anwendungen, die ich untersuche, hat ein Gegenstück in Ihrer serverseitigen Logikschicht und ist kein neues Konzept. Da Anwendungen jedoch immer komplizierter werden, wird unsere interne Entwicklungsbenutzbarkeit umso besser, je weiter wir die Ebenen einer Anwendung abstrahieren können, um das zu handhaben, was sie sollten.


Anfänge: Meine Tabellenstruktur, Werkzeuge und Notizen

Ich arbeite mit einem mythischen Wagensystem, mit Artikeln, die Preise haben. Ich habe versucht, die Datenstruktur nur zur Veranschaulichung so einfach wie möglich zu halten. Ich benenne Spalten und Tabellen zum Zwecke des Verständnisses und nicht für Produktionszwecke. Ich benutze auch TIMESTAMPS anstatt anderer Alternativen zur Vereinfachung. Für diejenigen, die die Heimversion des heutigen Spiels spielen, verwende ich die Tabellennamen von carts, cart_items, cart_log, items, items_cost.

Bitte beachten Sie, dass ich in diesem Tutorial sehr einfache Abfragen verwenden werde, um meine Punkte auszudrücken. Ich binde keine Variable, da ich keine Benutzereingaben verwende. Ich möchte die Abfragen so einfach wie möglich lesen, verwende dieses Tutorial jedoch nur für praktische Triggeranwendungen. Ich weiß, dass es ein oder zwei Kommentare dazu geben könnte. Betrachten Sie dies als meinen Haftungsausschluss.

Ich verwende den Partikelbaum PHP Quick Profiler, um die Ausführungszeiten anzuzeigen. Ich verwende auch die im Tool bereitgestellte Datenbankabstraktionsschicht nur zu meinem eigenen Vorteil. Es ist ein nettes Tool und bietet viel mehr als nur die Bereitstellung von SQL-Ausführungszeiten.

Ich verwende Chive auch, um die DB-Effekte zu veranschaulichen und meine Trigger zu erstellen. Chive ist nur MySQL 5+ und PHPMyAdmin sehr ähnlich. Es ist hübscher, aber im Moment auch viel fehlerhafter. Ich benutze Chive, einfach weil es gute Screenshots darüber gibt, was mit den Abfragen passiert.

Noch eine kurze Anmerkung. Möglicherweise müssen Sie das Trennzeichen für MySQL ändern, während Sie einen Trigger erstellen. Das natürliche Trennzeichen für MySQL ist; Da wir dieses Trennzeichen jedoch für unsere hinzugefügten Abfragen verwenden, müssen Sie das Trennzeichen möglicherweise explizit umbenennen, wenn Sie diese über die Befehlszeile erstellen. Ich habe mich entschieden, dies nicht anzuzeigen, da bei Verwendung von Chive das Trennzeichen nicht geändert werden muss.

Um ein Trennzeichen zu ändern, tun Sie dies einfach vor Ihrem Triggerbefehl:

Und das nach Ihrem Triggerbefehl:


Der einfache Auslöser: Datenintegrität

Wenn Sie Ihre Datenbankstruktur auch nur geringfügig normalisieren, sind Sie wahrscheinlich auf eine Zeit gestoßen, in der Sie die Hauptdatenquelle gelöscht haben, aber immer noch Fragmente in Ihrem Datenstrom herumlaufen. Beispielsweise haben Sie möglicherweise eine cart_id, auf die in zwei oder drei Tabellen ohne Fremdschlüssel verwiesen wird, insbesondere da Fremdschlüssel von der MyISAM-Engine nicht unterstützt werden.

Was Sie wahrscheinlich in der Vergangenheit getan haben, ist ungefähr so (zur Veranschaulichung vereinfacht):

Abhängig davon, wie gut Sie sich organisieren, verfügen Sie möglicherweise über eine einzelne API oder Methode, mit der Sie Ihre Einkaufswagen löschen können. In diesem Fall haben Sie Ihre Logik isoliert, um diese beiden Abfragen auszuführen. Wenn dies nicht der Fall ist, müssen Sie immer daran denken, Ihre Warenkorbartikel zu löschen, wenn Sie einen bestimmten Warenkorb löschen. Nicht schwierig, aber wenn Sie es vergessen, verlieren Sie Ihre Datenintegrität.

Geben Sie unseren Auslöser ein. Ich werde einen sehr einfachen Auslöser erstellen, sodass mein Auslöser jedes Mal ausgelöst wird, wenn ich einen Warenkorb lösche, um alle Warenkorbartikel mit derselben cart_id zu löschen:

Sehr einfache Syntax wie oben gesagt. Lassen Sie uns jede Zeile durchgehen.

In meiner ersten Zeile steht "CREATE TRIGGER` tutorial`.`before_delete_carts` ". Ich fordere MySQL auf, einen Trigger für das Datenbank-Tutorial zu erstellen, der den Namen "before_delete_carts" trägt. Ich neige dazu, meine Trigger mit der Formel "When_How_Table" zu benennen. Das funktioniert bei mir, aber es gibt viele andere Möglichkeiten, dies zu tun.

Meine zweite Zeile teilt MySQL die Definition dieses Triggers mit, "BEVOR DELETE ON` trigger_carts` FOR EACH ROW ". Ich sage MySQL, dass Sie vor dem Löschen in dieser Tabelle für jede Zeile etwas tun müssen. Das wird als nächstes in unserem BEGIN und END erklärt. "DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;" Ich sage MySQL, bevor Sie aus trigger_carts löschen, die OLD.cart_id nehmen und auch aus trigger_cart_items löschen. Die OLD/ALTE Syntax ist die definierte Variable. Wir werden dies im nächsten Abschnitt diskutieren, in dem wir OLD und NEW kombinieren werden.

Es gibt wirklich nichts, um diesen Auslöser zu erstellen. Der Vorteil besteht darin, dass Sie Ihre Datenintegritätslogik auf Ihre Datenschicht verschieben, was ich der Fall sein könnte, wenn sie dazu gehört. Es gibt noch einen weiteren kleinen Vorteil, nämlich den unten gezeigten leichten Leistungsgewinn.

Zwei Abfragen:

Delete with No Trigger

Eine Abfrage mit einem Trigger:

Delete with a Trigger

Wie Sie sehen, gibt es einen leichten Leistungsgewinn, der zu erwarten ist. Meine Datenbank, die ich verwende, befindet sich auf localhost mit meinem Server. Wenn ich jedoch einen separaten DB-Server verwendet hätte, wäre mein Leistungsgewinn aufgrund der Umlaufzeit zwischen den beiden Servern etwas größer. Mein Triggerlöschvorgang hat eine etwas höhere Löschzeit, es gibt jedoch nur eine Abfrage, sodass sich die Gesamtzeit verkürzt. Multiplizieren Sie dies mit dem gesamten Code, den Sie zur Wahrung Ihrer Datenintegrität verwenden, und der Leistungsgewinn wird zumindest bescheiden.

Ein Hinweis zur Leistung: Wenn der Trigger zum ersten Mal ausgeführt wird, ist er möglicherweise viel langsamer als die nachfolgenden Zeiten. Ich verwende Trigger nicht unbedingt für den Leistungsgewinn, sondern um meine Datenlogik auf meine Datenschicht zu verschieben, genau wie Sie Ihre Präsentation von Ihrem Markup auf Ihre Präsentationsschicht verschieben möchten, die auch als CSS bezeichnet wird.


Der ziemlich einfache Auslöser: Protokollierung und Überwachung

Das nächste Beispiel, das wir uns ansehen werden, befasst sich mit der Protokollierung. Angenommen, ich möchte jeden Artikel verfolgen, der in einen Warenkorb gelegt wird. Vielleicht möchte ich die Kaufrate meiner Warenkorbartikel überwachen. Vielleicht möchte ich nur eine Kopie jedes Artikels in einen Warenkorb legen, der nicht unbedingt verkauft wird, nur um einen Einblick in die Gedanken meiner Kunden zu erhalten. Möglicherweise haben Sie Ihre Warenkorbartikel als MEMORY-Tabelle erstellt und möchten alle Artikel in einer InnoDB-Tabelle protokollieren. Was auch immer der Grund sein mag, schauen wir uns einen INSERT-Trigger an, der einige gute Möglichkeiten für die Protokollierung oder Prüfung unserer Daten eröffnet.

Vor dem Auslösen haben wir wahrscheinlich so etwas gemacht (zur Veranschaulichung wieder vereinfacht):

Create with No Trigger

Jetzt können wir einen sehr einfachen Auslöser für diesen Protokollierungsprozess erstellen:

Lassen Sie uns das noch einmal durchgehen, damit klar ist, was dieser Trigger tut. Zuerst beginnen wir mit der Zeile "CREATE TRIGGER` after_insert_cart_items`". Ich fordere MySQL erneut auf, einen Trigger mit dem Namen "after_insert_cart_items" zu erstellen. Der Name könnte "Foo" oder "BullWinkle" sein oder wie auch immer Sie ihn nennen möchten, aber ich bevorzuge es, meine Triggernamen zu veranschaulichen. Als nächstes sehen wir "AFTER INSERT ON` trigger_cart_items` FOR EACH ROW". Wiederum heißt das, nachdem wir etwas in trigger_cart_items eingefügt haben, führen Sie für jede eingefügte Zeile aus, was zwischen meinem BEGIN und END liegt.

Schließlich haben wir nur "INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);" Dies ist eine Standardabfrage mit Ausnahme meiner beiden Werte. Ich verwende den NEUEN Wert, der in die Tabelle cart_items eingefügt wird.

Und wir haben unsere Anfragen mit dem subtilen Leistungsgewinn halbiert:

Create with a Trigger

Und nur um zu überprüfen, ob unser Trigger funktioniert, sehe ich die Werte in meiner Tabelle:

Proof of my Trigger

Dies ist wiederum relativ einfach, aber wir arbeiten mit einigen Werten, die die Komplexität nur geringfügig erhöhen können. Schauen wir uns etwas schwieriger an.


Der härtere Auslöser: Geschäftslogik

An dieser Stelle können wir den alten Weg mehrerer Abfragen mit einer einzigen Abfrage überspringen. Ich kann mir vorstellen, dass es nur ein bisschen langweilig wird, die Leistung von Abfragen weiter zu messen. Kommen wir stattdessen zu einigen weiteren Beispielen für Trigger.

In der Geschäftslogik schleichen sich immer die Fehler ein. Unabhängig davon, wie vorsichtig oder organisiert wir sind, rutscht immer etwas durch die Ritzen. Trigger auf UPDATE mildern dies nur ein wenig. Wir haben eine gewisse Macht in einem Trigger, um den OLD Wert zu bewerten und den NEW Wert basierend auf der Bewertung festzulegen. Nehmen wir zum Beispiel an, wir möchten, dass unser Artikelpreis immer 30% Aufschlag auf die Artikelkosten beträgt. Es ist dann natürlich sinnvoll, dass wir, wenn wir unsere Kosten aktualisieren, auch unseren Preis aktualisieren müssen. Lassen Sie uns das mit einem Auslöser behandeln.

Wir aktualisieren die Artikeltabelle mit einem Preis, der auf den NEW.cost-Zeiten 1.3 basiert. Ich habe einen Preis von 50 US-Dollar eingegeben, daher sollte mein neuer Preis 65 US-Dollar betragen.

Update Trigger Price Change

Sicher genug, dieser Auslöser funktionierte auch.

Wir müssen uns ein etwas fortgeschritteneres Beispiel ansehen. Wir haben bereits die Regel, den Preis eines Artikels basierend auf seinen Kosten zu ändern. Nehmen wir an, wir wollen unsere Kosten ein wenig senken. Wenn die Kosten weniger als 50 US-Dollar betragen, betragen unsere Kosten tatsächlich 50 US-Dollar. Wenn die Kosten über 50 US-Dollar, aber unter 100 US-Dollar liegen, betragen unsere Kosten 100 US-Dollar. Während mein Beispiel wahrscheinlich nicht mit einer echten Geschäftsregel übereinstimmt, passen wir die Kosten täglich an Faktoren an. Ich versuche nur, das Beispiel leicht verständlich zu halten.

Um dies zu tun, werden wir wieder mit einem UPDATE arbeiten, aber dieses Mal werden wir es auslösen, bevor wir unsere Abfrage ausführen. Wir werden auch mit einer IF-Anweisung arbeiten, die uns zur Verfügung steht.

Hier ist der neue Auslöser:

Was wir jetzt tun, ist nicht, eine Abfrage aufzurufen, sondern nur den Wert zu überschreiben. Ich sage, wenn die Kosten weniger als 50 Dollar betragen, dann machen Sie es einfach 50 Dollar. Wenn die Kosten zwischen 50 und 100 US-Dollar liegen, machen Sie es 100 US-Dollar. Wenn es darüber liegt, lasse ich es einfach gleich bleiben. Meine Syntax hier ist nicht so fremd von irgendeiner anderen serverseitigen Sprache. Wir müssen unsere IF-Klausel mit einem END IF schließen. aber anders als das ist es wirklich nicht schwierig.

Um zu überprüfen, ob unser Auslöser funktioniert, habe ich einen Wert von 30 US-Dollar für die Kosten eingegeben, der 50 US-Dollar betragen sollte:

Cost Is 50

Wenn ich einen Preis von 85 US-Dollar eingebe, ist hier der Wert:

Cost Is 100

Und um zu überprüfen, ob mein AFTER UPDATE-Trigger noch funktioniert, sollte mein Preis jetzt 130 US-Dollar betragen:

Price is 130

Das leben ist gut.


Abschluss

Ich habe nur die Spitze des Eisbergs mit Triggern und MySQL berührt. Obwohl es unzählige Verwendungszwecke für Trigger gibt, bin ich in der Vergangenheit ohne sie gut zurechtgekommen, indem ich mich mit meinen Daten in meiner Logikschicht befasst habe. Die Möglichkeit, meinen Daten in der Datenschicht Regeln hinzuzufügen, ist jedoch nur sinnvoll. Wenn Sie die bescheidenen Leistungsverbesserungen hinzufügen, ist der Vorteil sogar noch größer.

Wir müssen uns jetzt mit komplizierten Webanwendungen mit hohem Datenverkehr befassen. Wenn Sie einen Auslöser auf einer einzelnen Seite verwenden, ist dies möglicherweise nicht die beste Verwendung von Zeit und Energie. Ein Auslöser für eine komplexe Webanwendung kann den Unterschied ausmachen. Ich hoffe, Ihnen haben die Beispiele gefallen, und bitte lassen Sie mich wissen, was weitere Erklärungen benötigt.

Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.