/*************************************************************************************
 *************************************************************************************
 * 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 AS
$_plpgsql_$
  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
$_plpgsql_$
LANGUAGE plpgsql;

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;