Trigger bedeutet grob übersetzt auf Vietnamesisch Trigger, was bedeutet, dass es wie ein Trigger ist und aktiviert wird, wenn der Trigger gedrückt wird.

In SQL ist ein Trigger eine Art gespeicherte Prozedur, die ausgeführt wird, wenn versucht wird, Daten in der Tabelle zu ändern, in der der Trigger erstellt wurde. Im Gegensatz zu regulären gespeicherten Systemprozeduren können Trigger weder direkt ausgeführt noch Parameter übergeben oder empfangen.

Jede Tabelle hat normalerweise 3 Operationen, die Daten ändern: UPDATE, INSERT, DELETE. Und manchmal haben wir für jede dieser Aktionen Einschränkungen auf dem Tisch, um die Datenerhaltung zu unterstützen. Jetzt ist die Verwendung von Triggern eine gute Lösung.

Arten von Triggern

Trigger werden automatisch ausgelöst, wenn ein Ereignis in einer Tabelle oder Ansicht auftritt. Ereignissprachen werden in zwei Typen unterteilt, DML- und DDL-Ereignisse. Trigger, die DML-Ereignissen zugeordnet sind, werden DML-Trigger oder auch DDL-Trigger genannt.

DML-Trigger

DML-Trigger werden nach Abschluss eines DML-Ereignisses oder im Auftrag eines DML-Ereignisses ausgeführt. Diese Trigger stellen die referenzielle Integrität sicher, indem sie Änderungen an zugehörigen Tabellen kaskadieren, wenn eine Zeile geändert wird.

DML-Trigger gibt es in drei Haupttypen:

• INSERT-Trigger
• UPDATE-Trigger
• DELETE-Trigger

Einführung in eingefügte und gelöschte Tabellen

Die SQL-Anweisung in DML-Triggern verwendet zwei spezielle Tabellentypen, um die Datenbank zu ändern. Wenn Daten hinzugefügt, aktualisiert oder gelöscht werden, erstellt und verwaltet SQL Server diese Tabellen automatisch, die Zwischenablage speichert die ursprünglichen Daten sowie die geänderten Daten wie folgt:

Eingefügte Tabelle

Eingefügte Tabellen speichern Kopien von Zeilen, die durch INSERT- und UPDATE-Anweisungen angewendet wurden. Während des Einfügens oder Aktualisierens werden beiden Tabellen, der eingefügten Tabelle und der Triggertabelle, neue Zeilen hinzugefügt. Die Zeile in der eingefügten Tabelle ist eine Kopie der neuen Zeile in der Triggertabelle.

Gelöschte Tabelle

Die Deleted-Tabelle speichert eine Kopie der Zeilen, die durch die DELETE- und UPDATE-Anweisungen angewendet wurden. Während der DELETE- oder UPDATE-Ausführung wird die Zeile aus der Triggertabelle gelöscht und in die gelöschte Tabelle verschoben.

Hinweis: Die eingefügte Tabelle und die gelöschte Tabelle sind temporär, um Speicher zu speichern und die Auswirkung bestimmter Datenänderungen zu überprüfen und Bedingungen für DML-Triggeraktionen festzulegen. SQL Server 2019 lässt keine text-, nxtext- oder image-Spaltenreferenzen in eingefügten und gelöschten Tabellen für AFTER -Trigger zu.

Trigger einfügen

Ein INSERT-Trigger wird ausgelöst, wenn ein neuer Datensatz in die Tabelle eingefügt wird. Der INSERT-Trigger stellt sicher, dass die Eingabe erfüllt ist, aber die Einschränkung in der Tabelle definiert ist.

Wenn der Benutzer einen Datensatz in die Tabelle eingibt, speichert der INSERT-Trigger eine Kopie dieser Datensätze in der eingefügten Tabelle. Es prüft dann, ob der moiws-Wert die Bedingungen in der Tabelle erfüllt.

Wenn der Datensatz gültig ist, fügt der INSERT-Trigger die Zeile zur Tabelle hinzu, andernfalls zeigt er eine Fehlermeldung an. Ein INSERT-Trigger wird über das Schlüsselwort INSERT in den Anweisungen CREATE TRIGGER und ALTER TRIGGER erstellt.

Syntax INSERT|UPDATE|DELETE-Trigger:

 CREATE TRIGGER [schema_name.]trigger_name ON [schema_name.]table_name [WITH ENCRYPTION] [FOR INSERT] AS [IF UPDATE (column_name)] [{AND|OR} UPDATE (colum_name)] AS BEGIN <sql_statement> END;

