Could someone explain to me why this trigger does NOT cause a mutating table error?

Question

I have the following row-level trigger, which does both a selection and an update on the same table:

CREATE OR REPLACE TRIGGER insert_product 
    BEFORE INSERT ON products
    FOR EACH ROW
    DECLARE     
            max_version NUMBER (4, 2);
            not_newest EXCEPTION;
    BEGIN
            SELECT version INTO max_version
                FROM (SELECT CIF, name, max(version) AS version FROM products GROUP BY CIF, name 
                HAVING CIF = :NEW.cif AND name = :NEW.name);
            
            IF max_version >= :NEW.version THEN
                RAISE not_newest;
            END IF;
                
            UPDATE products
            SET retired = sysdate
            WHERE CIF = :NEW.cif AND name = :NEW.name AND retired IS NULL;

    EXCEPTION
            WHEN no_data_found THEN NULL;
            WHEN not_newest THEN raise_application_error(-20001, 'Invalid version');
    END insert_product;
/

So the thing is, since it's selecting and updating the same table, why doesn't it throw a mutating table error when I execute it?

I've tested it with queries that affect multiple rows and nothing seems to cause the error, it works properly, which is pretty great I guess, but I would like to understand why.

Answer 1

Your trigger does cause a mutating-table error if I insert multiple rows at the same time using INSERT INTO ... SELECT ...:

SQL> insert into products (cif) select 1 from dual union all select 2 from dual;
insert into products (cif) select 1 from dual union all select 2 from dual
            *
ERROR at line 1:
ORA-04091: table LUKE.PRODUCTS is mutating, trigger/function may not see it
ORA-06512: at "LUKE.INSERT_PRODUCT", line 5
ORA-04088: error during execution of trigger 'LUKE.INSERT_PRODUCT'

Interestingly, the mutating-table error also arises if I use INSERT INTO ... SELECT ... to insert only one row:

SQL> insert into products (cif) select 1 from dual;
insert into products (cif) select 1 from dual
*
ERROR at line 1:
ORA-04091: table LUKE.PRODUCTS is mutating, trigger/function may not see it
ORA-06512: at "LUKE.INSERT_PRODUCT", line 5
ORA-04088: error during execution of trigger 'LUKE.INSERT_PRODUCT'

I don't get the mutating-table error if I use INSERT INTO ... VALUES ..., however:

SQL> insert into products (cif) values (1);

1 row created. 

I can only guess at the reason why you don't get the exception in this case. Before the first row gets inserted the table hasn't yet started mutating, so the data in the table can be queried because it hasn't yet changed. I'd definitely expect a mutating-table error when inserting multiple rows: after inserting the first row and before inserting the second row the table definitely is mutating. However, it's less clear to me why inserting a single row with INSERT INTO ... SELECT ... does cause the error.

Finally, I don't know what columns are in your table besides those mentioned in the trigger, and I don't know which ones are nullable, so for the purpose of this answer I made them all nullable and just inserted a value into one of them.