So verwenden Sie MySQL-Trigger

Einführung

MySQL-Trigger wenden Einschränkungen auf Tabellen an, wenn Tabellenzeilen hinzugefügt, aktualisiert oder entfernt werden.

Spalten in MySQL wenden eine geringe Menge an Wertbeschränkungen an. Beispiel:Festlegen eines Spaltendatentyps als tiny int und nicht null erfordert eine kleine Zahl Werteingabe. Dennoch sind weitere Einschränkungen erforderlich, um die Integrität der Daten zu wahren.

Dieses Tutorial zeigt Ihnen, wie Sie MySQL-Trigger verwenden, und bietet Beispiele für jeden Triggertyp.

Voraussetzungen

• Ein System, auf dem MySQL auf einem Datenbankserver ausgeführt wird
• MySQL-Benutzerkonto mit Root-Rechten
• Kenntnisse grundlegender MySQL-Befehle (siehe unseren herunterladbaren Spickzettel für MySQL-Befehle)

Was ist ein Trigger in MySQL?

Ein Trigger ist ein benanntes MySQL-Objekt, das aktiviert wird, wenn ein Ereignis in einer Tabelle auftritt. Trigger sind eine bestimmte Art von gespeicherter Prozedur, die einer bestimmten Tabelle zugeordnet ist.

Trigger ermöglichen den Zugriff auf Werte aus der Tabelle zu Vergleichszwecken mit NEW und OLD . Die Verfügbarkeit der Modifikatoren hängt vom verwendeten Trigger-Ereignis ab:

Ereignis auslösen	ALT	NEU
EINFÜGEN	Nein	Ja
AKTUALISIEREN	Ja	Ja
LÖSCHEN	Ja	Nein

Beim Überprüfen oder Ändern eines Werts beim Versuch, Daten einzufügen, wird NEW.<column name> Modifikator verfügbar. Dies liegt daran, dass eine Tabelle mit neuen Inhalten aktualisiert wird. Im Gegensatz dazu ein OLD.<column name> value existiert für eine Insert-Anweisung nicht, da vorher keine Informationen an seiner Stelle vorhanden sind.

Beim Aktualisieren einer Tabellenzeile sind beide Modifikatoren verfügbar. Es gibt OLD.<colum name> Daten, die wir auf NEW.<column name> aktualisieren möchten Daten.

Schließlich wird beim Entfernen einer Datenzeile der OLD.<column name> modifier greift auf den entfernten Wert zu. Der NEW.<column name> existiert nicht, weil nichts den alten Wert beim Entfernen ersetzt.

MySQL-Trigger-Beispiel

Als Beispiel für einen angewendeten Trigger das Einfügen neuer Werte in die Tabelle person ergibt ein anderes Ergebnis als die ursprüngliche Eingabe:

Beachten Sie, dass die eingefügten Namen ursprünglich in Kleinbuchstaben geschrieben waren. Bei der Auswahl der Tabelle wird der erste Buchstabe groß geschrieben. Obwohl es keinen Hinweis auf etwas anderes als eine normale Einfügeanweisung gibt, wurde der Trigger vor der Einfügeanweisung ausgelöst um den ersten Buchstaben des Namens groß zu schreiben.

Verwendung von MySQL-Triggern

Jeder Trigger, der einer Tabelle zugeordnet ist, hat einen eindeutigen Namen und eine Funktion, die auf zwei Faktoren basiert:

1. Zeit . BEFORE oder AFTER ein bestimmtes Zeilenereignis.

2. Ereignis . INSERT , UPDATE oder DELETE .

MySQL-Trigger feuern abhängig von der Aktivierungszeit und dem Ereignis für insgesamt sechs eindeutige Triggerkombinationen. Die Before-Anweisungen helfen dabei, Daten zu prüfen und Änderungen vorzunehmen, bevor Sie Zusagen machen, während die After-Anweisungen die Daten zuerst festschreiben und dann Anweisungen ausführen.

