删除变异表上的SQL触发器 [英] SQL trigger on delete mutating table

问题描述

我必须为学校做一个项目，却陷入一个问题.我创建了以下表格:Flights(flight_id，从，到，距离，department_time，arrival_time，plane_id)；飞机(plane_id，plane_name，range);证书(employee_id，plane_id)；员工(employee_id，employee_name，薪金).

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).

在飞行中，flight_id是PK，plane_id是FK引用飞机(plane_id)；在飞机上，planes_id是一个PK，在雇员上，employee_id是一个PK，在证书中，plane_id是一个FK引用Planes(plane_id)，employee_id是一个FK引用，employees(employee_id)(对于每个飞行员，我们都知道他可以使用哪种飞机飞).

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).

我的问题是我必须创建一个触发器，以确保如果飞机的范围与至少3个航程的距离兼容，则无法删除该飞机.这是我编写的代码:

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;

但是，当我要对其进行测试时，我收到一条错误消息，指出Flights表正在变异.我相信这是因为我想从飞机"表中删除某些内容，而航班"中的plane_id是引用飞机(plane_id)的FK(我在ON DELETE CASCADE中添加了约束).我该如何解决这个问题?

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?

谢谢您的帮助！

推荐答案

尝试在触发器中使用PRAGMA AUTONOMOUS_TRANSACTION.

Try use PRAGMA AUTONOMOUS_TRANSACTION in trigger.

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

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;

这篇关于删除变异表上的SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！