CODEWITHSUNDEEP
sqloracle-database
NightOWL
NightOWL

Reputation: 37

SQL trigger on delete mutating table

I have to do a project for school and I got stuck at a question. I have created the following tables: Flights(flight_id, from, to, distance, departure_time, arrival_time, plane_id); Planes(plane_id, plane_name, range); Certification(employee_id, plane_id); Employees(employee_id, employee_name, salary).

In Flights flight_id is a PK and plane_id is a FK referencing Planes(plane_id); in Planes planes_id is a PK, in Employees employee_id is a PK and in Certification plane_id is a FK referencing Planes(plane_id) and emloyee_id a FK referencig Employees(employee_id) (for each employee that is a pilot we know what kind of planes he can fly).

My problem is that I have to create a trigger that makes sure that a plane can't be deleted if its range is compatible with the distance of at least 3 flights. This is the code I have written: 

CREATE OR REPLACE TRIGGER planes_del_trigg
        BEFORE DELETE ON planes FOR EACH ROW
    DECLARE 
        CURSOR dist_cur IS
            SELECT distance
            FROM flights;
        v_dist flights.distance%TYPE;
        v_counter NUMBER(3);
    BEGIN
        OPEN dist_cur;
        LOOP
            FETCH dist_cur INTO v_dist;
            IF v_dist <= :OLD.range THEN
                v_counter := v_counter + 1;
            END IF;
            EXIT WHEN dist_cur%NOTFOUND;
        END LOOP;
        CLOSE dist_cur;
        IF v_counter >= 3 THEN
            RAISE_APPLICATION_ERROR(-20501, 'This plane cannot be deleted!');
        END IF;
    END planes_del_trigg;

However, when I want to test it I get an error that says that Flights table is mutating. I belive that is because I wanna delete something from the Planes table and plane_id from Flights is a FK referencing Planes(plane_id) (I added the constraint with ON DELETE CASCADE). How could I solve this problem?

Thank you for your help!

Upvotes: 1

Views: 256

Answers (1)

lukasz.kiszka
lukasz.kiszka

Reputation: 163

Try use PRAGMA AUTONOMOUS_TRANSACTION in trigger.

Oracle doc: https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems002.htm

    CREATE OR REPLACE TRIGGER planes_del_trigg
            BEFORE DELETE ON planes
        DECLARE 
            CURSOR dist_cur IS
                SELECT distance
                FROM flights;
            v_dist flights.distance%TYPE;
            v_counter NUMBER(3);

 PRAGMA AUTONOMOUS_TRANSACTION;
        BEGIN
            OPEN dist_cur;
            LOOP
                FETCH dist_cur INTO v_dist;
                IF v_dist <= :OLD.range THEN
                    v_counter := v_counter + 1;
                END IF;
                EXIT WHEN dist_cur%NOTFOUND;
            END LOOP;
            CLOSE dist_cur;
            IF v_counter >= 3 THEN
                RAISE_APPLICATION_ERROR(-20501, 'This plane cannot be deleted!');
            END IF;
        END planes_del_trigg;

Upvotes: 2

Related Questions