Zum Beispiel:

Schritt 1 – Erstellen Sie 2 Tabellen gemäß den folgenden Informationen:

 CREATE TABLE Locations (LocationID int, LocName varchar(100)); CREATE TABLE LocationHistory (LocationID int, ModifedDate DATETIME);

Schritt 2 – Erstellen Sie einen INSERT-Trigger namens TRIGGER_INSERT_Locations für die Locations-Tabelle

 CREATE TRIGGER TRIGGER_INSERT_Locations ON Locations FOR INSERT NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID , getdate() FROM inserted END;

Testen Sie im nächsten Schritt die Trigger-Anweisung:

 insert into dbo.Locations values(1,'Ha Noi');

Beim obigen Trigger werden beim Einfügen von Standortdaten LocationHistory-Daten basierend auf den Standortdaten hinzugefügt.

Darin gibt es ein neues Schlüsselwort Replikation ist eine Datenreplikations- und -verteilungstechnologie und eine der in SSMS verfügbaren Disaster-Recovery-Lösungen, die sehr nützlich ist, um eine zweite oder Sicherungskopie von Objekten (Tabelle, Ansicht, gespeicherte Prozedur) zu verwalten und Datenbank. Die Bedeutung NOT FOR REPLICATION wird nicht für Replikationssituationen verwendet.

Auslöser aktualisieren

Die UPDATE-Trigger-Anweisung kopiert den ursprünglichen Datensatz in die gelöschte Tabelle und kopiert den neuen Datensatz in die eingefügte Tabelle, wenn der Datensatz aktualisiert wird. Anschließend wertet es den neuen Datensatz aus, um festzustellen, ob der Wert die Einschränkung in der Tabelle erfüllt.

Wenn der neue Wert gültig ist, wird der Datensatz aus der eingefügten Tabelle in die Triggertabelle kopiert. Wenn der neue Wert jedoch nicht gültig ist, wird eine Fehlermeldung an den Benutzer ausgegeben. Die Originaldatensätze werden also aus der Tabelle „Gelöscht“ zurück in die Triggertabelle kopiert.

Ein UPDATE-Trigger kann mit dem Schlüsselwort UPDATE im Befehl CREATE TRIGGER oder ALTER TRIGGER erstellt werden.

Zum Beispiel:

 CREATE TRIGGER TRIGGER_UPDATE_Locations ON Locations FOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM inserted END; GO update Locations SET LocName = 'Ho Chi Minh' where [LocName] = 'Ha Noi';

Nach dem Ausführen des obigen Auslösererstellungsbefehls und dem Aktualisieren der Daten in der Locations-Tabelle wird die LocationHistory-Tabelle mit neuen Daten hinzugefügt, wenn die Locations-Tabellendaten aktualisiert werden.

Auslöser löschen

Der Löschauslöser wird erstellt, um Benutzer daran zu hindern, bestimmte Datensätze aus der Tabelle zu löschen

Die folgende Ausführungssequenz tritt auf, wenn ein Benutzer versucht, einen Datensatz zu löschen:

• Der Datensatz wird aus der Triggertabelle gelöscht und der Tabelle „Gelöscht“ hinzugefügt.
• Beim Löschen von Daten wird die Constraint-Übereinstimmung überprüft
• Wenn es eine Einschränkung für den Datensatz gibt, die das Löschen verhindert, zeigt der DELETE-Trigger eine Fehlermeldung an
• Gelöschte Datensätze, die in der Tabelle „Gelöscht“ gespeichert sind, werden zurück in die Triggertabelle kopiert.

Zum Beispiel:

 CREATE TRIGGER TRIGGER_DELETE_Locations ON Locations FOR DELETE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM deleted END; GO DELETE FROM dbo.Locations WHERE LocName = 'Ho Chi minh';

Nach dem Ausführen von delete fügt der Trigger neue Datensätze zur LocationsHistory-Tabelle hinzu.

NACH Triggern

Ein AFTER-Trigger wird nach Abschluss der INSERT-, UPDATE-, DELETE-Operation ausgeführt. Eine Tabelle kann mehrere AFTER-Trigger haben, die für jede INSERT-, UPDATE-, DELETE-Operation definiert sind. Wenn mehrere Trigger in derselben Tabelle erstellt werden, muss der Benutzer die Trigger-Ausführungsmethode selbst definieren. Ein AFTER-Trigger wird ausgeführt, wenn die Check Constraints abgeschlossen sind, sodass der Trigger ausgeführt wird, nachdem die eingefügten und gelöschten Tabellen erstellt wurden.

