Trigger to prevent invalid delete operation

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;

I am asked to create a trigger that will perform delete operation in Supplier table if supplier_status='inactive' and do not allow to delete if supplier_status = 'active'.

I am getting following error in above code:

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'

just a little note : using `from` keyword is redundant within a `delete` statement spesific to Oracle DB. — Barbaros Özhan, Apr 11 '20 at 16:40
If only StackOverflow already had [loads of **answered** questions about Oracle mutating questions](https://stackoverflow.com/search?q=ORACLE+trigger+mutating+answers%3A1) — APC, Apr 11 '20 at 21:47

score 2 · Answer 1 · answered Apr 11 '20 at 15:18

Unless a supplier can have multiple rows -- which seems like a really bad idea -- then you don't need a query. Just use:

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

Trigger to prevent invalid delete operation

1 Answers1