MariaDB-Kurs / Gespeicherte Prozeduren, Funktionen und Trigger unterscheiden und verwenden
Eine gespeicherte Prozedur wird ausdrücklich mit CALL aufgerufen, eine gespeicherte Funktion liefert einen Wert innerhalb eines SQL-Ausdrucks und ein Trigger wird automatisch durch eine Tabellenänderung ausgelöst. Alle drei speichern SQL-Logik auf dem Server. Sie unterscheiden sich jedoch in Aufruf, Berechtigungen und Transaktionsverantwortung.
Das Objekt nach seiner Aufgabe auswählen
| Objekt | Aufruf | Typische Aufgabe |
|---|---|---|
| Gespeicherte Prozedur | CALL name(argumente) | Ausdrückliche Operation aus mehreren Anweisungen |
| Gespeicherte Funktion | name(argumente) in einem Ausdruck | Wiederverwendbare Regel mit einem Rückgabewert |
| Trigger | Automatisch bei INSERT, UPDATE oder DELETE | Kurze zeilenbezogene Aktion zur Tabellenänderung |
Eine Prozedur muss nicht zwingend Daten ändern; gespeicherte Funktionen sind nicht allgemein auf Arithmetik beschränkt. Diese Übung verwendet konkrete Aufgaben: Die Prozedur ändert den Bestand, die Funktion berechnet einen Betrag und der Trigger protokolliert alten und neuen Bestand. MariaDB-Trigger werden je Zeile ausgeführt. Eine Änderung vieler Zeilen verursacht entsprechend viele Triggerausführungen.
Vergleichen Sie die Steuerung von COMMIT und ROLLBACK durch die aufrufende Verbindung mit dem Sicherheitskontext von Views. Automatische Datenbanklogik kann die Fehlersuche erschweren. Unterscheiden Sie bewusst aufgerufene Operationen von Logik, die jeden passenden Schreibzugriff begleitet. Entscheiden Sie, welche Regeln in die Anwendungseingabeprüfung gehören und welche für verschiedene Schreibwege gemeinsam gelten müssen.
Rechte und zwei InnoDB-Tabellen vorbereiten
Verwenden Sie appops_lab mit einem Lernkonto mit CREATE ROUTINE, TRIGGER und den erforderlichen Tabellenrechten. Routinenaufrufe benötigen EXECUTE. Die Übung zu Konten und Rechten hilft, den Zugriff passend einzugrenzen. Die Richtlinie für Binärprotokolle kann zusätzliche Anforderungen an die Funktionserstellung stellen. Lassen Sie diese prüfen, statt globale Vertrauenseinstellungen zur Ausführung der Übung zu lockern.
Prüfen Sie Server sowie vorhandene Datenbanken und Objekte. Bewahren Sie andere Übungen innerhalb von appops_lab auf. Die beiden Tabellennamen sowie Funktions-, Prozedur- und Triggernamen müssen verfügbar sein. Der Aufbau entfernt keine bestehenden Objekte.
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
product_id INT NOT NULL PRIMARY KEY,
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
old_stock INT NOT NULL,
new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);
Beide Tabellen verwenden InnoDB. Dadurch können Bestandsänderung und Protokolleintrag des Triggers gemeinsam zurückgenommen werden. Existiert ein Name bereits, untersuchen Sie Definition und Daten vor einer Wiederholung.
Alle drei Objekte vollständig definieren
Das folgende Skript verwendet die Syntax des mariadb-Kommandozeilenclients. DELIMITER legt fest, wie der Client das Anweisungsende erkennt; es ist keine SQL-Anweisung des Servers. Verwenden Sie in einem grafischen Editor dessen Skriptausführung. Bei einem Python-Treiber senden Sie jede vollständige CREATE-Anweisung ohne DELIMITER-Zeilen und ohne abschließendes //.
DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
END IF;
RETURN ROUND(p_price * p_qty, 2);
END//
CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
IF p_delta IS NULL OR p_delta = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
END IF;
UPDATE routine_stock SET stock = stock + p_delta
WHERE product_id = p_product_id AND stock + p_delta >= 0;
IF ROW_COUNT() <> 1 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
END IF;
END//
CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
IF OLD.stock <> NEW.stock THEN
INSERT INTO routine_audit (product_id, old_stock, new_stock)
VALUES (NEW.product_id, OLD.stock, NEW.stock);
END IF;
END//
DELIMITER ;
line_total lehnt negative oder fehlende Preise und Mengen ab und liefert einen Betrag mit zwei Dezimalstellen. Die Menge 0 ist erlaubt. Prüfen Sie den DECIMAL-Wertebereich für große Preise und Mengen. DETERMINISTIC NO SQL beschreibt diese konkrete Implementierung und muss nach Änderungen weiterhin zutreffen. Die CREATE-FUNCTION-Referenz erklärt Eigenschaften und Sicherheitskontext.
adjust_stock ändert genau ein vorhandenes Produkt, wenn der neue Bestand nicht negativ wird. Andernfalls meldet SIGNAL einen fachlichen Fehler. ROW_COUNT wird unmittelbar nach UPDATE gelesen, da eine weitere Anweisung die zugrunde liegende Operation verändern kann. Die CREATE-PROCEDURE-Referenz erläutert Parameter und Aufrufkontext.
Der Trigger schreibt nur bei einer tatsächlichen Bestandsänderung einen Eintrag. OLD und NEW bezeichnen die alten und neuen Werte der Zeile. Die Tabelle ist ein Änderungsprotokoll für die Übung und allein kein unabhängiges manipulationssicheres Auditsystem. Die CREATE-TRIGGER-Referenz beschreibt die Zeilenwerte und AFTER UPDATE.
Beide Tabellen bei Rücknahme und Bestätigung vergleichen
SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;
Der berechnete Betrag ist 37.50. Innerhalb der Transaktion sinkt der Bestand von acht auf fünf; der Protokolleintrag zeigt 8 → 5. Nach ROLLBACK muss der Bestand wieder acht und die Anzahl der Protokolleinträge null sein. Eine automatisch hochgezählte Kennung muss nicht wiederverwendet werden. Eine Lücke in der Nummerierung belegt daher keine fehlgeschlagene Rücknahme.
START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;
Nach COMMIT beträgt der Bestand sechs, und ein Eintrag dokumentiert 8 → 6. Die Prozedur enthält bewusst kein internes COMMIT. Die aufrufende Verbindung kann sie dadurch mit weiteren Arbeiten zusammenfassen. Der Trigger teilt den Transaktionskontext seiner auslösenden Anweisung und bestätigt nicht unabhängig.
Ungültige Eingaben und Berechtigungsgrenzen beobachten
Führen Sie die folgenden Anweisungen einzeln aus. Die erste versucht mehr als die sechs verfügbaren Einheiten abzuziehen, die zweite verwendet einen negativen Einzelpreis. Beide sollen SQLSTATE 45000 melden. Prüfen Sie anschließend, dass Bestand sechs und genau ein bestätigter Protokolleintrag erhalten bleiben.
CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);
Funktion und Prozedur deklarieren SQL SECURITY INVOKER. Tabellenzugriffe werden deshalb anhand der Rechte des Aufrufers geprüft. EXECUTE ohne UPDATE erlaubt über diese Prozedur keine Bestandsänderung. Ein Trigger verwendet dagegen den Kontext seines DEFINER, des definierenden Kontos; er wird nicht als INVOKER deklariert. Verwalten Sie dieses Konto, dessen Lebensdauer und erforderliche Tabellenrechte entsprechend.
Die offiziellen Triggerbeschränkungen begrenzen unter anderem Transaktionssteuerung und erneute Änderungen an der auslösenden Tabelle. Halten Sie Triggerarbeit kurz und auf die Datenbanktransaktion bezogen. Lange externe Abläufe sollten nicht unsichtbar an einer Zeilenänderung hängen.
Definitionen prüfen und nur Übungsobjekte entfernen
SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;
Bewahren Sie Definitionen und Sicherheitskontexte auf und prüfen Sie die Erfassung benötigter Routinen und Trigger bei Sicherung und Wiederherstellung. Eine auf einem anderen Server wiederhergestellte Definition kann bei fehlendem DEFINER oder unzureichenden Rechten scheitern. Entfernen Sie am Ende ausschließlich die eigenen entbehrlichen Objekte:
DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;
Ein gewöhnliches ROLLBACK stellt entfernte Objekte nicht wieder her. Kontrollieren Sie die Namen und erhalten Sie andere Tabellen von appops_lab. Änderungen betrieblicher Routinen benötigen zusätzlich abgestimmte Aufrufer sowie ein Bereitstellungs- und Wiederherstellungsverfahren.




