Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

SQL Server 2016: Triggers, Stored Procedures und Funktionen

Änderungen verhindern

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Da Trigger in der gleichen Transaktion ausgeführt werden, welche auch die Änderung verursacht hat, haben Trigger die Möglichkeit diese Änderungen zu verhindern. Dies ist mit ROLLBACK oder einem Fehler möglich. Trotzdem stellt das gleichzeitige Loggen in eine separate Tabelle kein Problem dar.
11:02

Transkript

DML-Trigger werden genau wie DDL-Trigger in der Transaktion ausgeführt, der auch die auslösende Anweisung ausgeführt wurde. Das bedeutet, ich habe die Möglichkeit in einem Trigger Änderungen rückgängig zu machen, wenn ich das möchte, indem ich entweder ein ROLLBACK ausführe, dann wird automatisch einen Fehler ausgelöst, das sehen Sie gleich in der Demo, oder ich löse wirklich einen Fehler aus, indem ich RAISERROR oder THROW verwende und damit kann ich Änderungen verhindern, wenn die mir aus irgendwelchen Gründen in der Logik des Triggers nicht gefallen. Ich zeige mal, wie das aussieht, indem ich auf das SQL Server Management Studio wechsele. Hier lege ich dann zunächst wieder eine Datenbank an, eine kleine Tabelle, einige Zeile in dieser Tabelle und entsprechend einen Trigger. Der Trigger selber ist für Updates und ich prüfe in dem Trigger, wie viele Zeilen aktualisiert werden sollen. Ich könnte statt INSERTED auch DELETED nehmen, müsste die gleiche Anzahl haben. Wenn die Anzahl 1 ist, dann führe ich ein ROLLBACK aus. Das bedeutet, ich kann ein UPDATE fahren, wo maximal eine Zeile aktualisiert wird. Das funktioniert. Versuche ich mehr als eine, zum Beispiel zwei Zeilen zu aktualisieren, dann bekomme ich entsprechend diesen Fehler hier und zwar weil ich ein ROLLBACK ausführe, bekomme ich die Fehlermeldung, die sagt: Die Transaktion endete mit dem Trigger. Der Batch wurde abgebrochen. Ich könnte natürlich auch im Trigger selber einen Fehler auslösen, zum Beispiel hier mit THROW. Das heißt, ich lege das Ganze nochmal kurz frisch an und damit kann ich natürlich nach wie vor eine Zeile ändern. Da hat sich nichts getan. Wenn ich entsprechend das Ganze so realisiere, dann habe ich hier meine Fehlermeldung, meinen Fehlercode, und ich kann natürlich auf die Tabelle mal zugreifen um zu schauen, wie die Daten aussehen und Sie werden sehen, dass sich an der Datensituation nichts verändert hat. Die erste Zeile ist verändert, das kommt von dieser Anweisung. Allerdings keine weitere Zeile alle andere haben unterschiedliche und ungleich 11 Werte für die TraceID. Das heißt, damit habe ich Möglichkeit, entweder ROLLBACK oder einen Fehler zu benutzen. Oder ich habe natürlich auch generell die Möglichkeit, wenn man das so sagen darf, dass wirklich einen Fehler ausgelöst wird, also wenn ich zum Beispiel hier Code habe, die aus irgendeinem Grund fehlerhaft ist, zum Beispiel könnte ich versuchen so etwas wie 0/0 zu berechnen, dann könnte ich das Ganze nochmal frisch anlegen und dann habe ich einen echten Fehler, aber das sollte eigentlich ja eher vermieden werden. Das heißt, wenn ich jetzt hier, ich spare mir das Update Top 1, dann kriege ich entsprechend natürlich genau die Fehlermeldung. Das ist aber eher eine relativ schlechte Variante, weil das hieße, das mein Code doch irgendwo fehlerhaft wäre, aber letztendlich auch das ist abgedeckt. Sie können entweder künstliche Fehler erzeugen mit THROW oder RAISERROR. Sie können mit natürlichen Fehlern arbeiten, wenn die halt auftreten in Ihrem Code, oder entsprechend mit ROLLBACK, je nachdem was sie haben möchten. Der Vorteil von eigenen Fehlern ist, dass ich entsprechend selber dann den Fehlercode im Client oder beim Aufrufer abfragen kann, was einen gewissen Charme hat. Das Zurückrollen von Transaktionen ist nicht das einzige, was Sie machen können. Deswegen habe ich ein neue Skript geladen und dieses Script macht folgendes: Es legt eine Logtabelle an, wo ich reinschreiben möchte, was entsprechend geloggt wird, und einen Trigger, der diese Tabelle entsprechend auch bedient. Da habe ich natürlich das Problem, wenn ich in die Logtabelle reinschreibe, wäre es normalerweise so, dass ich mit einem ROLLBACK später auch genau diese Zeilen aus der Tabelle wieder entfernen würde, die würden da gar nicht wirklich permanent stehen, insofern muss ich ein bisschen trickreicher vorgehen. Was ich machen kann im Trigger, ich kann die bestehende Transaktion zurückrollen und ich kann hingehen und zum Beispiel einfach eine neue Transaktion beginnen. Das könnte auch eine implizite Transaktion sein, das heißt, ich könnte nur das INSERT-Statement hier stehen haben, aber ich kann eine explizite Transaktion hier zu Demo Zwecken öffnen, kann die entsprechend comitten und dann einen Fehler erst auslösen. Das heißt, der Trigger macht im Wesentlichen das Gleiche wie das erste Beispiel, das heißt, wenn ich mehr als eine Zeile aktualisiere, gibt es entsprechend eine Fehlermeldung. So oder so wird aber in die Logtabelle entweder eine Meldung reinsgeschrieben, die mir sagt, das genau eine Zeile geändert wurde, oder es wird in der Logtabelle geschrieben, dass entsprechend nur genau eine auf einmal geändert werden kann, also eine Zeile auf einmal geändert werden kann. Trotz Fehlersituation werden die Anträge in der Tabelle natürlich zum Schluss stehen bleiben, sonst wäre die ganze Sache ziemlich witzlos. Ich lege mal die ganze Infrastruktur, also die Datenbank, die eigentliche Datentabelle, ein paar Zeile da rein, dann entsprechend auch die Logtabelle natürlich, dann den Trigger und probiere den einfach mal aus. Das heißt, ich mache UPDATE auf alle Ziele, dann habe ich natürlich genau in der Ticketstabelle diese TraceID wieder auf 11 gesetzt. Dann schaue ich mal in das [Log], es hat offensichtlich funktioniert. Dann versuche ich mehr als eine Zeile aus der Ticketstabelle zu ändern. Es kommt hier einen Fehler. 50001 Es kann nur maximal eine Zeile auf einmal geändert werden. Das heißt, ich habe hier nach wie vor die Situation, die ich ja vorher hatte an den Daten, aber wenn ich in meinem Log schaue, dann sehe ich jetzt zwei Anträge, also das funktioniert. Ich kann innerhalb eines DML-Triggers, generell eines Triggers, die Transaktion zurückrollen und einen neue beginnen. Das macht natürlich nur für DML- und DDL-Trigger wirklich auch Sinn. Nachdem ich ein ROLLBACK hier durchgeführt habe, kann ich entsprechend einfach eine neue Transaktion beginnen, oder eine implizite Transaktion mit diesem INSERT-Statement. Ergebnis ist, dass ich letztendlich, die Daten in der Ticketstabelle nicht geädert habe, dafür aber entsprechend den Logeintrag, so wie ich es gerne hätte. Es gibt noch eine zweite Variante, die ich Ihnen jetzt in dem nächsten Skript zeige. Für das nächste Beispiel habe ich wieder ein Skript geladen. Damit lege ich erst einmal die Datenbank und die Ticketstabelle an, füge ein paar Zeilen ein. Ich lege zusätzlich noch eine Logtabelle an, die mir entsprechend die Fehlerdetails speichern soll. Das entspricht dem, was auch in den Stored Proceduren möglich ist. Ich lege dann einen UPDATE-Trigger an und der UPDATE-Trigger sieht jetzt so aus, dass ich zum einen einen TRY CATCH-Block anlege und in dem CATCH-Block entsprechend entweder ein ROLLBACK oder ein COMIT durchführen kann, ich habe zwei Möglichkeiten, ich spreche gleich über die Unterschiede, danach einfach hingehe und in die Fehlertabelle die entsprechende Zeile eintrage mit den Details des Fehlers und den Fehler dann hoch an den Aufrufer weiterleite, damit er weiß, das irgendwas schief gelaufen ist. Der Trick an der ganzen Sache ist folgende, dass ich hier oben noch SET XACT_ABORT OFF stehen habe. Das bedeutet, wenn ein Fehler auftritt, soll die Transaktion, die läuft, nicht abgebrochen werden, sondern offen bleiben und deswegen habe ich hier die Möglichkeit, entweder die Änderungen, die ich habe, zurückzurollen, dann wäre es ein ROLLBACK, oder wenn ich die Änderungen haben möchte trotz Fehler, dann kann ich entsprechend ein COMIT hier einsetzen. Unabhängig davon habe ich dann die Möglichkeit, entsprechend hier zu sagen, dass ich die Fehlersituation protokollieren möchte. So wenn ich das laufen lasse, die habe ich schon angelegt, dann kann ich ganz normal ein UPDATE ausführen. Das funktioniert natürlich an der Stelle nicht, weil ich hier künstlich einen Fehler erzeuge. So was ich jetzt aber habe, ich habe in dem Trigger ein ROLLBACK stehen, das heißt, die Daten müssten nach wie vor so sein, wie sie am Anfang waren. In der Tat, sonst hätte ich eine TraceID 11 irgendwo stehen, vorzugsweise hier oben. In meiner Logtabelle befinden sich trotzdem die Details zu diesem Fehler. Wenn ich jetzt möchte, dass die Änderung, die ich hiermit initiiert wird in der Tat stattfindet, dann kann ich hingehe und das ändern. Ich kann aus dem ROLLBACK ein COMIT machen, natürlich nicht beides, das funktioniert nicht. Ich kann dann hingehen und die komplette Datenbank nochmal kurz neu aufsetzen. Ich kann dann runtergehen und die UPDATE-Anweisung aufrufen Ich bekomme natürlich die Fehlermeldung, da hat sich nichts geändert, aber die Daten sind in der Tat auch verändert geblieben, das heißt, das UPDATE- Statement ist jetzt kurioserweise mit dem Fehler quittiert worden, aber die Änderungen sind in der Tat in der Tabelle persistiert worden. Das kann manchmal sinnvoll sein, insofern habe ich das hier in dem Beispiel gezeigt. Die Details finden Sie natürlich auch in der Logtabelle, da hat sich ja auch nicht sehr viel geändert. Die wichtigen Punkte sind hier, dass Sie innerhalb des Triggers XACT_ABORT OFF setzen, dann haben Sie hier drin die Möglichkeit, die Transaktion entweder zurückzurollen oder zu comitten. Wenn Sie die Zeile 66 nicht haben oder hier statt OFF, ON stehen, dann wird die Transaktion automatisch beim Auftreten eines Fehlers zurückgerollt und dann haben Sie hier unten gar nicht mehr die Möglichkeit entsprechend ein COMIT durchzuführen. Das wäre zwar möglich dann entsprechend auf das ROLLBACK zu verzichten, weil das ist dann gar nicht mehr notwendig, die Transaktion wurde automatisch zurückgerollt, aber um die Wahl zu haben muss ich hier oben SET XACT_ABORT OFF stehen haben, bevor der Fehler hier entsprechend auftritt und eigentlich besonders sinnvoll ist dann nur mit TRY CATCH-Blöcks. Das heißt, damit kann ich auf die Transaktion Einfluss nehmen, die entsprechend läuft, weil die DML-Anweisung hier ein UPDATE-Statement ausgeführt hat und ich kann damit steuern, ob ich die entsprechend comitten oder zurückrollen möchte.

SQL Server 2016: Triggers, Stored Procedures und Funktionen

Nutzen Sie in SQL Server Trigger, gespeicherte Prozeduren, Late Binding, Fehlerbehandlung sowie Scalar- und Tabellenwertfunktionen.

3 Std. 12 min (44 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:08.08.2016

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!