Syntax:

 CREATE TRIGGER <trigger_name> ON <table_name> [WITH ENCRYPTION] [FOR | AFTER] { [INSERT] [,] [UPDATE] [,] [DELETE] } AS BEGIN <sql_statement> END;

Zum Beispiel:

 CREATE TRIGGER AFTER_INSERT_Locations ON Locations AFTER INSERT AS BEGIN INSERT INTO LocationHistory SELECT LocationID ,getdate() FROM inserted END;

Nach der Ausführung wird es in die LocationHistory-Tabelle eingefügt, nachdem Standorte erfolgreich eingefügt wurden. Der Unterschied zwischen Trigger nach und Trigger an besteht nur in der Ausführungszeit des Triggers.

STATT Trigger

Der INSTEAD OF-Trigger wird bei Verwendung anstelle der INSERT-, UPDATE-, DELETE-Operation ausgeführt. Eine Tabelle oder Sicht kann nur einen INSTEAD OF-Trigger haben, der für jede INSERT-, UPDATE-, DELETE-Operation definiert ist.

Der INSTEAD OF-Trigger wird ausgeführt, bevor die Einschränkungsprüfung für die Tabelle ausgeführt wird, dieser Trigger wird ausgeführt, nachdem die eingefügten und gelöschten Tabellen erstellt wurden.

Zum Beispiel:

 CREATE TRIGGER INSTEAD_OF_DELETE_Locations ON Locations INSTEAD OF DELETE AS BEGIN SELECT 'example instead trigger' as Message END; GO DELETE FROM dbo.Locations WHERE LocName='Ha Noi'

Wenn die Löschanweisung ausgeführt wird, wird der INSTEAD OF-Trigger ausgelöst und der Block innerhalb des Triggers ausgeführt, und die Löschaktion findet nicht statt.

Reihenfolge der Ausführung von DML-Triggern

Mit SQL Server kann der Benutzer definieren, welche AFTER-Trigger zuerst und welche später ausgeführt werden sollen. Alle AFTER-Trigger, wenn sie zwischen dem ersten und dem letzten Trigger aufgerufen werden, haben keine bestimmte Ausführungsreihenfolge.

Verwenden Sie die gespeicherte Prozedur sp_settriggerorder , um die DML-Trigger-Türreihenfolge zu definieren.

Die Syntax zum Bestimmen der Ausführungsreihenfolge des AFTER DML-Triggers:

 sp_settriggerorder [@triggername=] '[triggerschema.]triggername', [@order=]'value', [@stmmttype=]'statement_type'

Zum Beispiel:

 EXEC sp_settriggerorder @triggername = 'TRIGGER_DELETE_Locations', @order='FIRST', @stmttype='DELETE'

Siehe Definition des DML-Triggers

Zum Anzeigen der Triggerdefinition über die gespeicherte Prozedur sp_hepltext
Syntax:

 sp_helptext '<trigger_name>';

Zum Beispiel:

 sp_helptext TRIGGER_DELETE_Locations

ALTER DML-Trigger

Es gibt zwei Möglichkeiten, den Trigger zu bearbeiten:

• DROP und RECREATE triggern erneut mit neuem Parameter
• Ändern Sie den Trigger mit der Syntax ALTER TRIGGER

Syntax:

 ALTER TRIGGER <trigger_name> ON {<table_name>|<view_name>} [WITH ENCRYPTION] {FOR|ALTER|INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE] } AS <sql_statement>

Zum Beispiel:

 ALTER TRIGGER TRIGGER_UPDATE_Locations ON Locations WITH ENCRYPTION FOR INSERT AS IF 'Ho Chi Minh' IN (SELECT LocName FROM inserted) BEGIN PRINT 'Location can not be updated' ROLLBACK TRANSACTION END;

DROP-DML-Trigger

Syntax:

 DROP TRIGGER <DML_trigger_name> [,...n]

Zum Beispiel:

 DROP TRIGGER TRIGGER_UPDATE_Locations

DDL-Trigger