Die Ausführung einer Reihe von Aktionen erfolgt automatisch und wirkt sich auf alle eingefügten, gelöschten oder aktualisierten Zeilen in der Anweisung aus.

Trigger erstellen

Verwenden Sie den CREATE TRIGGER Anweisungssyntax zum Erstellen eines neuen Triggers:

CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
<trigger body>;

Am besten benennen Sie den Auslöser mit den folgenden Informationen:

<trigger time>_<table name>_<trigger event>

Zum Beispiel, wenn ein Trigger vor dem Einfügen ausgelöst wird auf einer Tabelle namens employee , ist die beste Konvention, den Trigger aufzurufen:

before_employee_insert

Alternativ ist es üblich, das folgende Format zu verwenden:

<table name>_<first letter of trigger time><first letter of trigger name>

Das vor dem Einfügen Auslösername für die Tabelle employee sieht so aus:

employee_bi

Der Trigger wird zu einem bestimmten Zeitpunkt eines Ereignisses für eine Tabelle ausgeführt, die durch

definiert ist für jede Zeile, die von der Funktion betroffen ist.

Auslöser löschen

Verwenden Sie zum Löschen eines Triggers den DROP TRIGGER Aussage:

DROP TRIGGER <trigger name>;

Alternativ verwenden Sie:

DROP TRIGGER IF EXISTS <trigger name>;

Die Fehlermeldung wird nicht angezeigt, da es keinen Auslöser gibt, daher wird keine Warnung gedruckt.

Beispieldatenbank erstellen

Erstellen Sie eine Datenbank für die Trigger-Beispielcodes mit der folgenden Struktur:

1. Erstellen Sie eine Tabelle namens person mit Namen und Alter für Spalten.

CREATE TABLE person (name varchar(45), age int);

Beispieldaten in die Tabelle einfügen:

INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);

Wählen Sie die Tabelle aus, um das Ergebnis anzuzeigen:

SELECT * FROM person;

2. Erstellen Sie eine Tabelle namens average_age mit einer Spalte namens Durchschnitt :

CREATE TABLE average_age (average double);

Tragen Sie den Altersdurchschnittswert in die Tabelle ein:

INSERT INTO average_age SELECT AVG(age) FROM person;

Wählen Sie die Tabelle aus, um das Ergebnis anzuzeigen:

SELECT * FROM average_age;

3. Erstellen Sie eine Tabelle namens person_archive mit Namen , Alter , und Zeit Spalten:

CREATE TABLE person_archive (
name varchar(45),
age int,
time timestamp DEFAULT NOW());

Erstellen Sie einen BEFORE INSERT-Trigger

So erstellen Sie einen BEFORE INSERT auslösen, verwenden Sie:

CREATE TRIGGER <trigger name> BEFORE INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

Der BEFORE INSERT Trigger gibt Kontrolle über die Datenänderung, bevor er in eine Datenbanktabelle übernommen wird. Namen aus Gründen der Konsistenz großschreiben, die Länge einer Eingabe überprüfen oder fehlerhafte Eingaben mit BEFORE INSERT abfangen Trigger bietet außerdem Wertbeschränkungen, bevor neue Daten eingegeben werden.

Beispiel für BEFORE INSERT-Trigger

Erstellen Sie ein BEFORE INSERT auslösen, um den Alterswert zu prüfen, bevor Daten in die Person eingefügt werden Tabelle:

delimiter //
CREATE TRIGGER person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

Das Einfügen von Daten aktiviert den Trigger und überprüft den Wert von age bevor Sie die Informationen übergeben:

INSERT INTO person VALUES ('John', 14);

Die Konsole zeigt die beschreibende Fehlermeldung an. Die Daten werden aufgrund der fehlgeschlagenen Triggerprüfung nicht in die Tabelle eingefügt.

Erstellen Sie einen AFTER INSERT-Trigger

Erstellen Sie ein AFTER INSERT auslösen mit:

CREATE TRIGGER <trigger name> AFTER INSERT
ON <table name>
FOR EACH ROW
<trigger body>;

