Hello, I am new to PL / SQL and faced with such a problem as the table mutation during the trigger execution

I need when you enter a new supplier into the database automatically entered a record of a new product. my code at the moment

create or replace trigger newSuplier after insert on Suplier for each row declare s number; f number; begin select max(id_suplier) into s from suplier; select max(id_flower) into f from flower; insert into flower values (f+1, '', 0, s, sysdate); --где f+1 - новый айди цветка, '' - пустое название, s - айди нового поставщика END; /

The error is that the trigger tried to change the table that has already been modified by the expression that caused this trigger.

How can this be fixed, if at all possible? Thank you in advance.

    1 answer 1

    First general recommendation: Never assign an ID of any objects to get max (id), this is not safe, another one that will receive and use the same id can work along with your transaction. There are "sequences" for assigning an ID in Oracle. You need to create sequences for all database objects. if desired, you can use one for different objects, then their id will never intersect. Something like this create sequence flower_seq . To get the next unique ID, use select flower_seq.NextVal .

    I do not see that your trigger would change the table that changes in this query. You are trying to select from the table suplier in the line trigger on the same table, this in itself may already cause a mutation error. In the trigger ( for each row ) triggers, the following variables are available :new and :old representing the records currently being processed, all values ​​for the work must be taken from them.

    And I also recommend in all insert requests to explicitly specify the names of columns in which the insertion is going, this will protect you from trouble in the future, when an optional field is added to some table, and all the insert will stop working and they will need to be revised.

    With this in mind, your trigger might look something like this:

     create or replace trigger newSuplier after insert on Suplier for each row begin insert into flower(id_flower,name,num,id_suplier,create_date) select flower_seq.NextVal, '', 0, :new.id_suplier, sysdate from DUAL; end; /

    BUT it can still cause a table mutation if you have a foreign key referenced from the flower table to a suplier , I think you have it. This effect does not appear in all situations. I recommend first checking out the above code, if it causes a mutation error, you will have to resort to more complex measures. For example, to create an additional table in which our trigger will insert in a single value field :new.id_suplier . Then create a second trigger, the query level (without the phrase for each row ) which from this table will transfer the entries to the second table:

     create table new_suppliers(id_suplier number not null); create or replace trigger newSuplier_row after insert on Suplier for each row begin insert into new_suppliers(id_suplier) values(:new.id_suplier); end; / create or replace trigger newSuplier_sta after insert on Suplier begin insert into flower(id_flower,name,num,id_suplier,create_date) select flower_seq.NextVal, '', 0, id_suplier, sysdate from new_suppliers; delete from new_suppliers; end; /