nicht angemeldet
Rolf B
Matti Mäkitalo
Rolf B
Matti Mäkitalo
Rolf B
Matti Mäkitalo
TS
TS
Hallo Forum,
bisher habe ich alle Änderungen im Datenbanksystem über die Applikationsebene gemacht. Nun habe ich einen Fall, bei dem ich gerne einen Trigger einsetzen würde. Dazu habe ich Fragen.
MudGuardDELIMITER $$
CREATE TRIGGER `kalkulation` AFTER UPDATE ON kalkulationstabelle FOR EACH ROW BEGIN
UPDATE ersatzteile
SET Rabatt = NEW.Rabatt
WHERE TID = NEW.KID;
END $$
DELIMITER ;
Das soll mein Beispieltrigger werden, der in diesem Fall einen veränderten Rabatt der Kalkulation in der Ersatzteile-Tabelle übernimmt.
Meine Fragen:
Was bedeutet FOR EACH ROW?
Muss nun SQL (in meinem Fall mysql) die komplette Kalkulationstabelle nach geänderten Rabatten durchsuchen? Dann könnte so ein Trigger ja mitunter sehr leistungshungrig sein, oder?
Wie sinnvoll ist der Einsatz eines Triggers auf Applikationsebene?
Was ich meine: Ein Trigger ist nicht dramatisch, aber wenn ich sehr viele Trigger nutzen würde, könnte das ganz schön aufwendig sein, wenn mal Fehler auftreten.
Wie sicher sind Trigger? Funktionieren die immer oder gibt es Fehlerquellen, die ich als Trigger-Anfänger nicht kenne?
Kann man auch Trigger-Schlangen bilden?
Tabelle1 wird upgedated, daraufhin wird Tabelle2 upgedated, daraufhin Tabelle3? (mal vom Sinngehalt ganz abgesehen)
Wenn ich einen Trigger zusätzlich zur selben Programmierung auf Applikationsebene einsetze, schadet das (oder könnte schaden) oder ist das einfach nur redundant?
Schönes WE,
Sven
Hallo Sven,
FOR EACH ROW bezieht sich auf die Updates selbst und ist Teil der Syntax. Heißt: diese 3 Worte stehen da immer.
MySQL durchsucht da auch nichts, sondern führt die eingerichteten Trigger pro Update durch. In dem Moment kennt der Server den alten und neuen Inhalt der Row.
Suchen muss er in der ersatzteile Tabelle, es sollte also einen Index für die TID Spalte geben.
Viele Trigger sind natürlich aufwändig. Dafür hast du aber in der Datenbank eingebaute Helfer für die Datenkonsistenz. Ohne Trigger muss die Applikation das leisten.
Ob Trigger kaskadieren muss ich erst lesen. Das kannst du auch selbst...
Rolf
Hallo Rolf,
MySQL durchsucht da auch nichts, sondern führt die eingerichteten Trigger pro Update durch. In dem Moment kennt der Server den alten und neuen Inhalt der Row.
Suchen muss er in der ersatzteile Tabelle, es sollte also einen Index für die TID Spalte geben.
Gut zu wissen, danke.
Viele Trigger sind natürlich aufwändig. Dafür hast du aber in der Datenbank eingebaute Helfer für die Datenkonsistenz. Ohne Trigger muss die Applikation das leisten.
Ja, darum geht es mir. Datenkonsoistenz.
Ob Trigger kaskadieren muss ich erst lesen. Das kannst du auch selbst...
Oder ich probiere es aus.
Sven
Ob Trigger kaskadieren muss ich erst lesen. Das kannst du auch selbst...
Hat funktioniert (Server-Version: 10.4.24-MariaDB )
Sven
Hallo,
Ob Trigger kaskadieren muss ich erst lesen. Das kannst du auch selbst...
Hat funktioniert (Server-Version: 10.4.24-MariaDB )
Allerdings musst du aufpassen: Trigger können keine Tabellen mehr bearbeiten, auf denen bereits Trigger liefen.
Also: Trigger auf Tabelle A updated Tabelle B. Trigger auf Tabelle B updated Tabelle C. Trigger auf Tabelle C updated Tabelle A <-- Fehler.
Generell helfen Trigger bei der Datenkonsistenz - sind aber teilweise sehr schwer zu debuggen wenn etwas schief läuft. Testbarkeit ist auch schwerer (man benötigt immer eine laufende DB, was die Tests langsamer macht; und man kann die Trigger schwerer in "Units" aufteilen, weswegen es quasi unmöglich wird, die Seams festzulegen).
Ich habe größere Applikationen mit vielen Triggern entwickelt und würde es heute vermutlich nicht mehr tun.
Bis auf eine Ausnahme: History-Tabellen (quasi eine "Schattentabelle" zu jeder Tabelle; bei jedem Update/Delete wird der letzte, noch nicht aktualisierte/gelöschte Eintrag in diese History-Tabelle geschrieben).
Viele Grüße Matti
Also: Trigger auf Tabelle A updated Tabelle B. Trigger auf Tabelle B updated Tabelle C. Trigger auf Tabelle C updated Tabelle A <-- Fehler.
Klar. Das nannte sich in Excel (Ihr wisst schon: „voriges Jahrtausend“, „England hatte Königin“, „friedlich grasende Dinosaurier“) eine „Kreisbeziehung“ - würde also zu einer unendlichen Folge von Updates führen.
Hallo Matti Mäkitalo,
Unit Tests macht man doch möglichst gegen einen Mock der Datenbank, gelle? Weswegen man die SQLs in eine eigene Schicht auslagert
Rolf
Hallo,
Unit Tests macht man doch möglichst gegen einen Mock der Datenbank, gelle? Weswegen man die SQLs in eine eigene Schicht auslagert
Ja: wenn man eine Funktion testen will, die SQL ausführt, dann braucht man eine Datenbank - meist nimmt man dazu eine In-Memory-Datenbank (z.B. H2, sqlite oder ein "richtiges" DBMS, welches nicht persistiert).
Hat dieses SQL Nebeneffekte (z.B. durch Trigger) dann muss die Datenbank alle Tabellen und Trigger umfassen, die betroffen sein könnten.
Hier wird es dann schwer: wenn ich in einem "normalen" (nicht-Datenbank) Unit-Test eine Dependency weglasse werde ich meinen Code im Besten Fall gar nicht kompilieren können. Wenn ich einen Trigger weglasse dann läuft alles durch, und ich muss durch Assertions klarmachen, dass meine Trigger-Aktion so ausgeführt wurde wie gewünscht. Und wenn man es ganz richtig machen will muss man auch testen, dass ungewünschte Aktionen (z.B. Update von Schwesterzeilen, weil ein WHERE vergessen wurde) nicht ausgeführt wurde - und zwar auf der gesamten Datenbank. D.h. man ist schnell an einem Punkt, an dem man eigentlich einen kompletten Datenbank-Inhalt initialisiert, dann das eine SQL-Kommando absetzt, und dann die komplette Datenbank prüft, ob alle gewünschten Aktionen durchgeführt wurden und nichts anderes getan wurde.
Oder man fängt an, an unbeteiligte Tabellen Trigger zu hängen die abstürzen (um Mißerfolg klarzumachen, sowas wie Assertions.fail() oder assertTrue(false)).
Wie gesagt: in einem normalen Unit-Test mit Dependency-Injection ist sowas einfach: eine Unit, die nicht injected wird, kann nicht aufgerufen werden. Und die ein/zwei Methoden einer Unit, die man injected, aber wo man die speziellen Methoden nicht braucht (kleine Warnlampe btw) muss man dann halt passend verifyen/mocken.
Lange Rede, kurzer Sinn:
Viele Grüße Matti
Hallo Matti,
SQL Queries ändern sich normalerweise seltener als Programmcode, deswegen ist die Testhäufigkeit geringer; denke ich.
Einen Test, der beweist, dass ein UPDATE nur den gewünschten Satz verändert - wozu? Du musst doch nicht den SQL Server testen. Du schreibst deine SQL Statements, testest sie in der SQL Workbench oder phpmyadmin, und wenn sie tun, was sie sollen, musst du keine Unit-Tests mehr dafür haben.
Im Zusammenhang mit einem UPDATE musst Du nur testen, ob dein Code das korrekte Update-Statement erzeugt. Dafür musst Du die Statementgenerierung von der Statementausführung so trennen, dass Du die Generierung testen kannst, ohne das Statement auszuführen. Beispielsweise mit einem gemockten PDO (in PHP). Prepared statements vereinfachen das, da sind die Statements fix und es muss nichts generiert werden. Natürlich hat man auch da schonmal Szenarien wo SQL zusammengebaut wird. Aber auch dann gilt: Du musst testen, ob der SQL Generator das erwartete SQL liefert, und Du musst die Typen von SQL Statements, die dann entstehen können, gegen die DB geprüft haben. Ersteres ist Teil eines Unit-Tests, letzeres nicht.
Natürlich ist es sinnvoll, eine SQL Testsuite zu haben, die von einem Datenbankzustand A ausgeht, einen Haufen SQL ausführt und dann prüft, ob der erwartete Zustand B erreicht ist. Durch Assertions nach jedem einzelnen Statement kann man die fehlerhafte Stelle schnell finden und auch sicher sein, dass sich zwei Fehler nicht gegenseitig aufheben. Aber diese Testsuite gehört nicht in den ständigen Unit-Test, den man im Build hat oder in einem gated checkin. Dafür ist sie, wie Du richtig schreibst, zu aufwändig.
Und ob dein Businesscode korrekt mit der DB-Schnittstelle interagiert, das ist ein Test auf einer anderen Ebene. Für den brauchst Du die DB nicht, dafür mockst Du die DB. Andernfalls testest Du zu viel auf einmal.
Zumindest wurde mir das Unit-Testkonzept so vermittelt.
Rolf
Hallo,
SQL Queries ändern sich normalerweise seltener als Programmcode, deswegen ist die Testhäufigkeit geringer; denke ich.
wenn man anfängt, Business-Code in Form von Triggern als SQL zu schreiben, dann ändert sich der SQL-Code wahrscheinlich ähnlich häufig wie anderer Code. Und dann sollte man auch den SQL-Code Unit-Testen.
Einen Test, der beweist, dass ein UPDATE nur den gewünschten Satz verändert - wozu? Du musst doch nicht den SQL Server testen. Du schreibst deine SQL Statements, testest sie in der SQL Workbench oder phpmyadmin, und wenn sie tun, was sie sollen, musst du keine Unit-Tests mehr dafür haben.
Ich teste ja nicht eine Query, sondern ich teste ein Funktion, in der das Query drinnen steht. Und das kann (und sollte man m.E.) auch entsprechend testen, so dass nur die Dinge passieren, die man auch will. Insbesonders wenn ein UPDATE dank Triggern noch viele Nebeneffekte haben kann.
Im Zusammenhang mit einem UPDATE musst Du nur testen, ob dein Code das korrekte Update-Statement erzeugt. Dafür musst Du die Statementgenerierung von der Statementausführung so trennen, dass Du die Generierung testen kannst, ohne das Statement auszuführen.
Wenn Statements tatsächlich generiert werden stimme ich dir zu. Wenn Statements aber als SQL (oder einer Vorform davon wie JPQL) im Code eingebettet sind ist es Code und sollte auch getestet werden.
Nenn es meinetwegen Integrationstest statt Unit-Test und separiere es - ändert aber nichts daran dass man es automatisiert testen kann und sollte.
VG Matti
Hello,
Einen Test, der beweist, dass ein UPDATE nur den gewünschten Satz verändert - wozu? Du musst doch nicht den SQL Server testen. Du schreibst deine SQL Statements, testest sie in der SQL Workbench oder phpmyadmin, und wenn sie tun, was sie sollen, musst du keine Unit-Tests mehr dafür haben.
Ich teste ja nicht eine Query, sondern ich teste ein Funktion, in der das Query drinnen steht. Und das kann (und sollte man m.E.) auch entsprechend testen, so dass nur die Dinge passieren, die man auch will. Insbesonders wenn ein UPDATE dank Triggern noch viele Nebeneffekte haben kann.
[...] Insbesondere, wenn man von Status-, bzw. Fehlerbehandlung nichts hält, weil ja keine Fehler auftreten können, weil sie nicht auftreten dürfen. Das ist ähnlich, wie mit Atommüllfabriken. Da treten auch nie Fehler auf ;-P
Eine Web-Datenbank wird i.d.R. im dynamischen Multiuserumfeld betrieben. Drauf feuern alse ggf. hunderte von Userprozessen gleichzeitig Requests ab. Man weiß also nur selten ganz genau, welche Knoten dabei entstehen können.
Testen alleine reicht also nicht. Man muss auch eine möglichst vollständige Fehlerbehandlung einbauen.
__ "Fehler" steht hier nicht für falsche Programmierung, sondern dafür, dass ein durchzuführender Request nicht wie gewünscht beantwortet werden konnte. __
Glück Auf
Tom vom Berg
Hello,
bisher habe ich alle Änderungen im Datenbanksystem über die Applikationsebene gemacht. Nun habe ich einen Fall, bei dem ich gerne einen Trigger einsetzen würde. Dazu habe ich Fragen.
DELIMITER $$ CREATE TRIGGER `kalkulation` AFTER UPDATE ON kalkulationstabelle FOR EACH ROW BEGIN UPDATE ersatzteile SET Rabatt = NEW.Rabatt WHERE TID = NEW.KID; END $$ DELIMITER ;Das soll mein Beispieltrigger werden, der in diesem Fall einen veränderten Rabatt der Kalkulation in der Ersatzteile-Tabelle übernimmt.
Meine Fragen:
- Was bedeutet
FOR EACH ROW?
Muss nun SQL (in meinem Fall mysql) die komplette Kalkulationstabelle nach geänderten Rabatten durchsuchen? Dann könnte so ein Trigger ja mitunter sehr leistungshungrig sein, oder?
Jede betroffene (affected) Row wird mit dem Trigger behandelt.
- Wie sinnvoll ist der Einsatz eines Triggers auf Applikationsebene?
Was ich meine: Ein Trigger ist nicht dramatisch, aber wenn ich sehr viele Trigger nutzen würde, könnte das ganz schön aufwendig sein, wenn mal Fehler auftreten.
Die Trigger arbeiten auf Datenbankebene. Die API bekommt also nichts davon mit, wenn Du im Trigger keine Exception produzierst.
- Wie sicher sind Trigger? Funktionieren die immer oder gibt es Fehlerquellen, die ich als Trigger-Anfänger nicht kenne?
Ja, die Zugriffsrechte müssen entsprechend gesetzt sein. Trigger können Exceptions hervorrufen. Die kann man auch künstlich erzeugen lassen, wenn im Trigger festgelegte Bedingungen nicht stimmen. Deshalb sollte man, wenn man Trigger benutzt, keine Sammel-Updates mehr fahren. Sonst geht die Kontrollmöglichkeit eventuell verloren, je nach Programmiermodel.
- Kann man auch Trigger-Schlangen bilden?
Tabelle1 wird upgedated, daraufhin wird Tabelle2 upgedated, daraufhin Tabelle3? (mal vom Sinngehalt ganz abgesehen)
Man kann storded Procedures/Routines erstellen, der sich die definierten Trigger bedienen. Aber dabei immer auf den Kontrollfluss achten und die Datenintegrität. Ggf. muss man eine Transaktion starten.
Es gibt übrigens nur ein einziges Set von Insert-, Update-, Delete- Triggern pro Tabelle. Darin muss man die Einzelentscheidungen für unterschiedliche Spalten dann per CASE o. ä. verpacken.
Einen SELECT-Trigger gibt es übrigens nicht. Den kann man sich aber basteln, wenn man den Direktzugriff auf die Tabellen sperrt und dafür stored Routines vereinbart. Dann kann man z.B. auch direkt in der Datenbank festhalten, wer welchen Datensatz wann angefordert hat. Die API muss davon nichts wissen.
- Wenn ich einen Trigger zusätzlich zur selben Programmierung auf Applikationsebene einsetze, schadet das (oder könnte schaden) oder ist das einfach nur redundant?
Das ist dann kein Trigger im klassischen Sinne, also nicht ereignisgeteuert, sondern "nur" ein Statement(-Block) in der Vorgangsverwaltung/-Steuerung des Client-Server-Modells.
Bedenke dabei, dass jeder andere Prozess, der dieses Modul nicht benutzen muss, daran vorbei arbeiten kann. Diese Umgehungsmöglichkeit der Geschäftsregeln schließt man dadurch aus, indem man alle Regeln in der Datenbank kapselt und nur einen Zugang zulässt.
Man kann/sollte also Trigger weitestgehend vermeiden, indem man den direkten Zugriff auf die Tabellen sperrt und alle Reqests über Stored Procedures/Routines regelt, Dazu muss man sich ein wenig mit dem Rechtesystem des DBMS beschäftigen, aber es lohnt sich.
Glück Auf
Tom vom Berg
Hallo TS,
Es gibt übrigens nur ein einziges Set von Insert-, Update-, Delete- Triggern pro Tabelle
Entweder verstehe ich Dich miss, oder wir reden von unterschiedlichen Datenbanken. Schon für MYSQL 5.7 steht im Handbuch
It is possible to define multiple triggers for a given table that have the same trigger event and action time.
Die Klauseln FOLLOWS und PRECEDES ermöglichen Dir, den Ausführungszeitpunkt eines neuen Triggers in Relation zu den bestehenden Triggern festzulegen.
Deine Ausführungen zur Verwendung von Routinen statt SQL im Programm unterschreibe ich dagegen gerne. Auch wenn das die Entwicklung aufwändiger machen kann, wenn man einer DB Abteilung unterworfen ist, die jede neue Routine oder jede Änderung daran genehmigen will…
Rolf
Hi,
Es gibt übrigens nur ein einziges Set von Insert-, Update-, Delete- Triggern pro Tabelle
Entweder verstehe ich Dich miss, oder wir reden von unterschiedlichen Datenbanken.
ich glaube, das war so gemeint, daß es den z.B. Insert-Trigger nur für die ganze Tabelle gibt, nicht für einzelne Spalten.
Darum ja auch der folgende von Dir nicht mehr zitierte Satz:
Darin muss man die Einzelentscheidungen für unterschiedliche Spalten dann per CASE o. ä. verpacken.
(Hervorhebung durch mich)
cu,
Andreas a/k/a MudGuard
Hello,
Entweder verstehe ich Dich miss, oder wir reden von unterschiedlichen Datenbanken. Schon für MYSQL 5.7 steht im Handbuch
It is possible to define multiple triggers for a given table that have the same trigger event and action time.
Die Klauseln FOLLOWS und PRECEDES ermöglichen Dir, den Ausführungszeitpunkt eines neuen Triggers in Relation zu den bestehenden Triggern festzulegen.
Stimmt. Da gibt es 'was neues. Aber das bewirkt auch nichts anderes, als wenn man die Anweisungen der Reihe nach in den einzigen Trigger schreibt. Es macht das Ganze nur unübersichtlicher.
Allerdings könnte man nun argumentieren, dass man einzelne Anweisungen besser programmgesteuert hinzufügen, ändern, oder löschen kann, frei nach dem Motto:
Sowas könnte das Ziel sein. Aber z. Zt. scheint es noch nicht so zu funktionieren.
Glück Auf
Tom vom Berg