oracle - 防止无效删除操作的触发器

Question

set SERVEROUT ON;
create or replace TRIGGER tr_check_status
    BEFORE DELETE ON supplier
    for each row
declare
    active_suppliers NUMBER;
begin
    active_suppliers := 0;

    select count(1) into active_suppliers
    from supplier where supplier_id = :old.supplier_id
    AND supplier_status='active';

    IF(active_suppliers > 0) THEN
        raise_application_error(-20001, 'Active supplier can not be deleted');
    END IF;
end;
/
    delete from supplier
    where supplier_id=1;

我被要求创建一个触发器，如果​​supplier_status ='inactive'，它将在供应商表中执行删除操作，并且如果supplier_status ='active'则不允许删除。

我在上面的代码中收到以下错误：

Error starting at line : 19 in command -
delete from supplier
    where supplier_id=1
Error report -
ORA-04091: table DARSHAK.SUPPLIER is mutating, trigger/function may not see it
ORA-06512: at "DARSHAK.TR_CHECK_STATUS", line 6
ORA-04088: error during execution of trigger 'DARSHAK.TR_CHECK_STATUS'

标签： oracleplsqldatabase-trigger

Answer 1

除非供应商可以有多行——这似乎是一个非常糟糕的主意——否则你不需要查询。只需使用：

begin    
    if (:old.supplier_status = 'active') then
        raise_application_error(-20001, 'Active supplier can not be deleted');
    endif;
end;