CREATE TABLE "DOC_TYPE" (
  "ID" NUMBER( 2 ) NOT NULL PRIMARY KEY, 
  "NAME" VARCHAR2( 50 )
)
/
INSERT INTO "DOC_TYPE"("ID","NAME") VALUES (  1 , 'bekommen');
INSERT INTO "DOC_TYPE"("ID","NAME") VALUES (  2 , 'kaufen');
INSERT INTO "DOC_TYPE"("ID","NAME") VALUES (  3 , 'verkaufen');
INSERT INTO "DOC_TYPE"("ID","NAME") VALUES (  4 , 'schnell_verkaufen');
INSERT INTO "DOC_TYPE"("ID","NAME") VALUES (  5 , 'schmeisen');
/
CREATE TABLE "LADEN" (
  "ID" NUMBER( 2 ) NOT NULL PRIMARY KEY, 
  "NAME" VARCHAR2( 50 )
)
/
CREATE TABLE "BENUTZER" (
  "ID" NUMBER( 9 ) NOT NULL PRIMARY KEY, 
  "NAME" VARCHAR2( 50 )
)
/
CREATE TABLE "ZUSTAND" (
  "ID" NUMBER( 2 ) NOT NULL PRIMARY KEY, 
  "NAME" VARCHAR2( 50 )
)
/
CREATE TABLE "EINBAND" (
  "ID" NUMBER( 2 ) NOT NULL PRIMARY KEY, 
  "NAME" VARCHAR2( 50 )
)
/
CREATE TABLE "BUCH" (
  "ID" NUMBER( 9 ) NOT NULL PRIMARY KEY, 
  "TITEL" VARCHAR2( 255 ) NOT NULL, 
  "AUTHOR" VARCHAR2( 255 ) NOT NULL, 
  "VERLAG" VARCHAR2( 100 ), 
  "ISBN" NUMBER( 13 ) UNIQUE, 
  "JAHR" VARCHAR2( 10 ), 
  "ORT" VARCHAR2( 50 ), 
  "SEITEN" VARCHAR2( 25 ), 
  "EINBAND_ID" NUMBER( 2 ) REFERENCES "EINBAND"("ID"),
  "INFO" VARCHAR2( 1000 )
)
/ 
CREATE TABLE "EXEMPLAR" (
  "BARCODE" NUMBER( 9 ) NOT NULL PRIMARY KEY, 
  "BUCH_ID" NUMBER( 9 ) NOT NULL REFERENCES "BUCH"("ID"),
  "ZUSTAND_ID" NUMBER( 2 ) NOT NULL REFERENCES "ZUSTAND"("ID"), 
  "GEWICHT" NUMBER( 9 , 3 ) NOT NULL, 
  "BEMERKUNG" VARCHAR2( 1000 )
)
/ 
CREATE TABLE "DOCUMENT" (
  "ID" NUMBER( 9 ) NOT NULL PRIMARY KEY, 
  "TYPE_ID" NUMBER( 2 ) NOT NULL REFERENCES "DOC_TYPE"("ID"),
  "DOC_DATE" DATE NOT NULL, 
  "LADEN_ID" NUMBER( 2 ) NOT NULL REFERENCES "LADEN"("ID"),
  "DOC_BENUTZER_ID" NUMBER( 9 ) NOT NULL REFERENCES "BENUTZER"("ID"), 
  "AKZEPTIERT" NUMBER( 1 )
)
/ 
CREATE TABLE "DOCUMENT_ITEM" (
  "ID" NUMBER( 9 ) NOT NULL PRIMARY KEY, 
  "DOC_ID" NUMBER( 2 ) NOT NULL REFERENCES "DOCUMENT"("ID"),
  "BARCODE" NUMBER( 9 ) NOT NULL REFERENCES "EXEMPLAR"("BARCODE"), 
  "MENGE" NUMBER( 3 ) NOT NULL,
  "SUMME" NUMBER( 9 , 2 ) NOT NULL
)
/ 
CREATE TABLE "BEWEGEN" (
  "ID" NUMBER( 9 ) NOT NULL PRIMARY KEY, 
  "DOC_ID" NUMBER( 2 ) NOT NULL REFERENCES "DOCUMENT"("ID"),
  "BARCODE" NUMBER( 9 ) NOT NULL REFERENCES "EXEMPLAR"("BARCODE"), 
  "LADEN_ID" NUMBER( 2 ) NOT NULL REFERENCES "LADEN"("ID"),
  "MENGE" NUMBER( 3 ) NOT NULL
)
/ 
CREATE OR REPLACE
TRIGGER "TRIGGER1" BEFORE UPDATE OF "AKZEPTIERT" ON "DOCUMENT" 
FOR EACH ROW
BEGIN
  IF (:new."AKZEPTIERT"= 1 ) THEN
    IF (:new."TYPE_ID"< 3 ) THEN
      INSERT INTO "BEWEGEN"
        SELECT "ID", "DOC_ID", "BARCODE", :new."LADEN_ID", "MENGE"
        FROM "DOCUMENT_ITEM"
        WHERE "DOC_ID"=:new."ID";
    ELSE
      INSERT INTO "BEWEGEN"
        SELECT "ID", "DOC_ID", "BARCODE", :new."LADEN_ID", -"MENGE" AS "MENGE"
        FROM "DOCUMENT_ITEM"
        WHERE "DOC_ID"=:new."ID";
    END IF;
  ELSE
    DELETE FROM "BEWEGEN" WHERE "DOC_ID"=:new."ID";
  END IF;
END;