Aufgabe mit Trigger in PL/SQL (Oracle)

[Aqualon]

H!

Eine Freundin von mir musste in einer Software-Technik Klausur folgende Aufgabe lösen:


Erstellen Sie einen Trigger für folgendes Problem:
Eine Kredikartengesellschaft hat festgestellt, daß mit über 80% Wahrscheinlichkeit ein Kreditkartenbetrug vorliegt, wenn innerhalb von drei Tagen über 2.000,00 Euro von einem Konto abgebucht werden.
Jede Abbuchung soll also überprüft werden. Tritt durch eine Abbuchung der oben erwähnte Fall ein (innerhalb von 3 Tagen mehr als 2.000,-- Euro) sollen in eine Tabelle 'Kreditkarten_log' die aktuellen Daten der Abbuchung eingetragen werden. Der Tabelle 'Kreditkarten_log' liegt folgende Relation zugrunde:

Kreditkarten_log (Kartennummer, Betrag, Datum_der_Abbuchung, ID_der_abbuchenden_Institution)

Soweit die Aufgabe. Mag sein, dass ich etwas auf dem Schlauch stehe, aber so wie ich das sehe, ist die Aufgabe nicht per Trigger lösbar.

Laut der Lehrerin muss sich der Trigger auf die Tabelle 'Kreditkarten_log' beziehen. Da ist mein Kritikpunkt, dass dieser Trigger nichts von neuen Abbuchungen in die Tabelle Abbuchung erfährt (diese ist oben in der Aufgabe nicht definiert, muss es aber geben). Zusätzlich wurde in der Klausur meiner Freundin folgende Zeile als richtig abgehakt:

INSERT INTO Kreditkarten_log(Kartennummer, Betrag, Datum-der_Abbuchung, ID) usw...

Dies ist aber nicht möglich, da der Trigger von 'Kreditkarten_log' nur durch eine Änderung an 'Kreditkarten_log' ausgelöst werden kann, was diese Tabelle für jegliche SQL-Anweisungen sperrt.

Mit dem Trigger auf 'Kreditkarten_log' kann man soweit ich das sehe nicht weiterkommen.

Also schauen wir mal, was ein Trigger auf 'Abbuchung' macht. Dieser Trigger wird ausgelöst, wenn eine neue Abbuchung (also ein Eintrag in 'Abbuchung') erfolgt. Der Trigger könnte auch einen Eintrag in der Tabelle 'Kreditkarten_log' vornehmen, da diese nicht vom Abbuchungsvorgang betroffen ist.

Soweit so gut, aber in diesem Fall kann der Trigger nur auf die Daten der momentan einzutragenden Abbuchung per :new-Operator zugreifen, aber keinen SELECT auf Abbuchung machen, um feststellen zu können, ob in den letzten 3 Tagen mehr als 2000 Euro abgebucht worden sind.

Für mich heisst das, dass dieser Trigger auch nicht die gewünschte Aufgabenstellung erfüllt.

Ich persönlich hätte die Aufgabe per Stored Procedure gelöst. Diese bekommt die Abbuchungsdaten als Parameter übergeben und prüft ob inkl. der neuen Abbuchung mehr als 2000 Euro in den letzten 3 Tagen abgebucht worden sind. Ist dies der Fall trägt die Stored Procedure die Abbuchung in 'Abbuchung' ein und erstellt ausserdem einen Eintrag in 'Kreditkarten_log'. Ist dies nicht der Fall, wird einfach die Abbuchung eingetragen und die Stored Procedure danach beendet.

Ich bin kein Profi in PL/SQL und habe keine Ahnung, wie diese Aufgabenstellung per Trigger gelöst werden kann.

Weiß jemand, ob das überhaupt möglich ist und falls ja, wie?

Aqua

[HellHorse]

