EMZETT.
Login

Emzett Wiki

SQL: Transaktionen, Views und Trigger

Properties1
tagsprogrammiersprachen, sql

4 Min. Lesezeit

Kurz: Eine Transaktion bündelt Änderungen zu einer unteilbaren Einheit: Entweder gelingen alle (COMMIT) oder keine (ROLLBACK). Die ACID-Eigenschaften.

Teil des Kurses SQL

Kapitel 12 von 15 im Kurs SQL. Mit Fortschritt, Quiz und Zertifikat auf der Lernseite.

Transaktionen

Eine Transaktion bündelt Änderungen zu einer unteilbaren Einheit: Entweder gelingen alle (COMMIT) oder keine (ROLLBACK). Die ACID-Eigenschaften: Atomicity (alles oder nichts), Consistency (gültiger Zustand), Isolation (parallele Transaktionen stören sich nicht), Durability (nach Commit dauerhaft).

CREATE TABLE konto (name TEXT PRIMARY KEY, stand INTEGER CHECK (stand >= 0));
INSERT INTO konto VALUES ('Mia', 100), ('Tom', 50);
 
BEGIN;
UPDATE konto SET stand = stand - 30 WHERE name = 'Mia';
UPDATE konto SET stand = stand + 30 WHERE name = 'Tom';
COMMIT;
SELECT * FROM konto;
 
BEGIN;
UPDATE konto SET stand = 0 WHERE name = 'Tom';
SELECT * FROM konto;
ROLLBACK;
SELECT * FROM konto;

Ausgabe:

name  stand
----  -----
Mia   70
Tom   80
name  stand
----  -----
Mia   70
Tom   0
name  stand
----  -----
Mia   70
Tom   80

Das Beispiel “Überweisung”: Würde nach der ersten UPDATE ein Fehler auftreten, wäre ohne Transaktion Geld verschwunden.

Mit SAVEPOINT name und ROLLBACK TO name setzt du Zwischenmarken innerhalb einer Transaktion.

Isolation und Sperren

Wenn mehrere Nutzer gleichzeitig arbeiten, regeln Isolationsstufen (READ COMMITTED, REPEATABLE READ, SERIALIZABLE), wie viel sie voneinander sehen. In PostgreSQL ist READ COMMITTED Standard. SELECT ... FOR UPDATE sperrt gelesene Zeilen für andere.

Views

Eine View ist eine gespeicherte Abfrage, die sich wie eine Tabelle benutzen lässt:

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
CREATE VIEW kundenumsatz AS
SELECT k.id, k.name, COALESCE(sum(b.preis * b.menge), 0) AS umsatz
FROM kunden k LEFT JOIN bestellungen b ON b.kunde_id = k.id
GROUP BY k.id;
 
SELECT * FROM kundenumsatz WHERE umsatz > 0 ORDER BY umsatz DESC;
DROP VIEW kundenumsatz;

Ausgabe:

id  name  umsatz
--  ----  ------
3   Zoe   1073.5
1   Mia   1050.0
2   Tom   189.9

Views vereinfachen komplexe Abfragen, verstecken Spalten (Zugriffsschutz) und halten Logik an einem Ort. PostgreSQL kennt zusätzlich materialisierte Views, die das Ergebnis speichern.

Trigger

Ein Trigger führt automatisch Code aus, wenn Daten geändert werden:

CREATE TABLE produkte (id INTEGER PRIMARY KEY, name TEXT, preis REAL);
CREATE TABLE preis_log (produkt_id INTEGER, alt REAL, neu REAL);
 
CREATE TRIGGER preis_aenderung
AFTER UPDATE OF preis ON produkte
FOR EACH ROW
BEGIN
    INSERT INTO preis_log VALUES (OLD.id, OLD.preis, NEW.preis);
END;
 
INSERT INTO produkte VALUES (1, 'Stift', 1.5), (2, 'Heft', 3.0);
UPDATE produkte SET preis = 2.0 WHERE id = 1;
UPDATE produkte SET preis = 3.5 WHERE id = 2;
SELECT * FROM preis_log;

Ausgabe:

produkt_id  alt  neu
----------  ---  ---
1           1.5  2.0
2           3.0  3.5

Typische Einsatzzwecke: Änderungsprotokolle, abgeleitete Werte pflegen, Regeln prüfen. Nutze sie sparsam: Versteckte Logik macht Systeme schwerer nachvollziehbar.

Berechtigungen (DCL)

Server-Datenbanken (nicht SQLite) steuern Zugriff mit Nutzern und Rechten:

CREATE USER app WITH PASSWORD '...';
GRANT SELECT, INSERT ON kunden TO app;
REVOKE INSERT ON kunden FROM app;

Das Prinzip der geringsten Rechte: Anwendungen bekommen nur, was sie brauchen.

SQL-Injection verhindern

Setze nie Benutzereingaben per String-Verkettung in SQL ein. Verwende parametrisierte Abfragen (prepared statements):

-- GEFÄHRLICH: "SELECT * FROM nutzer WHERE name = '" + eingabe + "'"
--   Eingabe  x' OR '1'='1  liefert alle Nutzer.
-- SICHER (Parameter):
SELECT * FROM nutzer WHERE name = ?;

Die Datenbank behandelt den Parameter ausschließlich als Wert, nie als Code.

Merke

  • Transaktionen: BEGIN … COMMIT oder ROLLBACK; sie sichern ACID-Eigenschaften
  • Views sind gespeicherte Abfragen; Trigger reagieren automatisch auf Änderungen
  • Berechtigungen mit GRANT/REVOKE, Prinzip der geringsten Rechte
  • Gegen SQL-Injection helfen parametrisierte Abfragen, nie String-Verkettung

Übungsaufgabe

Baue eine Überweisung als Transaktion und eine View, die alle Konten mit Stand über 50 zeigt.

Quiz zur Selbstkontrolle

Weiter im Kurs

Zurück: Indizes und Performance

Weiter: JSON, Textsuche und moderne SQL-Funktionen

Alle Kapitel: SQL im Überblick

Graphansicht

Backlinks