Hi i was supposed to make script that will find data in table and based on these insert into same table.
When i was doing this operation while using temporary table to prevent mutating table error(it did not worked and i know my before insert trigger is poorly made caus i get this error).
Anyway i was getting error only while using

INSERT INTO table_name(columns....)
SELECT columns FROM other_table_name
WHERE id = 1

so i was trying to insert only one row at time and commit and it did not worked.

But simple
INSERT INTO table_name(columns...) VALUES(values...)

Was working without raising any error so whats the difference if i want to insert 1 row at time or 1 row at time?
I made my workaround iam just curious why is it like that.

Thanks for any answer.


Trigger code:

CREATE OR REPLACE TRIGGER TBIU_TRIG
 BEFORE INSERT OR UPDATE
 ON MY_TABLE
 FOR EACH ROW
DECLARE
  cursor c_max is
    select nvl(max(number1), 0) + 1
      from my_table;
begin
  if inserting then
    if :new.number1 is null then
      open c_max;
      fetch c_max into :new.number1;
      close c_max;
    end if;
end;

When i do like:
INSERT INTO my_table(col1) values('value');
Everything is OK. 1 Row was inserted.

But when i want to insert 1 row from another table where ID is PK like:
INSERT INTO my_table
SELECT col1
FROM another_table
WHERE id = 1;

I get following error cause of mutating table.

Test case:

CREATE TABLE my_table(col1 varchar2(30), number1 number(5));
CREATE TABLE another_table(col1 varchar2(30));
INSERT INTO another_table(col1) values('value123');

CREATE OR REPLACE TRIGGER TBIU_TRIG
BEFORE INSERT OR UPDATE
ON MY_TABLE
FOR EACH ROW
DECLARE
cursor c_max is
select nvl(max(number1), 0) + 1
from my_table;
begin
if inserting then
if :new.number1 is null then
open c_max;
fetch c_max into :new.number1;
close c_max;
end if;
end;

INSERT INTO my_table(col1) values('volue');
1 row inserted.

INSERT INTO my_table(col1)
SELECT col1 FROM another_table
Mutating table error.

This is my case summed up.

insert into t select ... from ...