ALTER TABLE Ports DROP CONSTRAINT fk_cruise;
DROP TABLE Cruises;
DROP TABLE Ports;

drop sequence cruises_seq;
drop sequence ports_seq;

CREATE TABLE Cruises
(
    cr_id INT PRIMARY KEY,
    c_name VARCHAR2(60) NOT NULL,
    c_ship VARCHAR2(60) NOT NULL,
    c_departure_date DATE NOT NULL, 
    c_return_date DATE NOT NULL
);

CREATE TABLE Ports
(
    p_id INT PRIMARY KEY,
    p_name VARCHAR2(60) NOT NULL,
    cr_id INT,
    CONSTRAINT fk_cruise FOREIGN KEY (cr_id) REFERENCES Cruises(cr_id) ON DELETE CASCADE
);

CREATE SEQUENCE cruises_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE ports_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PACKAGE CruiseDataPackage AS
    PROCEDURE FillData;
    PROCEDURE ClearData;
    PROCEDURE DeleteCruise(p_cr_id IN INT);
END CruiseDataPackage;
/

CREATE OR REPLACE PACKAGE BODY CruiseDataPackage AS
    PROCEDURE FillData IS
    BEGIN

        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (1, 'Средиземноморские жемчужины', 'Costa Diadema', to_date('15.06.2022', 'dd.mm.yyyy'), to_date('30.06.2022', 'dd.mm.yyyy'));
        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (2, 'Карибские приключения', 'Harmony of the Seas', to_date('01.06.2022', 'dd.mm.yyyy'), to_date('10.06.2022', 'dd.mm.yyyy'));
        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (3, ' Северное сияние', 'Hurtigruten MS Roald Amundsen', to_date('20.09.2022', 'dd.mm.yyyy'), to_date('05.10.2022', 'dd.mm.yyyy'));
        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (4, 'Австралийские просторы', 'Pacific Explorer', to_date('10.10.2022', 'dd.mm.yyyy'), to_date('20.10.2022', 'dd.mm.yyyy'));
        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (5, ' Южная Америка', ' Empress of the Seas', to_date('01.11.2022', 'dd.mm.yyyy'), to_date('15.11.2022', 'dd.mm.yyyy'));
        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (6, 'Азиатские тайны ', 'Diamond Princess', to_date('15.12.2022', 'dd.mm.yyyy'), to_date('30.12.2022', 'dd.mm.yyyy'));
        INSERT INTO Cruises (cr_id, c_name, c_ship, c_departure_date, c_return_date) VALUES
        (7, 'Полярное сияние', 'MS Spitsbergen', to_date('15.01.2023', 'dd.mm.yyyy'), to_date('30.01.2023', 'dd.mm.yyyy'));
        
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (1, 'Барселона', 1);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (2, 'Рим', 1);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (3, 'Майями', 2);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (4, 'Ямайка', 2);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (5, 'Осло', 3);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (6, 'Сидней', 4);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (7, 'Рио-де-Жанейро', 5);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (8, 'Буэнос-Айрес', 5);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (9, 'Токио', 6);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (10, 'Шанхай', 6);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (11, 'Сингапур', 6);
        INSERT INTO Ports (p_id, p_name, cr_id) VALUES
        (12, 'Тромсе', 7);
        
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE_APPLICATION_ERROR(-20001, 'Failed to fill tables with data', TRUE);
    END FillData;

    PROCEDURE ClearData IS
    BEGIN
        DELETE FROM Ports;
        DELETE FROM Cruises;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE_APPLICATION_ERROR(-20002, 'Failed to clear tables', TRUE);
    END ClearData;

    PROCEDURE DeleteCruise(p_cr_id IN INT) IS
    BEGIN
        DELETE FROM Ports WHERE cr_id = p_cr_id;
        DELETE FROM Cruises WHERE cr_id = p_cr_id;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20003, 'Failed to delete cruise', TRUE);
    END DeleteCruise;
END CruiseDataPackage;
/

-- Создание триггера
CREATE OR REPLACE TRIGGER prevent_delete_cruise
BEFORE DELETE ON Cruises
FOR EACH ROW
DECLARE
    cnt_cruises NUMBER;
BEGIN
    SELECT COUNT(*) INTO cnt_cruises FROM Cruises;
    IF cnt_cruises < 3 THEN
        RAISE_APPLICATION_ERROR(-20004, 'Cannot delete cruise. There should be at least three cruises in the table.', TRUE);
    END IF;
END;
/


BEGIN 
    CruiseDataPackage.FillData();
    CruiseDataPackage.ClearData();
    CruiseDataPackage.FillData();
    CruiseDataPackage.DeleteCruise(1);
END;

/
ORA-20002: Failed to clear tables ORA-06512: at "WKSP_MAXKUT898.CRUISEDATAPACKAGE", line 60 ORA-04091: table WKSP_MAXKUT898.CRUISES is mutating, trigger/function may not see it ORA-06512: at "WKSP_MAXKUT898.PREVENT_DELETE_CRUISE", line 4 ORA-04088: error during execution of trigger 'WKSP_MAXKUT898.PREVENT_DELETE_CRUISE' ORA-06512: at "WKSP_MAXKUT898.CRUISEDATAPACKAGE", line 55 ORA-06512: at line 3 Как исправить данную ошибку?