/*************************************************************************************
 *************************************************************************************
 * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick
 *************************************************************************************
 *************************************************************************************/

DROP FUNCTION IF EXISTS bestellung_berechne_endsumme() 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
$_SQL_$
  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 Bestell-Tabelle eingefügt.
  END
$_SQL_$
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;





