/************************************************************************************* ************************************************************************************* * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick ************************************************************************************* *************************************************************************************/ DROP FUNCTION IF EXISTS bestellung_berechne_endsumme() CASCADE; DROP TABLE IF EXISTS bestellung CASCADE; DROP TABLE IF EXISTS versandkosten CASCADE; /************************************************************************************* * Tabellen *************************************************************************************/ CREATE TABLE versandkosten (vnr INTEGER NOT NULL, art VARCHAR(30) NOT NULL, preis NUMERIC(8,2) NOT NULL, CONSTRAINT pk_versandkosten PRIMARY KEY (vnr), CONSTRAINT unique_art UNIQUE (art) ); CREATE TABLE bestellung (bnr INTEGER NOT NULL, rnr INTEGER NOT NULL, vnr INTEGER NOT NULL, kunde VARCHAR(50) NOT NULL, endsumme NUMERIC(8,2) NOT NULL, bestelldatum DATE NOT NULL DEFAULT CURRENT_DATE, CONSTRAINT pk_bestellung PRIMARY KEY (bnr), CONSTRAINT fk_bestellung_rechner FOREIGN KEY (rnr) REFERENCES rechner (rnr), CONSTRAINT fk_bestellung_versandkosten FOREIGN KEY (vnr) REFERENCES versandkosten (vnr) ); /************************************************************************************* * Trigger * * http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html * http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html *************************************************************************************/ CREATE FUNCTION bestellung_berechne_endsumme() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN NEW.endsumme := (SELECT gesamtpreis + preis FROM rechner, versandkosten WHERE rnr = NEW.rnr AND vnr = NEW.vnr ) ; RETURN NEW; -- Das modifizierte NEW-Tupel wird per INSERT -- in die Bestelltabelle eingefügt. END $$ ; CREATE TRIGGER bestellung_berechne_endsumme BEFORE INSERT ON bestellung FOR EACH ROW EXECUTE PROCEDURE bestellung_berechne_endsumme(); -- Man beachte: Die Endsumme kann beim Insert nicht überschrieben werden, -- wohl aber durch ein späteres Update. /************************************************************************************* * Daten *************************************************************************************/ INSERT INTO versandkosten(vnr, art, preis) VALUES (1, 'Kreditkarte', 2.00), (2, 'Vorauskasse', 0.00), (3, 'Nachnahme', 5.00), (4, 'Rechnung', 3.00) ; INSERT INTO bestellung(bnr, rnr, vnr, kunde) VALUES (1, 2, 3, 'Müller'), (2, 1, 1, 'Kowa') ; INSERT INTO bestellung(bnr, rnr, vnr, kunde, bestelldatum, endsumme) VALUES (3, 1, 4, 'Kowa', DATE '2012-05-05', NULL), (4, 1, 4, 'Kowa', DATE '2012-05-05', 0.00) ; -- Ein anderes Bestelldatum wird eingetragen, aber der Endpreis -- ändert sich durch die explizite Angabe nicht. Dieser müsste -- durch ein nachträgliches UPDATE-Kommando geändert werden. -- Das wurde im obigen Beispiel nicht durch Trigger verhindert. /* UPDATE bestellung SET endsumme = 0.00 WHERE bnr = 4; */ SELECT bnr, rnr, vnr, kunde, bestelldatum, endsumme FROM bestellung;