Der AFTER INSERT Trigger ist nützlich, wenn die eingegebene Zeile einen Wert generiert, der zum Aktualisieren einer anderen Tabelle benötigt wird.

AFTER INSERT-Trigger-Beispiel

Einfügen einer neuen Zeile in die Person aktualisiert nicht automatisch den Durchschnitt im average_age Tisch. Erstellen Sie ein AFTER INSERT Trigger auf die Person Tabelle, um das average_age zu aktualisieren Tabelle nach dem Einfügen:

delimiter //
CREATE TRIGGER person_ai AFTER INSERT
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

Einfügen einer neuen Zeile in die Person Tabelle löst den Trigger aus:

INSERT INTO person VALUES ('John', 19);

Die Daten werden erfolgreich an die Person übertragen Tabelle und aktualisiert das average_age Tabelle mit dem korrekten Mittelwert.

Erstellen Sie einen BEFORE UPDATE-Trigger

Machen Sie ein BEFORE UPDATE auslösen mit:

CREATE TRIGGER <trigger name> BEFORE UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

Der BEFORE UPDATE Trigger gehören zusammen mit BEFORE INSERT löst aus. Wenn vor dem Einfügen von Daten Einschränkungen bestehen, sollten die Einschränkungen auch vor dem Aktualisieren vorhanden sein.

BEFORE UPDATE Trigger-Beispiel

Wenn es eine Altersbeschränkung für die Person gibt Tabelle vor dem Einfügen von Daten, die Altersbeschränkung sollte auch vorhanden sein, bevor Informationen aktualisiert werden. Ohne BEFORE UPDATE Auslöser ist der Altersprüfungsauslöser leicht zu vermeiden. Nichts schränkt die Bearbeitung auf einen fehlerhaften Wert ein.

Fügen Sie ein BEFORE UPDATE hinzu Auslöser für die Person Tabelle mit demselben Körper wie BEFORE INSERT Auslöser:

delimiter //
CREATE TRIGGER person_bu BEFORE UPDATE
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //
delimiter ;

Das Aktualisieren eines bestehenden Werts aktiviert die Triggerprüfung:

UPDATE person SET age = 17 WHERE name = 'John';

Aktualisieren des Alters auf einen Wert kleiner als 18 zeigt die Fehlermeldung an und die Informationen werden nicht aktualisiert.

Erstellen Sie einen AFTER UPDATE-Trigger

Verwenden Sie den folgenden Codeblock, um ein AFTER UPDATE zu erstellen Auslöser:

CREATE TRIGGER <trigger name> AFTER UPDATE
ON <table name>
FOR EACH ROW
<trigger body>;

Die AFTER UPDATE Trigger hilft, festgeschriebene Änderungen an Daten nachzuverfolgen. Meistens treten alle Änderungen nach dem Einfügen von Informationen auch nach dem Aktualisieren von Daten auf.

AFTER UPDATE Trigger-Beispiel

Alle erfolgreichen Aktualisierungen des Zeitalters Daten in der Tabelle Person sollte auch den in average_age berechneten Zwischendurchschnittswert aktualisieren Tabelle.

Erstellen Sie ein AFTER UPDATE Trigger zum Aktualisieren des average_age Tabelle nach dem Aktualisieren einer Zeile in der person Tabelle:

delimiter //
CREATE TRIGGER person_au AFTER UPDATE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;

Das Aktualisieren vorhandener Daten ändert den Wert in person Tabelle:

UPDATE person SET age = 21 WHERE name = 'John';

Aktualisieren der Tabelle person aktualisiert auch den Durchschnitt im average_age Tabelle.

Erstellen Sie einen BEFORE DELETE-Trigger

So erstellen Sie einen BEFORE DELETE auslösen, verwenden Sie:

CREATE TRIGGER <trigger name> BEFORE DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

Der BEFORE DELETE Trigger ist aus Sicherheitsgründen unerlässlich. Wenn an eine übergeordnete Tabelle untergeordnete Tabellen angehängt sind, hilft der Trigger dabei, das Löschen zu blockieren und verwaiste Tabellen zu verhindern. Der Trigger ermöglicht auch das Archivieren von Daten vor dem Löschen.

