SQL -Set off sind wie automatisierte Routinen in einer Datenbank, in der vordefinierte Aktionen ausgeführt werden, wenn bestimmte Ereignisse wie Einfügen, Aktualisieren oder Löschen in einer Tabelle auftreten. Dies hilft bei der Automatisierung der Datenaktualisierung und der festgelegten Regeln. Es hält die Daten sauber und konsistent, ohne dass Sie jedes Mal zusätzlichen Code schreiben müssen. In diesem Artikel werden wir untersuchen, was genau ein SQL -Set off ist und wie er funktioniert. Wir werden auch verschiedene Arten von SQL -Triggern anhand einiger Beispiele untersuchen und verstehen, wie sie in unterschiedlich verwendet werden MySQL, PostgreSQL und SQL Server. Am Ende haben Sie eine gute Vorstellung davon, wie und wann Sie Auslöser in einem Datenbank -Setup verwenden müssen.
Was ist ein SQL -Auslöser?
Ein Auslöser ist wie ein automatisches Programm, das an eine Datenbanktabelle gebunden ist, und es wird der SQL -Code automatisch ausgeführt, wenn ein bestimmtes Ereignis auftritt, z. B. das Einfügen, Aktualisieren oder Löschen einer Zeile. Beispielsweise können Sie einen Auslöser verwenden, um einen Zeitstempel automatisch festzulegen, wenn eine neue Zeile erstellt, hinzugefügt oder gelöscht oder neue Datenregeln ohne zusätzlichen Code in Ihrer Anwendung angewendet werden. In einfachen Worten können wir sagen, dass ein Auslöser ein gespeicherter Satz von SQL -Anweisungen ist, die als Reaktion auf Tabellenereignisse „Feuer“ sind.
Wie Auslöser in SQL funktionieren
In MysqlSet off werden mit der Anweisung erstellen Set off definiert und sind an eine bestimmte Tabelle und ein bestimmtes Ereignis beigefügt. Jeder Auslöser ist Zeilenebene, was bedeutet, dass er für jede vom Ereignis betroffene Zeile einmal ausgeführt wird. Wenn Sie einen Auslöser erstellen, geben Sie an:
- Timing: Vor oder nachher – ob der Auslöser vor oder nach dem Ereignis abfeuert.
- Ereignis: Einfügen, aktualisieren oder löschen Sie den Vorgang, der den Auslöser aktiviert.
- Tabelle: Der Identify der Tabelle, an die sie beigefügt ist.
- Auslöser Körper: Die SQL -Anweisungen zur Ausführung, eingeschlossen in Beginn… Ende.
Beispielsweise wird ein Voreingang ausgeführt, kurz bevor eine neue Zeile zur Tabelle hinzugefügt wird, und ein After -Replace -Set off wird gleich nach der Änderung einer vorhandenen Zeile ausgeführt. MySQL benötigt das Schlüsselwort für jede Zeile in einem Auslöser, wodurch der Triggerkörper für jede von der Operation betroffene Zeile ausgeführt wird.
In einem Auslöser verweisen Sie mit den neuen und alten Aliase auf die Zeilendaten. In einem Einfügungsauslöser ist nur New.Column verfügbar (die eingehenden Daten). In ähnlicher Weise ist in einem Löschauslöser nur alter. In einem Replace -Auslöser können Sie jedoch beides verwenden: Previous.Column bezieht sich auf die Werte der Zeile vor dem Replace, und New Werte nach dem Replace.
Sehen wir uns die Set off -SQL -Syntax an:
CREATE TRIGGER trigger_name
BEFORE|AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
-- SQL statements right here --
END;Dies ist das Commonplace -SQL -Formular. Eine Sache, die beachtet werden muss, ist, dass die Triggerkörper häufig mehrere Aussagen mit Semikolonen enthalten. Normalerweise sollten Sie zuerst den SQL -Degremiter ändern, z.
Schritt-für-Schritt-Beispiel für das Erstellen von Triggern
Lassen Sie uns nun sehen, wie wir Set off in SQL erstellen können.
Schritt 1: Bereiten Sie eine Tabelle vor
Lassen Sie uns dazu einfach eine einfache Benutzertabelle erstellen:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
created_at DATETIME,
updated_at DATETIME
);Schritt 2: Ändern Sie den Trennzeichen
In SQL können Sie den Anweisungsgrenzwert ändern, damit Sie Multi-Assertion-Set off schreiben können. Zum Beispiel:
DELIMITER //Schritt 3: Schreiben Sie die Anweisung Create Set off
Zum Beispiel können wir einen Auslöser erstellen, der die Spalte created_at auf die aktuelle Zeit beim Einfügen festlegt:
CREATE TRIGGER before_users_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END;
//Im obigen Code bedeutet der Voreinfügungseinsatz auf den Benutzern, dass der Set off vor dem Einfügen jeder neuen Zeile feuert. Der Set off -Körper prüft, ob neu.created_at null ist und wenn ja, füllt es mit jetzt (). Dies automatisiert ein Zeitstempel.
Nach dem Schreiben des Auslösers können Sie den Trennzeichen falls gewünscht wiederherstellen, damit andere Codes ohne Probleme ausgeführt werden können.
DELIMITER ;Schritt 4: Testen Sie den Abzug
Wenn Sie nun einfügen, ohne erstellte created_at anzugeben, wird der Auslöser automatisch festgelegt.
INSERT INTO customers (username) VALUES ('Alice');
SELECT * FROM customers;Und das created_at wird automatisch mit dem aktuellen Datum/der aktuellen Uhrzeit gefüllt. Ein Auslöser kann Aufgaben automatisieren, indem Standardwerte eingerichtet werden.
Verschiedene Arten von Triggern
Für jede Tabelle gibt es sechs Arten von SQL -Triggern:
- Vor dem Auslöser einfügen
- Vor dem Replace -Set off
- Vor dem Löschen des Triggers
- Nach dem Einfügen von Set off
- Nach dem Replace -Set off
- Nach dem Löschen des Triggers
Lassen Sie uns anhand von Beispielen über jeden von ihnen erfahren.
1. Vor dem Set off einfügen
Dieser Auslöser wird aktiviert, bevor eine neue Zeile in eine Tabelle eingefügt wird. Es wird häufig verwendet, um die Daten zu validieren oder zu ändern, bevor sie gespeichert werden.
Beispiel für die Auslöser -SQL -Syntax für vor dem Einfügen:
DELIMITER //
CREATE TRIGGER before_insert_user
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END;
//
DELIMITER ;Dieser Auslöser wird automatisch auf den Zeitstempel erstellt- und Zeitstempel erstellt eingestellt, bevor ein neuer Benutzerdatensatz eingefügt wird.
2. Vor dem Replace -Set off
Dieser Auslöser wird ausgeführt, bevor eine vorhandene Zeile aktualisiert wird. Dies ermöglicht die Validierung oder Änderung von Daten, bevor das Replace erfolgt.
Beispiel für die Set off -SQL -Syntax für vor dem Replace:
DELIMITER //
CREATE TRIGGER before_update_user
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
IF NEW.e-mail NOT LIKE '%@%' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid e-mail deal with';
END IF;
END;
//
DELIMITER ;Dieser Auslöser prüft, ob die neue E -Mail -Adresse vor der Aktualisierung des Benutzerkurs gültig ist. Wenn nicht, wird ein Fehler aufgeworfen.
3. Vor dem Löschen des Triggers
Dies wird ausgeführt, bevor eine Zeile gelöscht wird. Und kann auch zur Durchsetzung der Referenzintegrität oder zur Verhinderung des Löschens unter bestimmten Bedingungen verwendet werden.
Beispiel für die Auslöser -SQL -Syntax für vor dem Löschen:
DELIMITER //
CREATE TRIGGER before_delete_order
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
IF OLD.standing="Shipped" THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Can not delete shipped orders';
END IF;
END;
//
DELIMITER ;Dieser Auslöser verhindert die Löschung von Bestellungen, die bereits versendet wurden.
4. Nach dem Einfügungsauslöser
Dieser Auslöser wird ausgeführt, nachdem eine neue Zeile eingefügt wurde und häufig zur Protokollierung oder Aktualisierung der zugehörigen Tabellen verwendet wird.
Beispiel für die Set off -SQL -Syntax für After After Insert
DELIMITER //
CREATE TRIGGER after_insert_user
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, motion, log_time
VALUES (NEW.id, 'Consumer created', NOW());
END;
//
DELIMITER ;Dadurch wird die Erstellung eines neuen Benutzers in der Tabelle user_logs protokolliert.
5. Nach dem Replace -Set off
Dieser Auslöser wird ausgeführt, nachdem eine Zeile aktualisiert wurde. Und ist nützlich für die Prüfung von Änderungen oder zur Aktualisierung der verwandten Daten.
Beispiel für die Set off -SQL -Syntax für After After Replace
DELIMITER //
CREATE TRIGGER after_update_user
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, motion, log_time)
VALUES (NEW.id, CONCAT('Consumer up to date: ', OLD.identify, ' to ', NEW.identify), NOW());
END;
//
DELIMITER ;Dadurch wird die Änderung des Namens eines Benutzers nach einem Replace abgelehnt.
6. Nach dem Löschen des Triggers
Dieser Auslöser wird ausgeführt, nachdem eine Zeile gelöscht wurde. Und wird üblicherweise zur Protokollierung von Löschungen oder zur Reinigung der verwandten Daten verwendet.
Beispiel für die Set off -SQL -Syntax für nach dem Löschen
DELIMITER //
CREATE TRIGGER after_delete_user
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, motion, log_time)
VALUES (OLD.id, 'Consumer deleted', NOW());
END;
//
DELIMITER ;Dadurch wird die Löschung eines Benutzers in der Tabelle user_log angewiesen.
Wann und warum können Set off verwendet werden
Auslöser sind leistungsstark, wenn Sie Dinge automatisieren möchten, die passieren, wenn sich die Daten ändern. Im Folgenden finden Sie einige Anwendungsfälle und Vorteile, die hervorgehen, wann und warum Sie SQL -Set off verwenden sollten.
- Automatisierung von Routineaufgaben: Sie können Spalten wie Zeitstempel, Zähler oder berechnete Werte automatisieren oder automatisch füllen oder aktualisieren, ohne einen zusätzlichen Code in Ihrer App zu schreiben. Wie im obigen Beispiel haben wir die Felder erstellte und updated_at automatisch verwendet.
- Durchsetzung der Datenintegrität und -regeln: Auslöser können Ihnen helfen, die Bedingungen zu überprüfen und sogar ungültige Vorgänge zu verhindern. Beispielsweise kann ein Bevor_insert -Set off eine Zeile stoppen, wenn er einige Regeln durchläuft, indem ein Fehler erhöht wird. Dies stellt sicher, dass die Daten auch dann sauber bleiben, wenn ein Fehler auftritt.
- Audit -Protokolle und Monitoring: Sie können Ihnen auch helfen, Änderungen automatisch aufzunehmen. Ein After -Löschen -Set off kann einen Datensatz in eine Protokolltabelle einfügen, wenn eine Zeile gelöscht wird. Dies gibt einen Prüfungsweg, ohne separate Skripte schreiben zu müssen.
- Konsistenz über mehrere Tabellen hinweg aufrechterhalten: Manchmal müssen Sie eine Scenario haben, in der sich die andere Tabelle automatisch aktualisieren soll, wenn eine Tabelle geändert wird. Auslöser können diese verknüpften Updates hinter den Kulissen bewältigen.
Leistungsüberlegungen und Einschränkungen
Sie müssen mit Sorgfalt Set off laufen. Da Set off jedes Mal, wenn sich die Daten ändert, leise verlaufen, können sie manchmal die Dinge verlangsamen oder das Debuggen schwierig machen, wenn Sie zu viele haben. Für Dinge wie das Festlegen von Zeitstempeln, das Überprüfen von Eingängen oder das Synchronisieren anderer Daten sind jedoch sehr nützlich. Sie sparen Zeit und verringern auch alberne Fehler, wenn sie denselben Code immer wieder schreiben.
Hier sind einige Punkte zu berücksichtigen, bevor sie sich für die Verwendung entscheiden Sql Auslöser:
- Versteckte Logik: Der Triggercode wird in den Datenbanken gespeichert und wird automatisch ausgeführt, wodurch das Verhalten des Methods weniger clear wird. So können Entwickler vergessen, dass der Auslöser die Daten hinter den Kulissen verändert. Daher sollte es intestine dokumentiert werden.
- Keine Transaktionskontrolle: Sie können eine Transaktion in einem SQL -Auslöser nicht starten, verpflichten oder zurückgreifen. Alle Triggeraktionen treten im Kontext der Transaktion der ursprünglichen Anweisung auf. Mit anderen Worten, Sie können keine teilweise Änderung in einem Auslöser begehen und die Hauptaussage fortsetzen.
- Nicht-transaktionale Tabellen: Wenn Sie eine nicht-transaktionsübergreifende Motor verwenden und ein Triggerfehler auftritt. SQL kann nicht vollständig zurückrollen. Daher können sich einige Teile der Daten ändern, und einige Teile können möglicherweise nicht die Daten inkonsistent machen.
- Begrenzte Datenoperationen: SQL -Grenzen, die Auslöser bestimmter Aussagen ausführen. Sie können beispielsweise DDL nicht ausführen oder eine gespeicherte Routine aufrufen, die ein Ergebnissatz zurückgibt. Außerdem gibt es in SQL keine Auslöser in Ansichten.
- Keine Rekursion: SQL erlaubt keine Rekursion; Es kann nicht weiterhin dieselbe Tabelle ändern, auf der es so definiert ist, dass sie sich sofort wieder feuern würde. Daher ist es ratsam, Auslöser zu vermeiden, die durch kontinuierliche Aktualisierung der gleichen Zeilen schleifen.
Vergleichstabelle für MySQL vs PostgreSQL vs SQL Server Triggers
Schauen wir uns nun an, wie sich Set off in verschiedenen Datenbanken unterscheiden wie MySQL, PostgreSQLund SQL Server.
| Besonderheit | Mysql | PostgreSQL | SQL Server |
| Triggersyntax | Definiertes Inline in Create Set off, in SQL geschrieben. In immer für jede Zeile einbezogen. | Set off erstellen… FUNKTION FUNKTION FUNKTION_NAME () ausführen. Ermöglicht jede Zeile für jede Anweisung. | Erstellen Sie Set off mit nach oder anstelle von. Immer Assertion-Degree. Verwendet beginnt… Ende. |
| Granularität | Nur Zeilenebene (für jede Zeile). | Zeilenebene (Commonplace) oder Anweisungsebene. | Nur Assertion-Ebene. |
| Timing -Optionen | Vor, nach dem Einfügen, aktualisieren Sie vor, löschen Sie. Nein statt von Auslöser auf Ansichten. | Vor, danach statt (auf Ansichten). | Danach anstelle von (Ansichten oder überschreiben Aktionen). |
| Schuss auslösen | Feuer einmal professional betroffener Reihe. | Kann einmal professional Reihe oder einmal professional Aussage feuern. | Feuer einmal professional Aussage. Verwendet eingeführte und gelöschte virtuelle Tische. |
| Zeilen verweisen | Verwendet New.Column und Previous.Column. | Verwendet neue und alte Insider -Triggerfunktionen. | Verwendet eingeführte und gelöschte virtuelle Tische. Muss sich ihnen anschließen, um auf die geänderten Zeilen zuzugreifen. |
| Sprachunterstützung | Nur SQL (keine dynamische SQL in Triggern). | PL/PGSQL, PL/Python, andere. Unterstützt dynamische SQL, return neu/alt. | T-SQL mit voller Sprachunterstützung (Transaktionen, versuchen/fangen usw.). |
| Fähigkeiten | Einfach. Keine dynamischen SQL- oder Verfahrensrückgabeergebnisse. Bevor Set off neu ändern können. | Kraftvoll. Kann Aktionen abbrechen oder ändern, Werte zurückgeben und mehrere Sprachen verwenden. | Integriert in SQL Server -Funktionen. Ermöglicht Strive/Fang, Transaktionen und komplexe Logik. |
| Auslösergrenzen | Vor v5.7.2: nur 1 vor und 1 nach Set off professional Tabelle professional Ereignis (einfügen, aktualisieren, löschen). Und nach V5.2 können Sie mehrere Auslöser für das gleiche Ereignis und das gleiche Timing erstellen. Die Verwendung folgt oder geht vor, um die Reihenfolge zu kontrollieren. | Keine erzwungenen Auslöser zählende Grenzen. | Erlaubt bis zu 16 Auslöser professional Tisch. |
| Auslöser bestellen | Kontrolliert mit Follows / Vorabgeschwindigkeiten. | Keine einheimische Bestellung von Triggern. | Keine native Bestellung, aber Sie können Logik in Triggern verwalten. |
| Fehlerbehandlung | Kein Versuch/Fang. Fehler lassen die Erklärung ab. Nach Läufen nur dann, wenn vor und die Zeilenaktion erfolgreich ist. | Verwendet Ausnahmeblöcke in Funktionen. Fehler lassen die Erklärung ab. | Unterstützt versuchen/fangen. Auslöserfehler abbrechen die Anweisung. |
Abschluss
Obwohl sich SQL -Set off zunächst etwas schwierig anfühlen, werden Sie sie vollständig verstehen und erfahren, wie hilfreich sie sind, sobald Sie anfangen. Sie laufen alleine, wenn sich etwas in Ihren Tabellen ändert, was Zeit spart und sicherstellt, dass die Daten weiterhin den von Ihnen festgelegten Regeln folgen. Unabhängig davon, ob es sich um Protokollierungsänderungen handelt, unerwünschte Updates zu stoppen oder Informationen über Tabellen hinweg zu synchronisieren, sind Set off in SQL wirklich nützlich. Stellen Sie nur sicher, dass Sie sie nicht zu übernehmen und zu viele Auslöser machen, da dies die Dinge später unordentlich machen kann. Halten Sie es einfach, testen Sie es richtig, und Sie können loslegen.
Hallo, ich bin Janvi, ein leidenschaftlicher Knowledge -Science -Fanatic, der derzeit bei Analytics Vidhya arbeitet. Meine Reise in die Daten der Daten begann mit einer tiefen Neugier darüber, wie wir aus komplexen Datensätzen sinnvolle Erkenntnisse herausholen können.
Melden Sie sich an, um weiter zu lesen und Experten-Kuratinhalte zu genießen.