135 lines
5.1 KiB
MySQL
135 lines
5.1 KiB
MySQL
Drop TABLE Artikel CASCADE CONSTRAINTS;
|
|
Drop TABLE Bestellung CASCADE CONSTRAINTS;
|
|
Drop TABLE Nachbestellung CASCADE CONSTRAINTS;
|
|
|
|
-- Create the tables
|
|
CREATE TABLE Artikel (
|
|
Artikel_ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
Name VARCHAR2(60) NOT NULL,
|
|
Preis NUMBER(10, 2) NOT NULL,
|
|
Bestand_Ist NUMBER NOT NULL,
|
|
Bestand_Min NUMBER NOT NULL,
|
|
Bestand_Max NUMBER NOT NULL
|
|
);
|
|
CREATE TABLE Bestellung (
|
|
ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
Bestellnummer VARCHAR2(9) NOT NULL,
|
|
Pos NUMBER NOT NULL,
|
|
Artikel_ID NUMBER,
|
|
Menge NUMBER NOT NULL,
|
|
Pos_Gesamtpreis NUMBER(10, 2) NOT NULL,
|
|
Gesamtpreis NUMBER(10, 2) NOT NULL,
|
|
Versendet NUMBER(1) NOT NULL,
|
|
FOREIGN KEY (Artikel_ID) REFERENCES Artikel(Artikel_ID)
|
|
);
|
|
CREATE TABLE Nachbestellung (
|
|
Nachbestellnummer NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
Artikel_ID NUMBER,
|
|
Menge NUMBER NOT NULL,
|
|
FOREIGN KEY (Artikel_ID) REFERENCES Artikel(Artikel_ID)
|
|
);
|
|
|
|
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel1', 8.99, 60, 12, 120);
|
|
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel2', 13.49, 25, 6, 60);
|
|
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel3', 6.99, 110, 22, 220);
|
|
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel4', 23.00, 12, 3, 36);
|
|
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel5', 4.75, 80, 16, 160);
|
|
INSERT INTO Artikel (Name, Preis, Bestand_Ist, Bestand_Min, Bestand_Max) VALUES ('Artikel6', 11.30, 45, 9, 90);
|
|
-- Trigger to set Pos before insert on Bestellung
|
|
CREATE OR REPLACE TRIGGER set_pos_before_insert
|
|
BEFORE INSERT ON Bestellung
|
|
FOR EACH ROW
|
|
DECLARE
|
|
max_pos NUMBER;
|
|
BEGIN
|
|
SELECT NVL(MAX(Pos), 0) INTO max_pos FROM Bestellung WHERE Bestellnummer = :NEW.Bestellnummer;
|
|
:NEW.Pos := max_pos + 1;
|
|
END;
|
|
/
|
|
|
|
-- Trigger to update Gesamtpreis after insert on Bestellung
|
|
CREATE OR REPLACE TRIGGER update_gesamtpreis_after_insert
|
|
AFTER INSERT ON Bestellung
|
|
FOR EACH ROW
|
|
DECLARE
|
|
PRAGMA AUTONOMOUS_TRANSACTION;
|
|
gesamt NUMBER(10, 2);
|
|
BEGIN
|
|
SELECT SUM(Pos_Gesamtpreis) INTO gesamt FROM Bestellung WHERE Bestellnummer = :NEW.Bestellnummer;
|
|
UPDATE Bestellung SET Gesamtpreis = gesamt WHERE Bestellnummer = :NEW.Bestellnummer;
|
|
COMMIT;
|
|
END;
|
|
/
|
|
|
|
SELECT * FROM USER_ERRORS WHERE NAME = 'UPDATE_BESTAND_AFTER_UPDATE';
|
|
|
|
-- Trigger to update Bestand_Ist after update on Bestellung
|
|
CREATE OR REPLACE TRIGGER update_bestand_after_update
|
|
AFTER UPDATE ON Bestellung
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF :NEW.Versendet = 1 AND :OLD.Versendet = 0 THEN
|
|
UPDATE Artikel SET Bestand_Ist = Bestand_Ist - :NEW.Menge WHERE Artikel_ID = :NEW.Artikel_ID;
|
|
DECLARE
|
|
v_Bestand_Ist NUMBER;
|
|
v_Bestand_Min NUMBER;
|
|
v_Bestand_Max NUMBER;
|
|
BEGIN
|
|
SELECT Bestand_Ist, Bestand_Min, Bestand_Max INTO v_Bestand_Ist, v_Bestand_Min, v_Bestand_Max FROM Artikel WHERE Artikel_ID = :NEW.Artikel_ID;
|
|
IF v_Bestand_Ist < v_Bestand_Min THEN
|
|
INSERT INTO Nachbestellung (Artikel_ID, Menge)
|
|
VALUES (:NEW.Artikel_ID, v_Bestand_Max - v_Bestand_Ist);
|
|
END IF;
|
|
END;
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
-- Trigger to prevent modification of shipped orders
|
|
CREATE OR REPLACE TRIGGER prevent_modification_after_shipment
|
|
BEFORE UPDATE OR DELETE ON Bestellung
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF :OLD.Versendet = 1 THEN
|
|
RAISE_APPLICATION_ERROR(-20001, 'Cannot modify shipped orders');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
-- Trigger to prevent insert outside business hours
|
|
CREATE OR REPLACE TRIGGER prevent_insert_outside_business_hours
|
|
BEFORE INSERT ON Bestellung
|
|
FOR EACH ROW
|
|
DECLARE
|
|
current_time TIMESTAMP;
|
|
BEGIN
|
|
SELECT SYSTIMESTAMP INTO current_time FROM DUAL;
|
|
IF TO_CHAR(current_time, 'HH24:MI:SS') NOT BETWEEN '08:00:00' AND '16:00:00' THEN
|
|
RAISE_APPLICATION_ERROR(-20002, 'Orders can only be placed during business hours (08:00 - 16:00)');
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
TRUNCATE TABLE BESTELLUNG;
|
|
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025001', null, 1, 2, 1999.98, 3.98, 0);
|
|
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025001', null, 3, 1, 49.99, 2.97, 0);
|
|
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025001', null, 3, 1, 49.99, 1.33, 0);
|
|
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025001', null, 3, 1, 1.99, 1.33, 0);
|
|
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025002', null, 3, 1, 1.99, 1.33, 0);
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025002', null, 3, 1, 50.99, 1.33, 0);
|
|
INSERT INTO Bestellung (Bestellnummer, Pos, ARTIKEL_ID, Menge, POS_GESAMTPREIS, Gesamtpreis, Versendet)
|
|
VALUES ('B2025002', null, 3, 1, 60.99, 1.33, 0);
|
|
|
|
|
|
SELECT * FROM BESTELLUNG; |