sgd-praktikum/SQL/TestTrigger.sql

-- Automatisch Cleaningplan erstellen
INSERT INTO BOOKING VALUES (444, TO_DATE('2023-09-07', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);


INSERT INTO BOOKINGROOM VALUES (444, 8);

SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-06', 'YYYY-MM-DD');
ROLLBACK;

-- Automatisch Cleaningplan erstellen und löschen
INSERT INTO BOOKING VALUES (444, TO_DATE('2023-09-07', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);


INSERT INTO BOOKINGROOM VALUES (444, 8);

SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-06', 'YYYY-MM-DD');

DELETE FROM BOOKINGROOM WHERE bookingID = 444;

SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-06', 'YYYY-MM-DD');
ROLLBACK;

-- Cleaningplan kann nicht erstellt werden
INSERT INTO BOOKING VALUES (555, TO_DATE('2023-09-03', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
INSERT INTO BOOKING VALUES (556, TO_DATE('2023-09-06', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);

INSERT INTO BOOKINGROOM VALUES (555, 8);
INSERT INTO BOOKINGROOM VALUES (556, 8);

SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-09-05', 'YYYY-MM-DD');
ROLLBACK;

-- Cleaningplan kann nicht erstellt werden
INSERT INTO BOOKING VALUES (888, TO_DATE('2023-09-03', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);

INSERT INTO BOOKINGROOM VALUES (888, 8);

INSERT INTO CLEANINGPLAN VALUES (cleaningplan_seq.nextval, TO_DATE('2023-09-04', 'YYYY-MM-DD'), 240, 8);
ROLLBACK;

-- Cleaningplan wird erfolgreich gebucht
INSERT INTO CLEANINGPLAN VALUES (cleaningplan_seq.nextval, TO_DATE('2023-11-09', 'YYYY-MM-DD'), 240, 8);
SELECT * FROM CLEANINGPLAN WHERE "date" = TO_DATE('2023-11-09', 'YYYY-MM-DD');
ROLLBACK;

-- Gesamtpreise werden angezeigt
INSERT INTO BOOKING VALUES (234, TO_DATE('2023-09-03', 'YYYY-MM-DD'), TO_DATE('2023-09-05', 'YYYY-MM-DD'), 65, 'No Pension', 0, 15);
INSERT INTO BOOKING VALUES (456, TO_DATE('2023-09-06', 'YYYY-MM-DD'), TO_DATE('2023-10-01', 'YYYY-MM-DD'), 10, 'No Pension', 0, 15);
UPDATE BOOKING SET COST = 5 WHERE BOOKINGID  = 456;
INSERT INTO BOOKING VALUES (789, TO_DATE('2023-09-06', 'YYYY-MM-DD'), TO_DATE('2023-09-06', 'YYYY-MM-DD'), 10, 'No Pension', 0, 15);
DELETE FROM BOOKING WHERE BOOKINGID = 234;
ROLLBACK;