Trigger-Tricks

[Der Buchhalter]

Moin,

ich bastel an einer sport-bezogenen Datenbank-Anwendung mit PDO und Sqlite. Hierbei werden ganz normale (Fußball-)Ergebnisse zu einer nicht ganz üblichen Punkte-Wertung verarbeitet. In Anlehnung an Wettquoten erhält ein Favorit bei einem Sieg weniger Punkte, als der Außenseiter für einen Sieg in demselben Spiel erhalten hätte. Wie dies im einzelnen berechnet wird, sollte m. E. keine Rolle spielen.

Ich trage das Spiel-Resultat in eine Tabelle für Ergebnisse ein. An dieser Tabelle hängt ein Trigger, welcher die Punkte pro Spieler ausrechnet und in eine andere Tabelle für Punkte einträgt. In dieser zweiten Tabelle gibt es nicht einen Eintrag pro Spiel, sondern je einen Eintrag pro beteiligtem Spieler.

Und hier beginnt mein Problem. Denn die Tabelle, in der ich die Punkte eintrage, fließt auch gleichzeitig in die Berechnung dieser Punkte ein. Ich habe also eine Art Zirkelbezug. Der Trigger trägt die Punkte des ersten Spielers ein und in dem Moment, wo der Trigger den zweiten Spieler berechnet, ist die Berechnungsgrundlage bereits verändert.

Ich behelfe mir zurzeit mit einer zusätzlichen Tabelle, welche ich hier mal Journal nenne. Wenn ich ein Spiel-Ergebnis in die ganz normale Ergebnis-Tabelle eintrage, reagiert ein Trigger und schreibt alle zur weiteren Berechnung benötigten Daten in das Journal. An dem Journal hängt ein weiterer Trigger, welcher nun die Punkte pro Spieler (anhand der festgeschriebenen Daten im Journal) berechnet und in die entsprechenden Einträge in die Punkte-Tabelle vornimmt.

Dies funktioniert so weit gut und hat auch den Vorteil, dass ich jederzeit im Journal nachschauen kann, auf welcher Datengrundlage eine Berechnung stattgefunden hat. Dennoch frage ich mich, ob es einen direkteren Weg ohne Journal und mit nur einem Trigger gibt.

Gäbe es z. B. Variablen in einem Sqlite-Statement, könnte ich die Werte (innerhalb des Triggers) temporär speichern und bräuchte keine Hilfs-Tabelle. Ich hoffe mal, dass mein Problem trotz der Abstrahierung auch für Außenstehende verständlich ist. Ansonsten liefere ich gern weitere Details.

Welche Möglichkeiten habe ich sonst diese Problematik anzugehen? Böte eine andere DB-Struktur vielleicht Möglichkeiten, die ich nicht sehe? Oder kann man eventuell eine Transaction für meine Zwecke umbiegen?

Gruß der Buchhalter

[TS]

Hello,

mMn darf die durch einen Trigger abgefragte Tabelle nicht durch ihn verändert werden. Einzige Ausnahme ist innerhalb des aktuell bearbeiteten Datensatzes (nit OLD und NEW).

Du solltest mal ein ERM aufmalen und die zeitlichen Zusammenhänge dazu skizzieren. Beachte auch, dass es parallele Prozesse geben könnte, die "gleichzeitig" auf dieselben Daten zugreifen.

Unter diesem Aspekt ist "Journal mit eineindeutiger Buchungsnummer" der richtige Weg → Trennung von Bestands- und Bewegungsdaten. DS in Tabellen mit Bewegungsdaten dürfen nicht verändert werden, nur angefügt.

Anderenfalls müsstest Du mit einem Wust von Lockings auf die Tabellen arbeiten. Das grät schnell außer Kontrolle.

Liebe Grüße
Tom S.

[Der Buchhalter]

Ahoi Tom!

Vielen Dank für die Antwort. Du hast m. E. genau erfasst worum es mir geht und meine Fragen eigentlich schon beantwortet.

Ich werde mich trotzdem mal an einer schematischen Darstellung versuchen. Da ich allerdings keinen Informatiker-Hintergrund habe, werde ich wohl eine freie Form wählen. Dazu komme ich aber erst später im Laufe des Tages.

Grüße vom Buchhalter

[Rolf B]

Hallo Der Buchhalter,

ich denke, diese Fachlichkeit gehört nicht in einen Trigger, dazu ist sie zum komplex. Wenn Du es unbedingt in der DB lösen willst, dann mit einer Stored Procedure, die kann sich Daten merken. Problem: SQLite kann meines Wissens keine Stored Procedures.

Eigentlich sollte diese Logik ohne Trigger gelöst werden, aus deiner Anwendung heraus. Dann hat Du die Probleme nicht. Eine Transaction mit entsprechendem Isolation Level (sprich: Lock-Intensität) brauchst Du dabei auf jeden Fall, weil Du ja mehrere Updates machst, die entweder alle gelingen müssen oder von denen keiner ausgeführt werden darf.

Läuft diese Anwendung als öffentlich zugängliches Web? Oder ist das eine Einpersonenlösung, die zufällig den Browser zur Oberflächendarstellung verwenden? Davon hängt ab, wie genau Du das Locking analysieren und vor allem testen musst.

