Aufgabe 15 - Datenbankseitige Programmierung

Aufgabe 15 - Datenbankseitige Programmierung

Worum geht es?

In dieser Übung wird zentrale Geschäftslogik direkt in PostgreSQL umgesetzt: Funktionen für Berechnungen, eine Stored Procedure für validierte Operationen und Trigger für automatische Geschäftsregeln (siehe Kapitel 7 - Datenbankseitige Programmierung). Alles baut auf Northwind auf. Im Expertenteil entsteht ein bedingtes Audit-Log samt Architekturbewertung.

Was Sie dafür brauchen

• Kapitel 7 - Datenbankseitige Programmierung.
• Ein PostgreSQL-Server mit Northwind.

Welche Kompetenzen Sie erwerben und zeigen

• Sie schreiben Funktionen und rufen sie ineinander auf.
• Sie kapseln eine mehrstufige Operation mit Validierung in einer Procedure.
• Sie setzen Geschäftsregeln mit BEFORE-Triggern durch.
• Sie bauen ein bedingtes Audit-Log und beurteilen, wann Logik in die Datenbank gehört.

Pädagogische Einordnung

• Reproduktion: Funktionen erstellen und testen (Teil A).
• Reorganisation und Transfer: eine Procedure und Validierungs-Trigger bauen (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: ein Audit-Log bauen und die Architekturfrage beurteilen (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Zur Vorbereitung die Struktur von orders, order_details und products prüfen (\d order_details).

Teil A - Funktionen

1. calculate_line_total (LANGUAGE sql): berechnet unit_price * quantity * (1 - discount), gerundet auf 2 Stellen. Testen: calculate_line_total(14.40, 12, 0.10) = 155.52.
2. get_order_total(p_order_id INT): Summe aller Zeilentotals einer Bestellung, unter Verwendung von calculate_line_total; bei nicht existierender ID 0.00. Gegen eine manuelle Summe prüfen.
3. get_top_products_by_supplier(p_supplier_id INT) (RETURNS TABLE(...)): die am häufigsten bestellten Produkte eines Lieferanten, absteigend nach Menge.

Reflexion: Warum ist eine zentrale Funktion besser als die Formel in jeder Query? Warum ist ein Standardwert bei ungültiger ID wichtig?

Teil B - Stored Procedure

1. adjust_stock(p_product_id INT, p_quantity_change INT): ändert products.units_in_stock; der Bestand darf nie unter 0 fallen (RAISE EXCEPTION); das Produkt muss existieren (RAISE EXCEPTION bei NOT FOUND); jede Änderung wird in eine Tabelle stock_log (product_id, change_qty, new_stock, changed_at, changed_by) protokolliert. Gültige und ungültige Aufrufe testen und stock_log prüfen.

Reflexion: Zwei Vorteile gegenüber einem direkten UPDATE; Folge, wenn die NOT FOUND-Prüfung fehlt.

Teil C - Validierungs-Trigger

1. BEFORE UPDATE Zeitstempel: Spalte updated_at zu products hinzufügen und mit einer Trigger-Funktion set_products_updated_at() bei jedem Update automatisch auf NOW() setzen. Nachweisen, dass sich updated_at ändert, obwohl es im UPDATE nicht genannt wird.
2. BEFORE INSERT/UPDATE Validierung: Trigger trg_validate_order_detail auf order_details, der eine Exception auslöst bei unit_price < 0.01, discount > 0.50 oder quantity <= 0. Mit gültigen und ungültigen Inserts testen.

Teil D - Expertenteil: Bedingtes Audit-Log und Architektur

1. AFTER-UPDATE-Audit: Eine Tabelle product_price_audit (old_price, new_price, changed_at, changed_by) und ein AFTER UPDATE-Trigger trg_product_price_audit auf products, der nur feuert, wenn sich unit_price tatsächlich geändert hat (OLD.unit_price <> NEW.unit_price). Mit zwei Preisänderungen und einem Update ohne Preisänderung testen; genau zwei Audit-Einträge erwarten.
2. Warum bedingt? Erklären, warum OLD.unit_price <> NEW.unit_price besser ist als immer zu protokollieren, und warum ein BEFORE-Trigger zur Validierung, ein AFTER-Trigger zum Auditing passt.
3. Objektüberblick: Alle erstellten Funktionen, Procedures und Trigger über information_schema.routines und information_schema.triggers auflisten und dokumentieren, welcher Trigger auf welcher Tabelle liegt.
4. Architekturfrage: In mindestens fünf Sätzen Stellung nehmen zum Streit „alles ins Backend” gegen „kritische Integrität in die Datenbank”: Wann hat wer recht, und wie würden Sie die Logik dieses Übungsbeispiels konkret aufteilen?

Wissenscheck

1. Worin unterscheiden sich FUNCTION und PROCEDURE, und wie ruft man sie auf?
2. Was passiert bei RAISE EXCEPTION innerhalb einer Procedure mit den bisherigen Änderungen?
3. Was enthalten NEW und OLD in einem UPDATE-Trigger?
4. Warum protokolliert man Preisänderungen nur bei OLD <> NEW?
5. Warum eignet sich ein BEFORE-Trigger besser zur Validierung als ein AFTER-Trigger?

Abgabe

• Ein Dokument mit den Screenshots der Definitionen (Functions, Procedure, Trigger-Funktionen), der Testaufrufe samt Ergebnissen bzw. Fehlermeldungen, den Antworten auf alle Theorie- und Reflexionsfragen und dem Objektüberblick aus Teil D.

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at