In SQL Server sind Trigger Datenbankobjekte. Im Grunde genommen sind es spezielle Arten von Funktionsaufrufen, die auf bestimmte Datenbankaktionen reagieren.
Dieser umfassende Leitfaden liefert Ihnen detaillierte Einblicke in SQL-Trigger, die in Ihrem Arbeitsalltag von großem Nutzen sein können. Beginnen wir!
Was genau sind SQL-Trigger?
Der Begriff „Trigger“ beschreibt im Wesentlichen eine Anweisung, die den Server veranlasst, eine bestimmte Abfrage jedes Mal automatisch auszuführen, wenn sich der Inhalt der Datenbank verändert.
Ein Trigger ist eine Sammlung von spezifisch benannten SQL-Anfragen, die im Speicherbereich abgelegt werden. Er stellt eine besondere Art von Funktionsaufruf dar, der unmittelbar ausgeführt wird, wenn ein Datenbankereignis eintritt. Jeder Trigger ist einer bestimmten Tabelle zugeordnet.
Ein Trigger könnte beispielsweise dann aktiv werden, wenn einer spezifischen Tabelle eine neue Spalte hinzugefügt wird oder wenn bestimmte Datensätze geändert werden.
Laut dem Microsoft Developer Network stellen Trigger eine spezielle Kategorie von gespeicherten Prozeduren dar. In einer Trigger-Anweisung legen wir zunächst fest, wann der Trigger ausgeführt werden soll, und geben dann die Aktion an, die nach der Aktivierung des Triggers ausgeführt werden soll.
Syntax:
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];
Erläuterung der einzelnen Parameter:
CREATE TRIGGER trigger_name– Hiermit wird ein neuer Trigger erstellt oder der Name eines bestehenden Triggers verändert.BEFORE/AFTER– Diese Anweisung legt den Zeitpunkt der Triggerausführung fest, entweder vor oder nach einem bestimmten Ereignis.INSERT/UPDATE/DELETE– Beschreibt die Operation, die wir an den Tabellen durchführen möchten.ON tableName– Hier geben wir den Namen der Tabelle an, für die wir einen Trigger einrichten möchten.FOR EACH ROW– Diese Anweisung bezieht sich auf Zeilentriggern, d.h. der Trigger wird ausgeführt, wenn eine Zeile geändert wird.trigger_body– Bestimmt die Aktion, die ausgeführt wird, wenn der Trigger ausgelöst wird.
Trigger sind gespeicherte Funktionen mit eindeutigen Kennungen, die es uns ermöglichen, bereits durchgeführte und gesicherte Abfragen wiederzuverwenden. Lassen Sie uns nun ergründen, warum SQL diese benötigt.
Trigger werden hauptsächlich genutzt, um die Ausführung von Code zu steuern, wenn ein bestimmtes Ereignis eintritt. Mit anderen Worten, die Verwendung von Triggern ist die ideale Lösung, wenn ein bestimmtes Codefragment immer als Reaktion auf ein bestimmtes Ereignis ausgeführt werden soll.
Im Folgenden sind einige Vorteile der Verwendung von Triggern bei SQL-Datenbankoperationen aufgeführt:
- Sie ermöglichen zusätzliche Prüfungen, wenn Daten in die betroffene Tabelle eingefügt, aktualisiert oder gelöscht werden.
- Sie reduzieren die Antwortzeiten, was zur Optimierung der Rechenkosten beiträgt.
- Sie ermöglichen die Kodierung komplexerer Standardparameter, auf die aufgrund anfänglicher Einschränkungen nicht zugegriffen werden kann.
Referenzielle Integrität ist ein entscheidendes Merkmal relationaler Datenbanksysteme. Dies bedeutet, dass die im Datenbanksystem gehaltenen Daten für jede Transaktion und jede Operation immer korrekt sein müssen.
Wenn sich zwei Tabellen in unterschiedlichen Datenbanken oder Systemen befinden, gibt es keine Möglichkeit, die Datenvalidierung mithilfe von Integritätsbedingungen sicherzustellen. In solch einer Situation stellen Trigger die einzige Möglichkeit zur Durchsetzung der Datenintegrität dar.
Kombination von Trigger-Argumenten
Für jede Tabelle können wir sechs verschiedene Arten von Triggern festlegen. Dies ist die Kombination von Trigger-Argumenten, die in SQL-Zeilentriggern vorkommen.
BEFORE INSERT: Diese Trigger führen die Aktionen für die Zeilen durch, bevor INSERT-Operationen in der angegebenen Tabelle oder Datenbank ausgeführt werden.
AFTER INSERT: Er führt die Aktion für die Zeilen unmittelbar nach jeder Datenbank-INSERT-Aktivität aus.
BEFORE UPDATE: Diese Trigger führen eine Funktion für die Zeilen aus, bevor eine UPDATE-Operation in der Datenbank ausgeführt wird.
AFTER UPDATE: Er führt die Aktion für die Zeilen aus, unmittelbar nach einer UPDATE-Aktivität in der Datenbank oder einer bestimmten Tabelle.
BEFORE DELETE: Er führt eine bestimmte Operation für die Zeilen aus, bevor die Datenbank oder Tabelle einer DELETE-Operation unterzogen wird.
AFTER DELETE: Diese Trigger führen die Aktion für die Zeilen nach jeder DELETE-Transaktion aus.
Arten von SQL-Triggern
SQL-Trigger sind gespeicherte Funktionen, die unmittelbar ausgeführt werden, wenn bestimmte Ereignisse eintreten. Es ähnelt der ereignisgesteuerten Programmierung. Die folgenden Situationen können die Ausführung von Triggern auslösen:
DML-Trigger: DML steht für Data Manipulation Language. DML-Trigger ermöglichen die Ausführung von Code als Reaktion auf Datenänderungen. Dieser Trigger wird aktiviert, wenn DML-Befehle wie INSERT, UPDATE und DELETE ausgeführt werden. Diese werden auch als „Tabellentrigger“ bezeichnet.
DDL-Trigger: DDL steht für Data Definition Language. DDL-Trigger ermöglichen die Ausführung von Code als Reaktion auf Änderungen am Datenbankschema, wie z.B. das Hinzufügen oder Löschen von Tabellen, oder auf Serverereignisse, z.B. wenn sich ein Benutzer anmeldet. Diese werden als „Datenbanktrigger“ bezeichnet.
Diese Trigger können aktiviert werden, wenn bestimmte DDL-Anweisungen wie CREATE, ALTER oder DROP in der aktiven Datenbank ausgeführt werden. Sie können auch verwendet werden, um die durchgeführten Aktivitäten zu verfolgen und zu verwalten.
LOGON-Trigger: Immer wenn ein LOGON-Ereignis (Start, Anmeldung, Abmeldung, Herunterfahren) eintritt, werden LOGON-Trigger unmittelbar aufgerufen. Sie werden erst nach einem Benutzerauthentifizierungsprozess und noch vor Beginn der Benutzertransaktion durchgeführt. Die LOGON-Trigger werden nicht ausgelöst, wenn die Autorisierung fehlschlägt.
Diese Trigger können neben anderen Überwachungs- und Identitätsverwaltungsfunktionen für Serververbindungen verwendet werden, um den Anmeldeverlauf aufzuzeichnen oder eine Ereignisbeschränkung für eine bestimmte Anmeldung festzulegen.
CLR-Trigger: CLR steht für Common Language Runtime. CLR-Trigger sind eine spezielle Untergruppe von Triggern, die hauptsächlich auf CLR innerhalb der .NET-Technologie basieren. Diese Trigger sind nützlich, wenn der Trigger umfangreiche Berechnungen durchführen oder sich auf eine andere Entität als SQL beziehen muss.
DML- und DDL-Trigger können tatsächlich erstellt werden, indem die Programmierung von unterstützten CLR-Triggern in .NET-Technologien, einschließlich Visual Basic, C# und Fis, ermöglicht wird.
Beispiel für einen SQL Server-Trigger
Lassen Sie uns diese Triggerkonzepte anhand eines Beispiels verdeutlichen.
Erstellen wir zunächst eine Datenbank mit SQL-Anweisungen.
CREATE DATABASE testdb; use testdb;
Hier habe ich die Datenbank „testdb“ genannt. Der nächste Schritt ist die Erstellung einer Tabelle.
CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );
Ich habe eine Tabelle zum Speichern der Schülerdaten erstellt. Hier ist der Befehl, um die Struktur der Tabelle zu beschreiben. „Student“ ist der von mir gewählte Tabellenname.
DESC student;
Unten ist die Struktur der von mir erstellten Tabelle.
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
Nachdem wir eine Tabelle erstellt haben, richten wir als Nächstes einen Trigger ein. Versuchen wir das BEFORE INSERT-Argument zu verwenden.
Der Name des von mir erstellten Triggers ist „marks“. Immer wenn die Tabelle mit den Noten des Schülers geändert wird, versucht der folgende Trigger, die Gesamtnote des Schülers automatisch zu berechnen.
CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;
Da wir Zeilendaten ersetzen und nicht mit den alten arbeiten müssen, haben wir „total“ mit einem neuen Klassennamen definiert, und alle nachfolgenden Ausdrücke werden mit dem Punktoperator nach total neuen Schlüsselwörtern vorangestellt. Fügen wir nun jeder Zeile Werte hinzu und betrachten die Ergebnisse. Zunächst ist die Gesamtnote für jeden Schüler 0.
INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);
Die Trigger-Anweisung wird in diesem Fall automatisch ausgelöst, wenn Daten in die Schülertabelle eingefügt werden. Die Gesamtpunktzahl jedes Schülers wird vom Trigger berechnet. Lassen Sie uns nun überprüfen, ob der Trigger mit einer SELECT-Anweisung aufgerufen wird oder nicht.
SELECT * FROM table_name;
Und hier ist die endgültige Ausgabe:
mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)
Im obigen Ergebnis sehen Sie, dass alle Fachnoten automatisch für jeden Schüler addiert werden. Wir können also schließen, dass der Trigger erfolgreich aufgerufen wurde.
Zusätzliche Triggeroperationen
Wir können viele Operationen mit Triggern durchführen. Einige sind einfach, andere etwas komplex, aber wenn wir die Abfragen einmal durchgegangen sind, ist es leicht zu verstehen. Mit Transact-SQL-Anweisungen können Sie Trigger mit den folgenden Befehlen aktivieren, deaktivieren oder löschen.
Abfrage, um zu überprüfen, ob ein bestimmter Trigger vorhanden ist oder nicht
Dieser Befehl sucht in der gesamten Datenbank nach dem angegebenen Trigger.
SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'
Abfrage zum Anzeigen von Triggern
Alle in der aktiven Datenbank verfügbaren Trigger werden mit der folgenden Anweisung angezeigt.
SHOW TRIGGERS;
Abfrage zum Deaktivieren des Triggers
Der folgende Befehl deaktiviert den Trigger in der Arbeitsdatenbank.
DISABLE TRIGGER trigger_name ON DATABASE;
Sie können auch einen bestimmten Tabellennamen angeben, um einen Trigger zu deaktivieren.
DISABLE TRIGGER trigger_name ON table_name;
Abfrage zum Aktivieren des Triggers
Der folgende Befehl deaktiviert zunächst einen bestimmten Trigger, der für die angegebene Tabelle in der aktiven Datenbank definiert wurde, bevor er wieder aktiviert wird.
ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name
Der Trigger muss deaktiviert sein, bevor der Versuch unternommen wird, ihn zu aktivieren.
Abfrage zum Aktivieren oder Deaktivieren aller Trigger in einer Tabelle
Mit der obigen SQL-Anweisung können wir alle Tabellentriggern gleichzeitig deaktivieren oder aktivieren, indem wir „ALL“ anstelle eines bestimmten Triggernamens einsetzen.
ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL
Abfrage zum Löschen oder Entfernen des Triggers
Ein Trigger kann entfernt werden, indem er oder die gesamte Tabelle gelöscht wird. Beim Löschen einer Tabelle werden auch alle zugehörigen Trigger gelöscht.
DROP TRIGGER [trigger_name];
Immer wenn ein Trigger gelöscht wird, werden die zugehörigen Daten aus der Datentabelle sys.objects gelöscht.
Vorteile von Triggern
- Es ist unkompliziert, Trigger zu erstellen, und der Trigger selbst kann gespeicherte Funktionen und Methoden aufrufen.
- Benutzer können einfache Audits mithilfe von Triggern implementieren.
- In SQL Server können Beschränkungen über Entitäten hinweg nicht erstellt werden, obwohl Sie die Operation von Beschränkungen emulieren können, indem Sie Trigger einsetzen.
- Integritätsbedingungen können mittels Triggern datenbankübergreifend realisiert werden.
- Wenn eine Gruppenvalidierung anstelle einer zeilenweisen Überprüfung neu eingegebener oder geänderter Daten erforderlich ist, können Trigger hilfreich sein.
Nachteile von Triggern
Aufgrund ihrer Einschränkungen sind SQL-Trigger in manchen Situationen möglicherweise nicht die beste Wahl.
- Trigger müssen sorgfältig dokumentiert sein.
- Das Debuggen von Triggern kann aufgrund der gleichzeitigen Datenbankausführung, auf die Anwendungskomponenten möglicherweise keinen Zugriff haben, kompliziert sein.
- DML-Anweisungen werden komplexer, wenn Trigger verwendet werden.
- Selbst ein kleines Problem mit einem Trigger kann zu logischen Fehlern in der Anweisung führen.
Fazit
Trigger sind sehr nützliche Komponenten von Transact-SQL und SQL und können auch in Oracle eingesetzt werden. Die Verwendung von Triggern ist entscheidend, wenn gespeicherte Methoden aufgerufen werden. Diese SQL-Trigger ermöglichen es uns, Aktivitätspläne zu analysieren und zu definieren, wie wir darauf reagieren. Wir können auch nach einer bestimmten Tabelle suchen, die mit einem Trigger zur Erfassung von Daten verbunden ist.
Rekursion kann durch Trigger ausgelöst werden. Immer wenn ein Trigger für eine Tabelle einen Befehl für die übergeordnete Tabelle ausführt, wird die zweite Iteration des Triggers ausgelöst, und dies wird als rekursiver Trigger bezeichnet. Dies hilft beim Versuch, eine Identitätskorrelation zu lösen.
Zusätzlich regeln Trigger das Aktualisierungsmuster, das die Datenbank akzeptieren darf. Es ist sehr vorteilhaft, die Datenintegrität im Datenbanksystem aufrechtzuerhalten, wenn SQL-Einschränkungsschlüssel, insbesondere Primär- und Fremdschlüssel, nicht vorhanden sind.
Ich hoffe, dieser Artikel hat Ihnen geholfen, mehr über SQL-Trigger zu erfahren.
Wenn Sie sich intensiver mit Datenbanken beschäftigen möchten, finden Sie hier einige ausgezeichnete Ressourcen zum Erlernen von SQL und NoSQL.