SQL-Beispiele aus dem Miro-Board (07 SQL-Trigger)
SQL-Dateien
Beispiel: Caching der Anzahl der lieferbaren Waren eines Händlers
-- Schemaevolution (Weiterentwicklung eines Datenbankschemas)
-- Definiere zwei neue Attribute "h_name_mit_ortschaft" und "anzahl_haendler"
-- für die Tabelle "haendler", sofern diese nicht bereits definiert wurden.
ALTER TABLE haendler
ADD COLUMN IF NOT EXISTS h_name_mit_ortschaft VARCHAR(100),
ADD COLUMN IF NOT EXISTS h_anzahl_waren INTEGER DEFAULT 0
;
-- Initial müssen die neuen Attribute von allen bereits vorhandenen
-- Tupeln aktualisiert werden. Bei Tupeln, die künftig hinzugefügt
-- werden, ist dies nicht notwendig, da die Werte der neuen Attribute
-- dann automatisch aktualisiert werden.
SELECT * FROM haendler;
UPDATE haendler h
SET h_name_mit_ortschaft = COALESCE(h_name || ' in ' || h_ortschaft, h_name),
h_anzahl_waren = (SELECT COUNT (DISTINCT w_id)
FROM liefert l
WHERE l.h_id = h.h_id
)
;
SELECT * FROM haendler;
-- Sollte die Datenbank noch keine Elemente enthalten, kann man sich
-- diese Update-Operation sparen.
-- Alle Händler wurden aktualisiert.
-- ALLERDINGS NUR EINMAL.
-- Jedes mal, wenn ein Händler eingefügt oder geändert wird, oder wenn es
-- irgendeine Änderung an der liefert-Tabelle gibt, müssen die beiden neuen
-- Attribute für die zugehörigen Händler erneut berechnet werden. Dies
-- erfolgt künftig automatisch mit Hilfe von Triggern.
CREATE FUNCTION h_anzahl_waren_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
UPDATE haendler
SET h_anzahl_waren =
(SELECT COUNT (DISTINCT w_id) FROM liefert WHERE h_id = OLD.h_id)
WHERE h_id = OLD.h_id;
END IF;
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
UPDATE haendler
SET h_anzahl_waren =
(SELECT COUNT (DISTINCT w_id) FROM liefert WHERE h_id = NEW.h_id)
WHERE h_id = NEW.h_id;
END IF;
RETURN NULL;
END
$$
;
CREATE TRIGGER h_anzahl_waren_trigger_on_insert_delete
AFTER INSERT OR UPDATE OR DELETE
ON liefert
FOR EACH ROW
EXECUTE PROCEDURE h_anzahl_waren_function()
;
CREATE TRIGGER h_anzahl_waren_trigger_on_insert_delete
AFTER INSERT OR DELETE
ON liefert
FOR EACH ROW
EXECUTE PROCEDURE h_anzahl_waren_function()
;
CREATE TRIGGER h_anzahl_waren_trigger_on_update
AFTER UPDATE
ON liefert
FOR EACH ROW
WHEN (OLD.h_id <> NEW.h_id OR OLD.w_id <> NEW.w_id)
EXECUTE PROCEDURE h_anzahl_waren_function()
;
Insert
-- Wie berechnet man h_name_mit_ortschaft?
SELECT h_id, h_name, h_ortschaft,
COALESCE(h_name || ' in ' || h_ortschaft, h_name)
AS h_name_mit_ortschaft
FROM haendler;
-- Wie viele verschiedene Waren liefert jeder Händler
-- (nur h_id)?
SELECT h_id, COUNT(DISTINCT w_id) AS w_anzahl
FROM liefert
GROUP BY h_id
ORDER BY h_id;
Before-Trigger
- Falls der Trigger (samt zugehöriger Prozedur) schon existiert: Lösche ihn.
DROP TRIGGER IF EXISTS h_name_mit_ortschaft_trigger ON haendler;
DROP FUNCTION IF EXISTS h_name_mit_ortschaft_function() CASCADE;
-- Erzeuge die Trigger-Prozedur. Diese berechnet den Wert von
-- h_name_mit_ortschaft aus den neuen Werten von h_name und h_ortschaft.
CREATE FUNCTION h_name_mit_ortschaft_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
NEW.h_name_mit_ortschaft = COALESCE(NEW.h_name || ' in ' || NEW.h_ortschaft, NEW.h_name);
-- Gibt das modifizierte NEW-Tupel zurück.
RETURN NEW;
END
$$
;
-- Jedes Mal BEVOR ein neues Tupel in haendler eingefügt wird bzw.
-- den alten Wert ersetzt, wird es modifiziert. Der Wert des Attributs
-- h_name_mit_ortschaft wird automatisch berechnet.
CREATE TRIGGER h_name_mit_ortschaft_trigger
BEFORE INSERT OR UPDATE
ON haendler
FOR EACH ROW
EXECUTE PROCEDURE h_name_mit_ortschaft_function()
;
Aktivierung des Before-Triggers
SELECT * FROM haendler; -- h_name_mit_ortschaft war überall noch NULL
-- Trigger-Definition
SELECT * FROM haendler; -- h_name_mit_ortschaft war überall noch NULL
-- bei bereits vorhandenen Tupeln
UPDATE haendler SET h_name_mit_ortschaft = 'xxxxx'; -- Durchlaufe alle Tupel in Händler-Tabelle (FOR EACH)
-- und führe h_name_mit_ortschaft_function() aus.
-- 1. Erstes Händler-Tupel:
-- OLD: h_id = 1, h_name = 'Maier', h_ortschaft = 'Königsbrunn', h_name_mit_ortschaft = NULL
-- NEW: h_id = 1, h_name = 'Maier', h_ortschaft = 'Königsbrunn', h_name_mit_ortschaft = 'xxxxx'
-- 2. BEFORE-Trigger-Funktion:
-- BEVOR das NEW-Tupel in die Tabelle eingefügt wird
-- NEW.h_name_mit_ortschaft = 'Maier in Königsbrunn'
-- 3. NEW-Tupel ersetzt in der Tabelle das OLD-Tupel
SELECT * FROM haendler;
INSERT INTO haendler -- INSERT-Trigger wird aktiviert.
VALUES (6, 'Kunz', 'Bobingen');
SELECT * FROM haendler;
UPDATE haendler -- UPDATE-Trigger wird aktiviert.
SET h_ortschaft = 'Oberottmarshausen'
WHERE h_id = 6;
SELECT * FROM haendler;
After-Trigger
-- Falls die Trigger (samt zugehöriger Prozedur) schon existieren: Lösche sie.
DROP TRIGGER IF EXISTS h_anzahl_waren_trigger_on_insert_delete ON haendler;
DROP TRIGGER IF EXISTS h_anzahl_waren_trigger_on_update ON haendler;
DROP FUNCTION IF EXISTS h_anzahl_waren_function() CASCADE;
-- Erzeuge die Trigger-Prozedur. Diese berechnet für den Händler, für den
-- die liefert-Tabelle verändert wurde, den Wert von "h_anzahl_waren" aus
-- den aktuellen Werten in der Tabelle "liefert".
CREATE FUNCTION h_anzahl_waren_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
UPDATE haendler
SET h_anzahl_waren = (SELECT COUNT (DISTINCT w_id)
FROM liefert
WHERE h_id = OLD.h_id
)
WHERE h_id = OLD.h_id;
END IF;
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
UPDATE haendler
SET h_anzahl_waren = (SELECT COUNT (DISTINCT w_id)
FROM liefert
WHERE h_id = NEW.h_id
)
WHERE h_id = NEW.h_id;
END IF;
RETURN NULL;
END
$$
;
-- Jedes Mal NACHDEM die Tabelle "liefert" verändert
-- wurde, aktualisiere die Tabelle "haendler".
CREATE TRIGGER h_anzahl_waren_trigger_on_insert_delete
AFTER INSERT OR DELETE
ON liefert
FOR EACH ROW
EXECUTE PROCEDURE h_anzahl_waren_function()
;
CREATE TRIGGER h_anzahl_waren_trigger_on_update
AFTER UPDATE
ON liefert
FOR EACH ROW
WHEN (OLD.h_id <> NEW.h_id OR OLD.w_id <> NEW.w_id)
EXECUTE PROCEDURE h_anzahl_waren_function()
Aktivierung de After-Trigger
DELETE FROM liefert WHERE h_id > 5;
DELETE FROM haendler WHERE h_id > 5;
INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES
(6, 'Händler A', 'Augsburg'),
(7, 'Händler B', NULL),
(8, 'Händler C', NULL)
;
SELECT * FROM haendler ORDER BY h_id;
UPDATE haendler
SET h_ortschaft = 'Rosenheim'
WHERE h_id = 7;
SELECT * FROM haendler ORDER BY h_id;
INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES
(6, 1, 210.00, 1),
(6, 1, 196.00, 3),
(6, 2, 120.00, NULL),
(7, 3, 150.00, 7),
(7, 4, 11.00, 1),
(8, 5, 6.00, 1)
;
SELECT * FROM haendler ORDER BY h_id;
UPDATE liefert
SET w_id = 4, l_preis = 160
WHERE h_id = 6 AND w_id = 3
;
SELECT * FROM haendler ORDER BY h_id;
UPDATE liefert
SET h_id = 7
WHERE h_id = 6 AND w_id = 1
;
SELECT * FROM haendler ORDER BY h_id;
DELETE FROM liefert
WHERE h_id = 7 AND w_id = 1 AND l_lieferzeit = 999
;
SELECT * FROM haendler ORDER BY h_id;
DELETE FROM liefert
WHERE h_id = 7 AND w_id = 1
;
SELECT * FROM haendler ORDER BY h_id
;
Updateable Views
CREATE VIEW sportler
(id, name, vorname, geburtstag, land, geschlecht)
AS
SELECT s.id, s.name, s.vorname, s.geburtstag,
l.kuerzel, s.geschlecht
FROM e_sportler s JOIN enum_land l ON s.l_id = l.id;
CREATE TRIGGER insert_into_sportler INSTEAD OF INSERT
ON sportler
FOR EACH ROW
EXECUTE <Function, die das Tupel in e_sportler einträgt>();
CREATE RULE insert_into_sportler AS
ON INSERT TO sportler
DO INSTEAD
INSERT INTO
e_sportler
(id, name, vorname, geburtstag, geschlecht, l_id)
VALUES (NEW.id,
NEW.name,
NEW.vorname,
NEW.geburtstag,
NEW.geschlecht,
(SELECT id
FROM enum_land
WHERE kuerzel = NEW.land
)
);
CREATE RULE update_sportler AS
ON UPDATE TO sportler
DO INSTEAD
UPDATE e_sportler
SET id = NEW.id,
name = NEW.name,
vorname = NEW.vorname,
geburtstag = NEW.geburtstag,
geschlecht = NEW.geschlecht,
l_id = (SELECT id
FROM enum_land
WHERE kuerzel = NEW.land
)
WHERE id = OLD.id;
CREATE RULE delete_from_sportler AS
ON DELETE TO sportler
DO INSTEAD
DELETE FROM e_sportler
WHERE id = OLD.id;
Constraint-Trigger
CREATE FUNCTION geschlecht_korrekt() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
IF NOT(((SELECT geschlecht FROM e_wettkampf w WHERE w.id = NEW.w_id)
IN
('x', (SELECT geschlecht FROM e_sportler s WHERE s.id = NEW.s_id)))
)
THEN
RAISE EXCEPTION 'falsches Geschlecht' -- besser: detaillierte Fehlerinfos
END IF;
RETURN NULL;
END
$$
;
CREATE CONSTRAINT TRIGGER geschlecht_korrekt
AFTER INSERT OR UPDATE OR DELETE
ON r_teilnahme
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE geschlecht_korrekt();