Ein DDL-Trigger (Data Definition Language) führt eine gespeicherte Prozedur aus, wenn ein DDL-Ereignis wie CREATE, ALTER, DROP in der Datenbank oder auf dem Server auftritt. DDL-Trigger können nur ausgeführt werden, wenn das DDL-Ereignis abgeschlossen ist.
DDL-Trigger können verwendet werden, um Änderungen im Datenbankschema zu verhindern.
Der DDL-Trigger kann ein Ereignis aufrufen oder eine Nachricht basierend auf einer Schemanachricht anzeigen. DDL-Trigger werden entweder auf Datenbankebene oder auf Serverebene definiert.

Syntax:

 CREATE TRIGGER <trigger_name> ON {ALL SERVER | DATABASE} [WITH ENCRYPTON] {FOR | AFTER} <event_type> } AS <sql_statement>

Zum Beispiel:

 CREATE TRIGGER Secure ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable trigger scure to drop or alter table' ROLLBACK;

Mit diesem Code wird ein DDL-Trigger für die DROP TABLE- oder ALTER TABLE-Anweisung generiert.

Bereich des DDL-Triggers

Der DDDL-Trigger wird durch eine SQL-Anweisung aufgerufen, die in der aktuellen Datenbank oder dem aktuellen Server ausgeführt wird. Beispielsweise wird ein DDL-Trigger für die CREATE TABLE-Anweisung erstellt, die beim CREATE TABLE-Ereignis in der Datenbank ausgeführt wird.
Der DDL-Trigger bewirkt, dass die CREATE LOGIN-Anweisung beim LOGIN-Ereignis auf dem Server ausgeführt wird.

Der Umfang eines DDL-Triggers hängt davon ab, ob der Trigger für ein Datenbankereignis oder ein Serverereignis ausgeführt wird. Trigger werden wie folgt in zwei Typen unterteilt:

Datenbankbezogene DDL-Trigger

Der datenbankweite DDL-Trigger wird vom Datenbankschema-Bearbeitungsereignis aufgerufen. Diese Trigger werden in der Datenbank gespeichert und auf dem DDL-Ereignis ausgeführt, mit Ausnahme derjenigen, die der Zwischenablage zugeordnet sind.

DDL-Trigger im Serverbereich

Der DDL-Trigger im Serverbereich wird vom DDL-Ereignis der Serverschicht aufgerufen. Diese Trigger werden im Datenbankmaster gespeichert.

Verschachtelter Auslöser

Sowohl DDL- als auch DML-Trigger haben dieses Konzept, wenn ein Trigger eine Aktion implementiert, die einen anderen Trigger initiiert. DDL- und DML-Trigger können in bis zu 32 Ebenen verschachtelt werden. Angenommen, wenn der Trigger eine Tabelle bearbeitet, auf der sich ein anderer Trigger befindet, wird der zweite Trigger initialisiert, ruft dann den dritten Trigger auf und so weiter …

Wenn verschachtelte Trigger ausgeführt werden dürfen, werden die Trigger nacheinander als Endlosschleife gestartet. Wenn die letzte Feder erreicht wird, wird der Trigger beendet.

Verschachtelte Trigger können verwendet werden, um Funktionen wie das Archivieren oder Sichern von Datensätzen zu handhaben, die von früheren Aktionen betroffen sind.

Der Benutzer kann verschachtelte Trigger deaktivieren, indem er die Option nested triggers über die gespeicherte Prozedur sp_configure auf 0 oder OFF konfiguriert. Die Standardkonfiguration lässt verschachtelte Trigger zu.

Zum Beispiel:

 GO CREATE TRIGGER Employee_Deletion ON HumanResources.Employee AFTER DELETE AS BEGIN PRINT 'Deletion will affect EmployeePayHistory table' DELETE FROM EmployeePayHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM deleted) END; GO CREATE TRIGGER Deletion_Comnfirmation ON HumanResources.EmployeePayHistory AFTER DELETE AS BEGIN PRINT 'Employe details successfully deleted from EmployeePayHistory table' END; DELETE FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID=1

UPDATE()-Funktion

Die UPDATE()-Funktion gibt einen booleschen Wert zurück, der bestimmt, ob ein UPDATE oder INSERT für die Ansicht oder Spalte der Tabelle ausgeführt wurde.

Die Funktion UPDATE() kann überall im Hauptteil eines UPDATE- oder INSERT-Triggers verwendet werden, um zu prüfen, ob der Trigger eine Aktion ausführen soll.

Syntax:

 UPDATE (column);

