MySQL-Trigger sind auf Zeilenebene. Gemäß SQL-Standards können Trigger in zwei Typen unterteilt werden: 1. Trigger auf Zeilenebene, die einmal für jede geänderte Datenzeile aktiviert werden. Wenn eine Anweisung 100 Datenzeilen einfügt, wird der Trigger 100 Mal aufgerufen . Trigger auf Anweisungsebene Der Trigger wird einmal für jede Anweisung aktiviert. Eine Anweisung, die 100 Datenzeilen einfügt, ruft den Trigger nur einmal auf. MySQL unterstützt nur Trigger auf Zeilenebene, keine Trigger auf vorbereiteter Anweisungsebene.
Die Betriebsumgebung dieses Tutorials: Windows7-System, MySQL8-Version, Dell G3-Computer.
Übersicht über Trigger
MySQL-Trigger ist eine gespeicherte Prozedur, die einer bestimmten Tabelle zugeordnet ist und automatisch ausgeführt wird, wenn sich die Daten in der Tabelle ändern (hinzugefügt, aktualisiert, gelöscht). Diese Vorgänge, die Datenzeilen ändern, werden als Triggerereignisse bezeichnet. Beispielsweise können Anweisungen wie INSERT oder LOAD DATA, die Daten einfügen, Einfügetrigger aktivieren.
Gemäß SQL-Standards können Trigger in Trigger auf Zeilenebene und Trigger auf Anweisungsebene unterteilt werden.
Trigger auf Zeilenebene werden einmal für jede geänderte Datenzeile aktiviert. Wenn eine Anweisung 100 Datenzeilen einfügt, wird der Trigger 100 Mal aufgerufen.
Trigger auf Anweisungsebene werden jeweils einmal aktiviert Eine Anweisung, die 100 Datenzeilen einfügt, ruft den Trigger nur einmal auf.
MySQL unterstützt nur Trigger auf Zeilenebene, keine Trigger auf vorbereiteter Anweisungsebene.
Verschiedene Ereignisse können unterschiedliche Arten von Auslösern aktivieren. Der INSERT-Ereignisauslöser wird zum Einfügen von Datenoperationen verwendet, einschließlich INSERT-, LOAD DATA-, REPLACE-Anweisungen usw.; der UPDATE-Ereignisauslöser wird für Aktualisierungsvorgänge verwendet, wie z. B. UPDATE-Anweisungen; DELETE- und REPLACE-Anweisungen usw., DROP TABLE und die TRUNCATE TABLE-Anweisung aktivieren keine Löschtrigger.
Darüber hinaus können MySQL-Trigger vor oder nach dem auslösenden Ereignis ausgeführt werden, die BEFORE-Trigger bzw. AFTER-Trigger genannt werden. Diese beiden Trigger-Timings können mit verschiedenen Trigger-Ereignissen kombiniert werden, z. B. dem BEFORE INSERT-Trigger oder dem AFTER UPDATE-Trigger.
Zu den Vorteilen von MySQL-Triggern gehören:
Benutzeränderungsvorgänge an Daten in der Tabelle aufzeichnen und prüfen, um Prüffunktionen zu implementieren;
Integritätseinschränkungen implementieren, die komplexer sind als Prüfeinschränkungen, z. B. das Verbot von Nichteinschränkungen -Geschäftszeitdatenoperationen;
Bestimmte Geschäftslogik implementieren, z. B. die Anzahl der Personen in einer Abteilung automatisch aktualisieren, wenn Mitarbeiter hinzugefügt oder gelöscht werden;
Die Daten in der Tabelle synchron kopieren;
Obwohl Trigger leistungsstark sind, weisen sie auch einige Mängel auf:
Trigger erhöhen die Komplexität der Datenbankstruktur, und Trigger sind für die Anwendung unsichtbar und schwer zu debuggen;
Trigger Der Trigger muss mehr Ressourcen des Datenbankservers belegen und versuchen, die von der Datenbank bereitgestellten Nicht-Null-, Eindeutigkeits- und Prüfeinschränkungen usw. zu verwenden.
Trigger können keine Parameter empfangen und können nur basierend auf dem aktuellen Triggerobjekt arbeiten.
Die Verwendung von Triggern für spezielle Szenarien kann eine gewisse Bequemlichkeit bringen, aber verlassen Sie sich nicht zu sehr auf Trigger, um eine Verschlechterung der Datenbankleistung und Wartungsschwierigkeiten zu vermeiden. Als nächstes stellen wir die Verwaltungsvorgänge von Triggern vor.
Drei von MySQL unterstützte Trigger
Im tatsächlichen Einsatz unterstützt MySQL drei Trigger: INSERT-Trigger, UPDATE-Trigger und DELETE-Trigger.
1) INSERT-Trigger
Ein Trigger, der vor oder nach der Ausführung der INSERT-Anweisung reagiert.
Bei der Verwendung von INSERT-Triggern müssen Sie die folgenden Punkte beachten:
Im INSERT-Triggercode können Sie auf eine virtuelle Tabelle mit dem Namen NEW verweisen (ohne Berücksichtigung der Groß- und Kleinschreibung), um auf die eingefügte Zeile zuzugreifen.
Im BEFORE INSERT-Trigger kann auch der Wert in NEW aktualisiert werden, wodurch der eingefügte Wert geändert werden kann (sofern er über die entsprechenden Betriebsberechtigungen verfügt).
Für die Spalte AUTO_INCREMENT enthält NEW den Wert 0, bevor INSERT ausgeführt wird, und enthält den neuen automatisch generierten Wert, nachdem INSERT ausgeführt wird.
2) UPDATE-Trigger
Ein Trigger, der vor oder nach der Ausführung der UPDATE-Anweisung reagiert.
Bei der Verwendung von UPDATE-Triggern müssen Sie die folgenden Punkte beachten:
Im UPDATE-Triggercode können Sie auf eine virtuelle Tabelle mit dem Namen NEW verweisen (ohne Berücksichtigung der Groß-/Kleinschreibung), um auf den aktualisierten Wert zuzugreifen.
Im UPDATE-Triggercode kann auf eine virtuelle Tabelle mit dem Namen OLD (ohne Berücksichtigung der Groß-/Kleinschreibung) verwiesen werden, um auf den Wert zuzugreifen, bevor die UPDATE-Anweisung ausgeführt wird.
-
Im BEFORE UPDATE-Trigger kann auch der Wert in NEW aktualisiert werden, was eine Änderung des in der UPDATE-Anweisung zu verwendenden Werts ermöglicht (sofern Sie über die entsprechenden Betriebsberechtigungen verfügen).
Alle Werte in
OLD sind schreibgeschützt und können nicht aktualisiert werden.
Hinweis: Wenn der Trigger so konzipiert ist, dass er den Aktualisierungsvorgang der Tabelle selbst auslöst, können nur Trigger vom Typ BEFORE verwendet werden und Trigger vom Typ AFTER sind nicht zulässig.
3) DELETE-Trigger
Ein Trigger, der vor oder nach der Ausführung der DELETE-Anweisung reagiert.
Bei der Verwendung von DELETE-Triggern müssen Sie die folgenden Punkte beachten:
Im DELETE-Triggercode können Sie auf eine virtuelle Tabelle mit dem Namen OLD verweisen (ohne Berücksichtigung der Groß-/Kleinschreibung), um auf die gelöschten Zeilen zuzugreifen. Alle Werte in
OLD sind schreibgeschützt und können nicht aktualisiert werden.
Im Allgemeinen geht MySQL bei der Verwendung von Triggern mit Fehlern wie folgt um.
Wenn bei Transaktionstabellen der Trigger fehlschlägt und die gesamte Anweisung daher fehlschlägt, werden alle von der Anweisung vorgenommenen Änderungen zurückgesetzt. Bei nicht transaktionalen Tabellen kann ein solches Rollback nicht durchgeführt werden, selbst wenn die Anweisung fehlschlägt Änderungen, die vor dem Ausfall vorgenommen wurden, sind weiterhin gültig.
Wenn der BEFORE-Trigger fehlschlägt, führt MySQL den Vorgang nicht für die entsprechende Zeile aus.
Wenn während der Ausführung des BEFORE- oder AFTER-Triggerprogramms ein Fehler auftritt, führt dies dazu, dass die gesamte Anweisung, die das Triggerprogramm aufruft, fehlschlägt.
MySQL führt den AFTER-Trigger nur aus, wenn sowohl der BEFORE-Trigger als auch die Zeilenoperation erfolgreich ausgeführt wurden.
Trigger erstellen
MySQL verwendet die CREATE TRIGGRT-Anweisung, um einen Trigger zu erstellen. Die grundlegende Syntax lautet wie folgt:
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name FOR EACH ROW
trigger_body;Nach dem Login kopieren
Dabei ist Triggername der Name des Triggers die Auslösezeit des Triggers; INSERT, UPDATE und DELETE werden verwendet, um den Typ des Triggerereignisses zu definieren; Tabellenname ist der Name der mit dem Trigger verknüpften Tabelle, die keine temporäre Tabelle oder Ansicht sein kann; ein Trigger auf Zeilenebene; trigger_body ist die spezifische Anweisung, die vom Trigger ausgeführt wird.
Da beispielsweise die Gehälter der Mitarbeiter wichtige Informationen sind, muss die Historie der Gehaltsänderungen aufgezeichnet werden. Zuerst erstellen wir eine Audit-Tabelle:
CREATE TABLE emp_salary_audit (
audit_id INTEGER NOT NULL AUTO_INCREMENT
emp_id INTEGER NOT NULL,
old_salary NUMERIC(8,2) NULL,
new_salary NUMERIC(8,2) NULL,
change_date TIMESTAMP NOT NULL,
change_by VARCHAR(50) NOT NULL,
CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);Nach dem Login kopieren
Dabei ist audit_id der Primärschlüssel für die automatische Erhöhung; old_salary und new_salary werden zum Speichern des monatlichen Gehalts vor bzw. nach der Änderung verwendet; ;change_by zeichnet die Ausführungsänderung auf Der Bediener.
Dann erstellen Sie einen Trigger tri_audit_salary, um den Änderungsdatensatz des monatlichen Gehalts des Mitarbeiters aufzuzeichnen:
DELIMITER $$
CREATE TRIGGER tri_audit_salary
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
-- 当月薪改变时，记录审计数据
IF (NEW.salary <> OLD.salary) THEN
INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
END IF;
END$$
DELIMITER ;
Nach dem Login kopieren
Unter anderem wird DELIMITER verwendet, um das Endzeichen der SQL-Anweisung zu ändern, was wir bereits bei der Einführung der gespeicherten Prozedur kennen data Führen Sie dann den Trigger aus. UPDATE bedeutet, dass Datenänderungen nur für Aktualisierungsvorgänge aufgezeichnet werden. NEW und OLD im Triggerkörper sind spezielle Variablen in MySQL-Triggern, einschließlich geänderter und vorab geänderter Datensätze. Es gibt keine OLD-Variablen für INSERT-Trigger ist keine NEUE Variable für DELETE-Trigger; CURRENT_TIMESTAMP und USER() sind MySQL-Systemfunktionen, die die aktuelle Zeit und den angemeldeten Benutzer zurückgeben.
Nachdem wir den Trigger erstellt haben, führen wir einige Datenänderungsvorgänge durch, um die Wirkung des Triggers zu überprüfen:
UPDATE employee
SET email = 'sunqian@shuguo.net'
WHERE emp_name = '孙乾';
UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = '孙乾';
SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date |change_by|
--------|------|----------|----------|-------------------|---------|
1| 25| 4700| 5170|2019-10-18 10:16:36|TONY |Nach dem Login kopieren
Die erste UPDATE-Anweisung hat nur die E-Mail-Adresse von „Sun Qian“ geändert, sodass tri_audit_salary nicht ausgelöst wird Die UPDATE-Anweisung ändert sein monatliches Gehalt und löst tri_audit_salary aus. Daher enthält die Prüftabelle „salary_audit“ ein Datenelement, das die Situation vor und nach der monatlichen Gehaltsänderung aufzeichnet.
Wenn Sie die Vorgänge des gleichzeitigen Hinzufügens und Löschens von Mitarbeitern überwachen möchten, können Sie einen INSERT-Trigger und einen DELETE-Trigger erstellen.
Darüber hinaus unterstützt MySQL die Definition mehrerer Trigger für denselben Auslösezeitpunkt und dasselbe Ereignis sowie die Angabe ihrer Ausführungsreihenfolge:
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name FOR EACH ROW
{ FOLLOWS | PRECEDES } other_trigger
trigger_body;Nach dem Login kopieren
Unter diesen bedeutet FOLLOWS, dass der Trigger nach dem Trigger ausgeführt wird. other_trigger bedeutet, dass dieser Trigger ausgeführt wird vor other_trigger; wenn keine Optionen angegeben sind, erfolgt die Ausführung standardmäßig in der Reihenfolge, in der Trigger erstellt werden.
Trigger anzeigen
Verwenden Sie die SHOW TRIGGERS-Anweisung, um die Liste der Trigger in der Datenbank anzuzeigen:
SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]Nach dem Login kopieren
Unter anderem wird db_name verwendet, um die Trigger in der angegebenen Datenbank anzuzeigen, und der Standardwert ist die aktuelle Datenbank ; LIKE wird zum Abgleichen des Speichernamens des Prozesses verwendet. WHERE kann weitere Filterbedingungen angeben. Die folgende Anweisung gibt beispielsweise die Trigger in der aktuellen Datenbank zurück:
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: tri_audit_salary
Event: UPDATE
Table: employee
Statement: BEGIN
-- 当月薪改变时，记录审计数据
IF (NEW.salary <> OLD.salary) THEN
INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
END IF;
END
Timing: AFTER
Created: 2020-10-06 21:50:02.47
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)Nach dem Login kopieren
Darüber hinaus enthält die MySQL-Systemtabelle INFORMATION_SCHEMA.TRIGGERS detailliertere Triggerinformationen.
Wenn Sie die DDL-Anweisung zum Erstellen eines Triggers erhalten möchten, können Sie die Anweisung SHOW CREATE TRIGGER verwenden. Beispiel:
mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
Trigger: tri_audit_salary
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
-- 当月薪改变时，记录审计数据
IF (NEW.salary <> OLD.salary) THEN
INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
END IF;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)Nach dem Login kopieren
Löschen eines Triggers
MySQL bietet keine Anweisung zum Ändern eines Triggers. Sie können einen Trigger nur über die DROP TRIGGER-Anweisung löschen und erneut erstellen. Beispielsweise kann die folgende Anweisung verwendet werden, um den Trigger tri_audit_salary zu löschen:
DROP TRIGGER IF EXISTS tri_audit_salary;
Nach dem Login kopieren
IF EXISTS, um einen Fehler zu vermeiden, wenn der Trigger tri_audit_salary nicht existiert.
【Verwandte Empfehlung: MySQL-Video-Tutorial】
Das obige ist der detaillierte Inhalt vonAuf welcher Ebene ist MySQL-Trigger?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