Rolf

[TS]

Hello,

Eigentlich sollte diese Logik ohne Trigger gelöst werden, aus deiner Anwendung heraus. Dann hat Du die Probleme nicht. Eine Transaction mit entsprechendem Isolation Level (sprich: Lock-Intensität) brauchst Du dabei auf jeden Fall, weil Du ja mehrere Updates machst, die entweder alle gelingen müssen oder von denen keiner ausgeführt werden darf.

Da möchte ich vehement widersprechen!

Wenn man die Geschäftsregeln in der Datenbank vereinbaren kann, sollte man es auch tun. Alles, was erst in der API vereinbart wird, ist unsicher. Man kann dann niemals mit anderen APIs auf die DB zugreifen (und sicher sein, dass die die Regeln auch einhalten).

Außerdem kann man bei geschickter Trennung von Bewegungsdaten und Bestandsdaten durchaus auf Locks verzichten. Es gilt immer der Grundsatz: nur eine Konsolidierung zur selben Zeit. Und wenn man alle Bewegungsdatensätze, die zu einem Vorgang gehören, auch mit einer eineindeutoigen Vorgangskennung versieht, kann man die Konsolidierung sogar später noch jederzeit wiederherstellen.

Denn Konsolidierung ist das, was der OP hier betreiben will.

Immer hübsch die Trennung einhalten und die Tabellen nicht verknoten.

• Stammdaten
• Bestandsdaten
• Bewegungsdaten
• Reports

Liebe Grüße
Tom S.

[Der Buchhalter]

Hi Tom!

Denn Konsolidierung ist das, was der OP hier betreiben will.

So ist es. Zumindest passt mein Laien-Verständnis des Begriffs zu meinem Vorhaben. Und jetzt habe ich auch noch das passende Schlagwort für weiterführende Recherche. Danke.

Außerdem kann man bei geschickter Trennung von Bewegungsdaten und Bestandsdaten durchaus auf Locks verzichten.

Mit dem Journal habe ich bereits einige meiner persönlichen Sorgen eliminiert. Aber ich habe noch keine Ahnung von dem Thema Locks. Ich versuche immer zwei Fragen zu durchdenken: bereitet es Probleme, wenn „gleichzeitig“ derselbe Datensatz mehrfach geschrieben werden sollte? Und: bereitet es Probleme, wenn „gleichzeitig“ verschiedene Datensätze geschrieben werden sollten?

Beide Situationen bereitem dem jetzigen System (aus meiner Sicht) keine Probleme. Den ersten Teil decke ich mit mit einer uniqueId ab, so dass ich auf Konflikte reagieren kann. Und beim zweiten Teil sehe ich keine Probleme. Welcher Eintrag zuerst in der Datenbank landet kann mir egal sein. Durch entsprechende Schlüssel ist bei der Konsolidierung sichergestellt, dass die Zusammenhänge korrekt abgebildet werden. Genau das hast Du ja auch beschrieben, wenn ich dich richtig verstanden habe.

Ich erstelle übrigens kein Schema mehr. Die Diskussion hat mir bereits sehr weitergeholfen.

Grüße

[Der Buchhalter]

Moin Rolf,

ja, ich könnte die Berechnung in meiner Anwendung vornehmen und die Werte dann selbst eintragen. In diesem Fall möchte ich allerdings ganz bewusst möglichst wenig (bis gar keine) Logik am Frontend belassen.

Es handelt sich um ein persönliches Lern-Projekt unter Labor-Bedingungen. Insofern kann ich die Praxis-Tauglichkeit hinten anstellen und meine Neugier auf diesen speziellen Aspekt befriedigen. Stored Procedures wären normalerweise auch mein Ansatz gewesen, aber diese Option habe ich in Sqlite eben nicht. Und jetzt besteht die Herausforderung in diesem „Level“ halt darin, eine Lösung in Sqlite zu finden.

Kleiner Exkurs: in meiner Konstellation sind viele Spieler an einem einzigen Spiel beteiligt. Und jeder dieser Spieler erhält anschließend eine Punkte-Wertung. Mit der Trigger-Lösung muss meine Anwendung nur ein Ergebnis eintragen. Ohne Trigger müsste ich noch eine Vielzahl weiterer Einträge pro Spiel vornehmen.

Vor dem Hintergrund, die Browser-Anwendung möglichst einfach halten zu wollen, scheint mir der Trigger eine elegante Lösung.

Zum Thema Komplexität; mein Trigger ist schlank. Die eigentliche Punkte-Berechnung habe ich per Custom Function an PHP ausgelagert. Das hätte ich wohl vorher mal erwähnen sollen. Somit geschieht die Logik m. E. letztlich doch in meiner Anwendung und der Trigger kümmert sich nur um die Distribution der Werte.

Mit dem Journal fahre ich gut und möchte es gar nicht mehr missen. Durch Tom fühle ich mich in diesem Vorgehen noch bestärkt. Und mit dem Journal ist auch der Trigger kein Problem mehr. Einzig die Frage, ob es eine einfache Abkürzung gibt, muss wohl verneint werden.

Danke für deine Anregungen. Gruß der Buchhalter