Zum Beispiel:

 CREATE TRIGGER Accounting ON Production.TransactionHistory AFTER UPDATE AS IF (UPDATE(TransactionID) OR UPDATE(ProductID)) BEGIN RAISEERROR(5009,16,10) END; GO

Verarbeiten Sie mehrere Zeilen (Datensätze) in einer Sitzung

Wenn ein Benutzer Code für einen DML-Trigger schreibt, verlassen sich die Anweisungen auf den Trigger, um einzelne Anweisungen auszulösen. Eine einzelne Anweisung aktualisiert mehrere Datensätze in den Daten. Dies ist ein häufiges Verhalten bei DELETE- und UPDATE-Triggern, und häufig wirken sich Anweisungen auf mehrere Datensätze aus. Das Verhalten für den INSERT-Trigger ist seltener, da die Insert-Anweisung normalerweise nur 1 Datensatz hinzufügt.

Wenn ein Feature des DML-Triggers automatisch aufruft und eine Zusammenfassung der Werte einer Tabelle neu berechnet und die Ergebnisse in einer anderen Tabelle speichert, sind mehrzeilige Überlegungen wichtig.

Stellen Sie sich beispielsweise die Situation vor, das Ergebnis einer Einfügungssumme in einer einzelnen Zeile zu speichern:

 USE AdventureWorks2019; GO CREATE TRIGGER PODetails ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal = Subtotal + LineTotal FROM INSERTED WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID;

Im folgenden Code wird die Zwischensumme für jeden Befehlsvorgang zum Einfügen einer einzelnen Zeile berechnet und gespeichert.
Der folgende Code wird entweder für mehrzeilige oder einzeilige Situationen ausgeführt:

 USE AdventureWorks2019; GO CREATE TRIGGER PODetailMultiple ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE Purchasing.PurchaseOrderHeader SET Subtotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted);

LOGON-Trigger

Diese Trigger werden als Reaktion auf das LOGON-Ereignis in SQL Server ausgelöst. Anmeldeauslöser lösen die gespeicherte Prozedur im LOGON-Ereignis aus. Dieses Ereignis wird abgefangen, wenn eine Benutzersitzung mit einer Instanz von SQL Server eingerichtet wird. Der Logon-Trigger tritt auf, nachdem die Login-Authentifizierung abgeschlossen ist, aber vorher tatsächlich eine Benutzersitzung aufgebaut wurde. Der Anmeldetrigger wird nicht ausgeführt, wenn die Validierung fehlschlägt.

Anmeldeauslöser werden auf der Serverebene erstellt und sind in den folgenden Fällen nützlich:

• Überprüfen Sie die Anmeldeaktivität
• Anmeldeaktionen steuern

Erstellen Sie beispielsweise eine LoginActivity-Tabelle mit den in adventureworks erstellten Spalten LOGONEvent und Logintime wie folgt:

 CREATE TABLE LoginActivity (LOGONEvent XML, Logintime datetime)

Erstellen Sie einen Trigger, damit der Benutzer beim Anmelden den Anmeldeverlauf in die Tabelle LoginActivity einfügt:

 CREATE TRIGGER [track_login] ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO LoginActivity SELECT EVENTDATA(), GETDATE() END;

Hinweis: Seien Sie sehr vorsichtig, wenn Sie den LOGON-Trigger verwenden, da dies zu einer Situation führen kann, in der der Logikfehler TRIGGER nicht angemeldet werden kann, der DAC geöffnet werden muss, um eine Verbindung herzustellen, also testen Sie ihn sorgfältig, bevor Sie ihn ausführen.

Leistung auslösen

In der Praxis verursachen Trigger keine Kosten, sondern funktionieren recht gut. Viele Leistungsprobleme können jedoch durch die im Trigger vorhandene Logik verursacht werden. Angenommen, ein Trigger erstellt einen Zeiger und iteriert über mehrere Zeilen, wird die Ausführung verlangsamt.

Betrachten Sie auf ähnliche Weise die Situation, in der der Trigger mehrere SQL-Anweisungen ausführt, wodurch separate Tabellen in den eingefügten und gelöschten Tabellen verhindert werden. Es wird das Ergebnis erneut zurückgeben und die Abfrage mehr verlangsamen, als wenn der Trigger nicht verwendet wird.

Daher ist es am besten, die Triggerlogik einfach zu halten und die Verwendung von Zeigern zu vermeiden, wenn Anweisungen in Tabellen und verschiedene Aufgaben ausgeführt werden, die die Systemleistung beeinträchtigen.