Vielleicht meinten sie nicht triggers, sondern rules (http://www.postgresql.org/docs/7.4/static/rules.html). Damit lässt es sich sehr elegant und einfach lösen.

[Aqualon]

Original geschrieben von HellHorse
Vielleicht meinten sie nicht triggers, sondern rules (http://www.postgresql.org/docs/7.4/static/rules.html). Damit es es sich sehr elegant und einfach lösen.
Dass es damit vielleicht möglich ist, kann gut sein. Nur verlangt die Aufgabenstellung halt einen Trigger. Inwieweit es diese rules von PostgreSQL überhaupt bei Oracle gibt, weiß ich nicht, da wir diese nicht durchgenommen haben.

Aqua

[grakaman]

Original geschrieben von Aqualon
H!

Eine Freundin von mir musste in einer Software-Technik Klausur folgende Aufgabe lösen:



Soweit die Aufgabe. Mag sein, dass ich etwas auf dem Schlauch stehe, aber so wie ich das sehe, ist die Aufgabe nicht per Trigger lösbar.

Laut der Lehrerin muss sich der Trigger auf die Tabelle 'Kreditkarten_log' beziehen. Da ist mein Kritikpunkt, dass dieser Trigger nichts von neuen Abbuchungen in die Tabelle Abbuchung erfährt (diese ist oben in der Aufgabe nicht definiert, muss es aber geben). Zusätzlich wurde in der Klausur meiner Freundin folgende Zeile als richtig abgehakt:

INSERT INTO Kreditkarten_log(Kartennummer, Betrag, Datum-der_Abbuchung, ID) usw...

Dies ist aber nicht möglich, da der Trigger von 'Kreditkarten_log' nur durch eine Änderung an 'Kreditkarten_log' ausgelöst werden kann, was diese Tabelle für jegliche SQL-Anweisungen sperrt.

Mit dem Trigger auf 'Kreditkarten_log' kann man soweit ich das sehe nicht weiterkommen.

Also schauen wir mal, was ein Trigger auf 'Abbuchung' macht. Dieser Trigger wird ausgelöst, wenn eine neue Abbuchung (also ein Eintrag in 'Abbuchung') erfolgt. Der Trigger könnte auch einen Eintrag in der Tabelle 'Kreditkarten_log' vornehmen, da diese nicht vom Abbuchungsvorgang betroffen ist.

Soweit so gut, aber in diesem Fall kann der Trigger nur auf die Daten der momentan einzutragenden Abbuchung per :new-Operator zugreifen, aber keinen SELECT auf Abbuchung machen, um feststellen zu können, ob in den letzten 3 Tagen mehr als 2000 Euro abgebucht worden sind.

Für mich heisst das, dass dieser Trigger auch nicht die gewünschte Aufgabenstellung erfüllt.

Ich persönlich hätte die Aufgabe per Stored Procedure gelöst. Diese bekommt die Abbuchungsdaten als Parameter übergeben und prüft ob inkl. der neuen Abbuchung mehr als 2000 Euro in den letzten 3 Tagen abgebucht worden sind. Ist dies der Fall trägt die Stored Procedure die Abbuchung in 'Abbuchung' ein und erstellt ausserdem einen Eintrag in 'Kreditkarten_log'. Ist dies nicht der Fall, wird einfach die Abbuchung eingetragen und die Stored Procedure danach beendet.

Ich bin kein Profi in PL/SQL und habe keine Ahnung, wie diese Aufgabenstellung per Trigger gelöst werden kann.

Weiß jemand, ob das überhaupt möglich ist und falls ja, wie?

Aqua

Ich kenne mich zwar nicht mit PL/SQL aus, aber normalerweise hast du ja mehrere Möglichkeiten, wie du einen Trigger implementierst (bei T-SQL: AFTER, INSTEAD OF). Ich würde dann einen Trigger implementieren, der nach dem Insert ausgeführt wird (AFTER), dann den zuletzt eingefügten Datensatz, oder besser gesagt ID, abruft und darauf basierend dann die anderen Werte abruft. Allerdings würde ich den Trigger dann auch für die Abbuchungstabelle erstellen. Allerdings steht ja in der Aufgabe auch nicht, dass der Trigger für die Log Tabelle erstellt werden soll. Vielleicht hast du das ja auch missverstanden und deine Lehrerin meinte mit "beziehen" lediglich, dass der Trigger einen Insert auf die Log Tabelle ausführen soll.

[Aqualon]

Original geschrieben von grakaman
Ich würde dann einen Trigger implementieren, der nach dem Insert ausgeführt wird (AFTER), dann den zuletzt eingefügten Datensatz, oder besser gesagt ID, abruft und darauf basierend dann die anderen Werte abruft. Allerdings würde ich den Trigger dann auch für die Abbuchungstabelle erstellen.
Kann ich bei einem AFTER INSERT Trigger denn mit Sicherheit feststellen, welcher Eintrag der letzte ist? Das müsste doch gehen, wenn es eine Abbuchungs_ID gibt, die automatisch hochgezählt wird und die man dann per MAX(Abbuchungs_ID) ausliest, oder?

Wenn man die hätte, kann man ja die Kreditkarten-Nummer und die anderen Daten auslesen, das würde dann ohne Probleme gehen...

Auf die Idee bin ich gar nicht gekommen, muss ich dann später mal ausprobieren, ob es so hinhaut.

Danke für den Tip!

Aqua

[grakaman]

Original geschrieben von Aqualon
Kann ich bei einem AFTER INSERT Trigger denn mit Sicherheit feststellen, welcher Eintrag der letzte ist? Das müsste doch gehen, wenn es eine Abbuchungs_ID gibt, die automatisch hochgezählt wird und die man dann per MAX(Abbuchungs_ID) ausliest, oder?


Ja, aber mit der Aggregat Funktion max kannst du nicht rumhantieren. Diese garantiert nicht, dass der zuletzt eingefügt Datensatz auch jener ist, den du willst. Wenn mehrere Benutzer zugreifen und Abbuchungen vornehmen, kann es sehr gut sein, dass du den falschen Datensatz erwischst (bei solchen Anwendungen wie Kreditkartenabrechnung, bei 0815 DB Anwendungen reicht max() sicher aus).
Beim SQL Server gibts dafür die Funktion IDENT_CURRENT(table) bzw. SCOPE_IDENTITY(). Das garantiert auch, dass du die ID des Datensatz für die richtige Session bekommst. Bei Oracle werden die Befehle freilich anders heißen.

MfG

[Aqualon]

Das übersteigt dann meine Oracle-Fähigkeiten bei weitem. Aber du hast Recht, bei einem AFTER STATEMENT Trigger kann ja wieder ein INSERT kommen, bevor der SELECT MAX()... ausgeführt wird.

Das sinnvollste bei diesem Problem wäre wohl eine täglich ablaufende stored procedure, die alle Abbuchungen der letzten 3 Tage überprüft und beim Überschreiten der Grenze einen Eintrag in Kreditkarten_log erzeugt.

Aqua

[grakaman]

Original geschrieben von Aqualon
Das sinnvollste bei diesem Problem wäre wohl eine täglich ablaufende stored procedure, die alle Abbuchungen der letzten 3 Tage überprüft und beim Überschreiten der Grenze einen Eintrag in Kreditkarten_log erzeugt.

Aqua

Wenn du deine Datenbankoperationen generell in SProcs abhandelst, dann kannst du diese Überprüfung auch gleich dort vornehmen. Das hängt allerdings alles sehr stark von dem Einsatz ab. Aus reiner oo Sicht, ist die Implementierung von Business Logik im Daten Layer nicht erlaubt. Aber ob solche strengen Sichtweisen immer sinnvoll sind, ist imo fraglich. Klar, wenn ich eine Anwendung habe, die möglicherweise auf mehrern DB's laufen muss oder mehrere Anwendungen auf die selbe Business Logik zugreifen, dann wäre es wohl sinnvoller solche Funktionalität in den Business Layer zu verlagern. Allerdings bezweifle ich, dass eine Kreditkartengesellschaft immer die Datenbank wechselt oder andere Spirenzchen macht ;) Außerdem kann man mit der Methode auch viel Overhead vermeiden.

Dein Vorschlag, eine Sproc täglich laufen zu lassen, würde freilich auch funktionieren. Aber man könnte so unter Umständen erst zu spät reagieren, wenn man das denn wollte/müsste.

MfG

[Aqualon]

Vernünftigerweise würde das gleich der Teil der Business Logik überprüfen, der für das Eintragen der Abbuchungen zuständig ist. Aber Beispiele in der Schule sind meistens nicht gerade das, was man praktisch sinnvoll verwenden kann...

Aqua

[HellHorse]

Original geschrieben von Aqualon
Dass es damit vielleicht möglich ist, kann gut sein. Nur verlangt die Aufgabenstellung halt einen Trigger. Inwieweit es diese rules von PostgreSQL überhaupt bei Oracle gibt, weiß ich nicht, da wir diese nicht durchgenommen haben.

Aqua
Ooops, ist etwas warm hier, habe wohl nicht richtig gelesen.
Was ist das allerdings für eine Schule, wo in "Software-Technik" PL/SQL gelehrt wird?

Allerdings bin auch der Meinung dass sowas besser in die Business Logik kommt.

[Aqualon]

Original geschrieben von HellHorse
Was ist das allerdings für eine Schule, wo in "Software-Technik" PL/SQL gelehrt wird?
Ist die Akademie für Datenverarbeitung (http://www.adv-boeblingen.de/index.php?id1=1&id2=1) in Böblingen. Ich mache gerade dort den verlinkten Studiengang.

Denn Sinn von Datenbankprogrammierung in Softwaretechnik verstehe ich zwar auch nicht so ganz, aber das ist nichts neues, dass ich den Sinn mancher Sachen anzweifle.

Aqua

[grakaman]

Original geschrieben von Aqualon
Vernünftigerweise würde das gleich der Teil der Business Logik überprüfen, der für das Eintragen der Abbuchungen zuständig ist. Aber Beispiele in der Schule sind meistens nicht gerade das, was man praktisch sinnvoll verwenden kann...

Aqua

Wie gesagt, das hängt sehr stark vom Einsatz ab. Ich s elbst programmiere sehr gerne oo, aber nicht alles macht Sinn nach strengen Designrichtlinien zu implementieren. Außerdem vernachlässigt das einfach die Tatsache, dass es verschiedene Technologien am Markt gibt. Oracle selbst bietet ja auch die Möglichkeit (imo ab 8i), innerhalb der Datenbank Java zu benutzen. Der nächste SQL Server (Yukon) unterstützt auch sehr stark .NET und man kann dort alternativ zu T-SQL, z.B. mittels C# über ein Objektmodell auf die Ressourcen in der DB zugreifen und Operationen ausführen. Du kannst sogar gleich einen WebServices innerhalb der DB hosten. Das spart unheimlich viel Entwicklungszeit/Wartung, da du somit den Data Layer direkt in die Datenbank verschieben kannst. Natürlich nur, wenn deine Anwendung nicht auf verschiedenen DB's laufen muss.
Ein weiterer Punkt, wo die strikte Einhaltung der Designrichtlinien nicht sinnvoll ist, ergibt sich z.B. bei gewissen Datenbindungen. Laut Theorie darf ja ein Layer nur auf den unterliegenden Layer zugreifen. Nun ist es so, dass man gewisse Daten direkt an UI Elemente binden will. So kann z.B. die Methode eines Datenobjekts ein DataSet zurückliefern, was man direkt an ein DataGrid binden will. Hier macht es überhaupt keinen Sinn, den selben Aufruf noch einmal in der Business Logik zu wrappen. Man würde nicht einmal Codezeilen im Präsentations Layer sparen.
Ein weiterer Punkt ist, dass man in manchen Situationen aus Performancegründen z.B. einen sequenziellen Reader direkt an ein UI Element binden will.
Wie gesagt, es hängt sehr stark vom Einsatzgebiet ab. Bei reinen Datenbankanwendungen, die auch nicht groß über ein Frontend verfügen, macht die verschiebung der Logik in die DB schon Sinn. Nicht zuletzt gibt es Einsatzgebiete, wo Performance ausschlaggebend ist. Aber im Fall einer Kreditkartenabrechnung halte ich Trigger für absolut legitim.

MfG

[HellHorse]

Original geschrieben von grakaman
...
Ich halte die Business Logik direkt in die DB zu programmieren für eine schlechte Idee.
Eine Firma in meiner Umgebung hat das gemacht für die Lohnabrechnung des Kantonspersonals. Ging gut, bis zu dem Zeitpunkt, als plötzlich noch die Kinderzulage berücksichtig werden musste.
Ich glaube nicht, dass der Implentierungsaufwand merklich kleiner ist. Ich galube auch nicht dass es einfacher zu Warten ist, im Gegenteil. Zudem ist debuggen schwerer. Und zu guter letzt legt man sich zu stark auf einen Datenbankhersteller fest. Der Sinn hinter SQL ist ja genau das zu verhindern.
Klar die Performance ist schlechter, wenn man es ausserhalb macht, aber Perfomance sollte aus meiner Sicht erst berücksichtig werden, wenn die aktuelle zu schlecht ist.

Und 3 Sichten Architektur hat aus meiner Sicht nix mit OO zu tun da explizit zwischen Daten und Funktionen unterschieden wird. Aber das ist ok, OO ist nicht die Antwort auf alle Fragen.

@Aqualon
Es ging mir weniger um den Sinn von Datenbankprogrammierung in Softwaretechnik sondern um den Sinn, in einer allgemeinen Vorlesung PL/SQL zu unterrichten.

[grakaman]

Original geschrieben von HellHorse
Ich halte die Business Logik direkt in die DB zu programmieren für eine schlechte Idee.
Eine Firma in meiner Umgebung hat das gemacht für die Lohnabrechnung des Kantonspersonals. Ging gut, bis zu dem Zeitpunkt, als plötzlich noch die Kinderzulage berücksichtig werden musste.


Ich sehe hier aber keinen Grund, warum das schwierger upzudaten wäre.


Ich glaube nicht, dass der Implentierungsaufwand merklich kleiner ist.
Ich galube auch nicht dass es einfacher zu Warten ist, im Gegenteil.


Doch, der Implementierungsaufwand ist geringer und vor allem hast du weniger Overhead. Aber trotzdem wirfst du hier etwas durcheinander, diese Aussage bezog sich nämlich nicht auf den SQL Dialekt. Damit ist es nämlich recht schwer, die BL _komplett_ zu implementieren, weil einfach die Funktionalität (Datentypen usw.) fehlt. Und gerade deswegen gibt es mit die Mehrschichten Systeme, wobei mit der Zeit eben die SQL Dialekte immer mehr an Funktionalität gewannen.
Aber trotzdem ist es manchmal sinnvoll, gewisse Teile in der Datenbank abzuhandeln. Eine Kreditkartenabrechnung stufe ich nämlich als mission critical ein und da ist Performance wichtiger als theoretische Designpattern.
Und die Integration von Java/.NET in die Datenbank hat entscheidende Vorteile. Ich kann hier wenig für Oracle sprechen, aber was ich so von Yukon gehört habe, finde ich einfach nur genial. Außerdem vergisst du hier, dass viele theoretischen Konzepte zu Zeiten entwickelt wurde, die durch heutige Technologien überflüssig gemacht werden.
Jetzt habe ich die Möglichkeit mit der selben Funktionalität einer Programmiersprache, meine Daten innerhalb der Datenbank zu bearbeiten und diese mit ein Leichtes zu veröffentlichen, z.B. als WebService per SOAP. Vorteil, ich brauche keinen externen WebService schreiben, der dann auf BL/DAL zugreift, um Daten zurückzuliefern. Oder anders gesprochen, ich kann mit weniger Aufwand service orientierter programmieren.


Zudem ist debuggen schwerer.


Nicht mit den richtigen Tools. Ich weiß ja, dass du gerne Postgres SQL benutzt, aber zu kommerziellen Produkten wie Oracle/SQL Server etc. gehören schon ein paar Tools mehr als das reine RDBMS.
Und in VS.NET kann ich z.B. auch SQL debuggen (Haltepunkte, Stacktrace etc.) oder man nimmt eben z.B. den Query Analyzer bzw. Profiler beim SQL Server. Und mit Java/.NET in der Datenbank hast du dann auch die selben Debugmöglichkeiten wie sonst.


Und zu guter letzt legt man sich zu stark auf einen Datenbankhersteller fest. Der Sinn hinter SQL ist ja genau das zu verhindern.
Klar die Performance ist schlechter, wenn man es ausserhalb macht, aber Perfomance sollte aus meiner Sicht erst berücksichtig werden, wenn die aktuelle zu schlecht ist.


Das ist aber absolut nicht möglich und aus meiner Sicht auch total unsinnig. Die Hersteller spezifischen SQL Dialekte sind so kompatible zueinander (fängt bei den Datentypen schon an), wie die Nacht dunkel und der Tag hell ist. Außerdem kauft kein normaler Mensch ein Datenbanksystem für einen haufen Geld, ohne dann dessen Potential auszunutzen. Und btw, die Providerunabhängigkeit wird gerade in einem Mehrschichtensystem durch den Data Access Layer garantiert, keines Falls aber durch SQL (auch wenn es vielleicht in der Theorie von SQL so angedacht war).
Und was die Herstellerabhängigkeit angeht, damit hast du absolut Recht. Aber das ist in vielen Projekten auch auch irrelevant, weil man die Zielplattform a) selbst entscheidet, b) diese sich oft eh nie ändern wird und c) sollte diese doch abgeändert werden, dann wird oft innerhalb einer Produktlinie upgegradet.
In Projekten, wo dies nicht der Fall ist, muss man seine Software natürlich anders designen. Etwas anderes habe ich auch nie behauptet.
Um noch einmal auf die BL in SQL zurückzukommen. Es gibt z.B. viele Unternehmen, in denen viele kleinere Subunternehmen arbeiten. Diese tauschen sich z.B. durch bestimmte Schnittstellen Daten aus, so dass man diese dann direkt in der DB hat. Dort ist es gar nicht anders möglich, als die BL mittels SQL abzuhandeln. Nicht zuletzt ist das eine Frage der vorhandenen Ressourcen (Personal) und dessen Skills.


Und 3 Sichten Architektur hat aus meiner Sicht nix mit OO zu tun da explizit zwischen Daten und Funktionen unterschieden wird. Aber das ist ok, OO ist nicht die Antwort auf alle Fragen.


Ein richtiges Mehrschichtensystem ist aber nur durch oo zu erreichen. Z.B. wenn man in einer Webapplikation im Präsentationslayer noch einmal Code/Markup trennen will.

MfG

Nachtrag: Der Trend geht imo dahin, Applikationsserver und DB zu mergen oder zumindest diese besser zu integrieren.

[HellHorse]

Beim Verfassen einer Antwort wurde mir klar: wir sind ja wieder einmal extrem OT. :(
Also schlagen wir uns ein ander Mal die Köpfe ein, wenn es zum Thema passt, ok? :D