Mutating table trigger error for one type of insert statement

Question

I'm getting mutating table error for statement insert into employee select 'xyz',200 from dual and scripts executes successfully for insert into employee values ('abc',100);.

Can somebody explain why the statement fails for one type of insert statement? Both scripts insert similar type of data into table

details of script:

--table creation
create table employee (name varchar2(30),salary number);

--trigger creation
create or replace trigger emp_trig 
before insert on employee
for each row
begin
delete from employee where name=:new.name;
end;
/
--insert statement 1
insert into employee values ('abc',100);
--result : 1 row inserted

--insert statement 2
insert into employee select 'xyz',200 from dual
--result: 

Error report -
ORA-04091: table NMS_CON.EMPLOYEE is mutating, trigger/function may not see it
ORA-06512: at "NMS_CON.EMP_TRIG", line 2
ORA-04088: error during execution of trigger 'NMS_CON.EMP_TRIG'

Answer 1

Inserting a single row will not lead to a mutating table error - how could it, since that row wasn't there before?

But insert-select potentially involves more than one row, so then you get the error.

Generally, you should not have non-query DML operations in your trigger. Too many possible side effects and undesirable consequences.

A better approach is to write a procedure that will do the insert for you, do not give insert privileges on the table directly, only to the package that owns the procedure. Then inside that procedure you can do a delete before your insert, or you can do a merge - or whatever.

All the logic is hidden inside the procedure and by restricting privs on the table, you ensure that the procedure must be called.

Hope that helps!