Auf welcher Ebene ist MySQL-Trigger?

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;

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)
);

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 ;

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     |

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;

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]

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)

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)

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;

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!