BEFORE DELETE Trigger-Beispiel

Archivieren Sie gelöschte Daten, indem Sie einen BEFORE DELETE erstellen Trigger auf den Tisch person und fügen Sie die Werte in das person_archive ein Tabelle:

delimiter //
CREATE TRIGGER person_bd BEFORE DELETE
ON person
FOR EACH ROW
INSERT INTO person_archive (name, age)
VALUES (OLD.name, OLD.age); //
delimiter ;

Löschen von Daten aus der Tabelle person archiviert die Daten im person_archive Tabelle vor dem Löschen:

DELETE FROM person WHERE name = 'John';

Den Wert wieder in die Person einfügen table hält das Protokoll der gelöschten Daten im person_archive Tabelle:

INSERT INTO person VALUES ('John', 21);

Der BEFORE DELETE Trigger ist nützlich, um alle Tabellenänderungsversuche zu protokollieren.

Erstellen Sie einen AFTER DELETE-Trigger

Erstellen Sie ein AFTER DELETE auslösen mit:

CREATE TRIGGER <trigger name> AFTER DELETE
ON <table name>
FOR EACH ROW
<trigger body>;

Der AFTER DELETE Trigger verwalten Informationsaktualisierungen, die erfordern, dass die Datenzeile verschwindet, bevor die Aktualisierungen vorgenommen werden.

AFTER DELETE Trigger-Beispiel

Erstellen Sie ein AFTER DELETE Trigger auf den Tisch person um das durchschnittliche_Alter zu aktualisieren Tabelle mit den neuen Informationen:

delimiter //
CREATE TRIGGER person_ad AFTER DELETE
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //
delimiter ;

Löschen eines Datensatzes aus der Tabelle person aktualisiert das durchschnittliche_Alter Tabelle mit dem neuen Durchschnitt:

Ohne AFTER DELETE auslösen, werden die Informationen nicht automatisch aktualisiert.

Mehrere Auslöser erstellen

MySQL unterstützt nicht das gleichzeitige Auslösen mehrerer Trigger. Das Hinzufügen mehrerer logischer Operationen zu demselben Trigger ist jedoch möglich. Verwenden Sie den BEGIN und END Trennzeichen zur Angabe des Trigger-Hauptteils:

CREATE TRIGGER <trigger name> <trigger time > <trigger event>
ON <table name>
FOR EACH ROW
BEGIN
<trigger body>;
END;

Stellen Sie sicher, dass Sie das Standardtrennzeichen ändern, bevor Sie einen Trigger mit mehreren Vorgängen erstellen.

Auslöser anzeigen

Alle Trigger in einer Datenbank auflisten mit:

SHOW triggers;

Die Ausgabe zeigt eine Liste aller Trigger, einschließlich des Namens und des Anweisungsinhalts:

Es werden auch andere Informationen angezeigt, wie z. B. die Erstellungszeit und der Benutzer, der den Trigger erstellt hat.

Was ist Istio? - Architektur, Funktionen, Vorteile und Herausforderungen

Grafana Prometheus-Dashboard-Tutorial

Cent OS

1. So erstellen Sie eine MySQL-Datenbank in Workbench

2. So verwenden Sie phpMyAdmin, um eine MySQL-Datenbank zu kopieren

3. So verwenden Sie Salt auf Centos 8

4. So reparieren Sie eine beschädigte MySQL-Datenbanktabelle

5. So installieren und verwenden Sie MySQL unter Ubuntu 20.04

So verwenden Sie MySQL-String-Funktionen

So installieren und verwenden Sie MySQL Workbench unter Ubuntu 18.04

So installieren Sie MySQL unter CentOS 8

So verwenden Sie das Webuzo-Panel !!!

So installieren und verwenden Sie SQLite unter Ubuntu 20.04

So verwenden Sie das MySQL-Optimierungstool